Excel 2007 : Conditional Calculations

[JukeboxRobo]

Hi Folks,

Here's what I'm trying to do.

I'm trying to get a weighted average for a series of numbers, but if certain numbers in a column A are errors or negative numbers, I want to ignore them and still maintain the weighting. Check the data for clarification

I have to do this for a large series of numbers, which is why an conditional statement would be easier.

[Miraun]

If you want to leave column E in there, you can use;
=SUMIF(E3:E7,">0")

[JukeboxRobo]

If you want to leave column E in there, you can use;
=SUMIF(E3:E7,">0")

Hi Miraun,

I appreciate your help.

Unfortunately, due to the way the spreadsheet was set up, it doesn't have the space to keep something like column E. If I have something like Column E and try to hide it, it messes with the spacing. Is there any way I could do it through conditional statements in one cell?

[TMS]

E3: =IFERROR(B3*C3,0) and copy down ( to eliminate #N/A errors)

E8: =SUMIF(E3:E7,">0",E3:E7) to eliminate zero values


Regards

[TMS]

If you can eliminate the #N/A in column B, you can use:

=SUMPRODUCT(--(B3:B7>0),--(B3:B7),--(C3:C7))

Regards

[JukeboxRobo]

TMShucks, this works perfectly! Thank you! I also added to your reputation.

[TMS]

You're welcome. Thanks for the rep.

See my signature for details of how to mark your thread solved.

Regards, TMS