SUM IF Array

  • Thread starter Thread starter CH
  • Start date Start date
C

CH

I have a spreadsheet that has columns E, I, M, Q
calcualating quarterly averages of total items purchased,
E being first quarter and I second quarter, etc. The
formula for that is simple, E7=B7:D7/3, and so on. Now,
I have a yearly average that needs to be automatically
adjusted as new information is put in to each respective
quarter. If E were populated with an average, I,M,Q
likely wouldn't be, but because there is a formula in all
remaining fields the last amount from the previous column
is carried in to the next and all remaining rows across
show the same amount. So, how can I make this sum if
work below by using else if or something of that nature?
When I use this formula, it will give me E7's sum
correctly and then I7's also when I populate the actual
data with it-but not when I populate it to look at M and
Q.

I hope I am making sense and any help you can give me is
appreciated. I have searched the site and tried array
functions and logical functions without success.

=IF(E7>"I7",E7,(IF(I7<>E7,(I7+E7)/2,(IF(M7<>I7,
(I7+E7+M7)/3,(IF(Q7<>M7,(E7+I7+M7+Q7)/4)))))))

Best regards,
CH
 
I can't really follow what you're doing.

<<>"but because there is a formula in all
remaining fields the last amount from the previous column
is carried in to the next and all remaining rows across
show the same amount.">>

Are there formulas in I7, M7, and Q7 that carry over the data, besides
"average"?

Try to rephrase your question and include the formulas that you're pesently
using.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

I have a spreadsheet that has columns E, I, M, Q
calcualating quarterly averages of total items purchased,
E being first quarter and I second quarter, etc. The
formula for that is simple, E7=B7:D7/3, and so on. Now,
I have a yearly average that needs to be automatically
adjusted as new information is put in to each respective
quarter. If E were populated with an average, I,M,Q
likely wouldn't be, but because there is a formula in all
remaining fields the last amount from the previous column
is carried in to the next and all remaining rows across
show the same amount. So, how can I make this sum if
work below by using else if or something of that nature?
When I use this formula, it will give me E7's sum
correctly and then I7's also when I populate the actual
data with it-but not when I populate it to look at M and
Q.

I hope I am making sense and any help you can give me is
appreciated. I have searched the site and tried array
functions and logical functions without success.

=IF(E7>"I7",E7,(IF(I7<>E7,(I7+E7)/2,(IF(M7<>I7,
(I7+E7+M7)/3,(IF(Q7<>M7,(E7+I7+M7+Q7)/4)))))))

Best regards,
CH
 
Back
Top