Excel - Count if and running average formula help 2

[ITALIAORIANA]

Hi,
I am trying to get a running average in a column that contains formulas. Right now I have several columns based on employee types(home worker, part time, piece rate etc..)Then I have a totals column for all employees by month.

The average formula I have to get a running average for the columns is =IF(COUNT(B3:B9)>0,AVERAGE(B3:B9),"") - This works fine and does not count the cell if it is blank.

The totals column however has a formula to add all the different employee types by month. The formula continues to count all the cells to average because (I think) it's reading the formula as a value.

Is there a way I can tell the totals column to give me a running average of the totals without counting the formula?

Thanks
Deana

 

[SkipVought]

Hi,

Check out the SUBTOTAL function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Hi Skip,

Thanks for the subtotal suggestion but it's not working either. Here is what I used - =SUBTOTAL(1,G3:G9)
There are a total of 7 cells in the column - only 2 of them have a value.
142 and 97 so the average should be 119.5 since it should only count the fields with values. But it's showing 34.1 because it's still averaging based on all 7 cells.

Thanks
Deana

 

[SkipVought]

Strange. When I use your formula and enter the two values in that range, I get 119.5.

However, if I enter ZEROS on all blank cells, I get 34.14285714.

ZERO is a valid number. "" is not and is ignored. Use formulas like...
[tt]
=if(isblank(H2),"",H2)
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

The totals column however has a formula to add all the different employee types by month. The formula continues to count all the cells to average because (I think) it's reading the formula as a value.

Please provide that formula.

I want to see the formula in column B because Excel's default behavior is to ignore empty cell and zero-length strings* in either the AVERAGE or SUBTOTAL formulas. So if those formulas are returning "", then your original AVERAGE formula should have worked as expected.

*A zero-length string is what you get when your formula returns "", as in the formula you did provide.


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ITALIAORIANA]

Hi John,

I am using the following formulas. The total workers column and average row are the only cells that contain formulas - all other cells are manual input cells.

=SUM(B3:F3) - In the total workers column

=IF(COUNT(B3:B9)>0,AVERAGE(B3:B9),"") - In average row at bottom of all columns excetp total workers

=SUBTOTAL(1,G3:G9) - In average at the bottom of total workers column

Example of spreadsheet:

Home Piece Total
Worker Rate Hourly Salary Workers
Jul-09 10 12 55 20 142
Aug-09 13 12 10 45 97
Sep-09
Oct-09
Nov-09
Dec-09

Average 11.5 12.0 32.5 32.5 34.1

I can't get


Thanks
Deana

 

[ITALIAORIANA]

sorry, i hit submit before i was done typing

i can't get the formula in total workers average to give me a running average like the other columns do.

 

[SkipVought]

=SUM(B3:F3) - In the total workers column

Rather...
[tt]
=if(SUM(B3:F3)=0,"",SUM(B3:F3))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Thanks so much for your help. I found this formula that works but now if the other cells are empty I get #DIV/0! -

=SUM(G3:G9)/(SUMPRODUCT((G3:G9<>0)*1))

I tried incorporating =if(SUM(B3:F3)=0,"",SUM(B3:F3)) from skip to not show the #DIV/0! I obviously didn't do it right because now it shows False

Here is what I combined
=IF(SUM(G3:G9)=0,""/SUMPRODUCT((G3:G9<>0)*1))

 

[ITALIAORIANA]

Wow - I have been working on this spreadsheet all day long.

Thanks Skip and John for helping me.

Here is the final formula to calculate the total running average without the #DIV/0! error.

=IF(SUM(G3:G9)=0,"",(SUM(G3:G9)/(SUMPRODUCT((G3:G9<>0)*1))))

Phew!!!

 

[SkipVought]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}