CODEWITHSUNDEEP
excelvba
Parseltongue
Parseltongue

Reputation: 11657

Excel- Add numbers in a column given that they're positive and given that they're associated with a value in a row

Might be complicated, so let me give you some background:

I have a spreadsheet that contains the following data:

  1. Column H contains dates from 1794- 2011 (and within ‘H’ there are multiple copies of each date for each legislator present in that date)
  2. 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!

Upvotes: 4

Views: 607

Answers (4)

Jesse
Jesse

Reputation: 1935

A solution without array formulas is to use SUMPRODUCT.

=SUMPRODUCT(--(H1:H7=K10),--(Q1:Q7>0),Q1:Q7)/SUMPRODUCT(--(H1:H7=K10),--(Q1:Q7>0))

I ripped off Siddharth's example shown below with SUMPRODUCT.

enter image description here

Upvotes: 3

Roger
Roger

Reputation: 420

You should be able to use SUMIFS/COUNTIFS for this.

for positive values:

=sumifs(Q:Q,H:H,1794,Q:Q,">0")/countifs(H:H,1794,Q:Q,">0")

for negative values:

=sumifs(Q:Q,H:H,1794,Q:Q,"<0")/countifs(H:H,1794,Q:Q,"<0")

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

enter image description here

Upvotes: 5

Tejs
Tejs

Reputation: 41236

This could be considered kind of a hack, but you could just use some hidden columns that have a copied IF formula per data point. To illustrate:

Add another column A which has IF(Q > 0, Q, 0) Add another column B which has IF(Q > 0, 1, 0) Add another column C which has IF(Q < 0, Q, 0) Add another column D which has IF(Q < 0, 1, 0)

Then, to determine the averages, you can simply do a SUM(A) / SUM(B) and SUM(C) / SUM(D) respectively, since it would be the total value divided by the number of true occurances.

Upvotes: 0

Related Questions