Excel- Add numbers in a column given that they're positive and given that they're associated with a value in a row
excel, vba
Solution
Here us a simple way using ARRAY Formulas. Please see SNAPSHOT
=IFERROR(AVERAGE(IF(($H$1:$H$7=K10)*($Q$1:$Q$7>0),$Q$1:$Q$7)),"")
You have to enter this using CTL + SHIFT + ENTER
Problem
Might be complicated, so let me give you some background: I have a spreadsheet that contains the following data: - Column H contains dates from 1794- 2011 (and within ‘H’ there are multiple copies of each date for each legislator present in that date) - Column Q contains scores that range from -1 to 1 I want to calculate the average “positive” value in Column Q for each date in Column H, and then have it print out. For example: ``` 1794: Average Positive Value : .65, Average Negative: -.20 1795: Average Positive Value: .75, Average Negative: -.11 ``` I've tried searching around to figure out how to do this, but I don't know the search terms relevant. Would appreciate some assistance. Thanks!