Average with more than one condition

[wasafiri]

Hi guys.

So I have a spreadsheet that estimates my earnings per month. Since my earnings really are in a world over their own over the weekends (they are only 30% of normal) I am trying to count weekends separately.

I've set up Column A - day of the month, Column B - which tells me "true" if the day in question falls on a weekend, and "false" if it falls during the week. Column C tells me my earnings for that day.

With me so far? Ok, so here's the formula I've come up with that tells me my average earnings during the week:

Please Login or Register  to view this content.

and this formula tells me my average earnings during weekends:

Please Login or Register  to view this content.

However, the one glaring problem in these formulas is that they incorrectly handle values of zero. Supposing a month is 31 days long, and it's the 16th of the month - the formula assumes that the 0s from the 17th of the month until the 31st of the month are actual earnings! So the formula doesn't represent my true average, not unless it "ignores" the zeroes.

How do I force it to ignore zeros?

For instance, for avg. minutes per weekday the formula needs to find the average earnings for days marked "False" and only consider numbers that are >0.

Appreciate any help you can give!!!

[bentleybob]

SUMIFS and COUNTIFS.

[Cutter]

Welcome to the Forum but it's just a matter of time before a Mod asks you to change your thread title so I'll give you a heads up by referring you to the Forum Rules.

Click on the Forum Rules link at the top of the page and pay particular attention to Rule #1.

[wasafiri]

SUMIFS and COUNTIFS.

What do you mean?

Welcome to the Forum but it's just a matter of time before a Mod asks you to change your thread title so I'll give you a heads up by referring you to the Forum Rules.

Click on the Forum Rules link at the top of the page and pay particular attention to Rule #1.

Thanks, oversight on my part, edited for clarity.

[Cutter]

Try something like:

=SUMIFS(C2:C32,C2:C32,">0",B2:B32,"FALSE") to give you the sum of values that are above 0 and adjacent to FALSE.

Here are links you may be interested in: SUMIFS(), COUNTIFS() and AVERAGEIFS()

http://www.excelfunctions.net/Excel-...-Function.html

http://www.excelfunctions.net/Excel-...-Function.html

http://www.excelfunctions.net/Excel-...-Function.html


EDIT: You can just change the "SUM" to "AVERAGE" in the above formula to get what you want. To get a count just change the "SUM" to "COUNT" and remove the first "C2:C32,".

[wasafiri]

Try something like:

=SUMIFS(C2:C32,C2:C32,">0",B2:B32,"FALSE") to give you the sum of values that are above 0 and adjacent to FALSE.

Here are links you may be interested in: SUMIFS(), COUNTIFS() and AVERAGEIFS()

http://www.excelfunctions.net/Excel-...-Function.html

http://www.excelfunctions.net/Excel-...-Function.html

http://www.excelfunctions.net/Excel-...-Function.html


EDIT: You can just change the "SUM" to "AVERAGE" in the above formula to get what you want. To get a count just change the "SUM" to "COUNT" and remove the first "C2:C32,".

Beautiful.... thanks. I was expecting to need some sort of macro - thanks for the very simple solution!

[Cutter]

You're welcome.

Don't forget to mark your thread as SOLVED - click the FAQ button at top of page for instructions (if needed).