Average Non-Adjacent Cells in Budget Spreadsheet

[OPB3]

I have Budget SpreadSheet thet is set by month(U3), then Budget(U4),
Actual(V5) columns. This is repeated 12 times across the sheet. I then
have "Buget Average" and an "Actual Average" cell. The budget average
is easy because all cells have values for all 12 months. How do I
average the actual since only a few months have data?

This shoud be simple but I have read 200 posts and have not found
anything. All suggestions were for ranges/adjacent cells.

 

[Bob Phillips]

Just run AVERAGE as normal, it ignores blank cells

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

=(F10+H10+J10+L10)/(COUNTA(F10,H10,J10,L10)

This formula should work where F10, H10, J10, L10, etc are your Actuals and
where a zero is entered for any past month with no Actual data.

 

[Harlan Grove]

Tim Whitley wrote...

=(F10+H10+J10+L10)/(COUNTA(F10,H10,J10,L10)

....

Why not just use

=AVERAGE(F10,H10,J10,L10)

?

 

[OPB3]

This is close but it only works on on rows (AH7, AH8, etc) without m
subtotals (AH9). I have attached a pdf of the spreadsheet (I Think) an
this is the actual formula:

=(J7+L7+N7+P7+R7+T7+V7+X7+Z7+AB7+AD7+AF7)/(COUNTA(J7,L7,N7,P7,R7,T7,V7,X7,Z7,AB7,AD7,AF7))

The row with the subtotal has a zero placed due to the formula so th
future months that have no actuals skew the average. The AVERAG
function alone, as suggested, does not work at all.

I am a "newbie" to excell and appreciate the help.

=(F10+H10+J10+L10)/(COUNTA(F10,H10,J10,L10)

This formula should work where F10, H10, J10, L10, etc are your Actual
and
where a zero is entered for any past month with no Actual data.

I have Budget SpreadSheet thet is set by month(U3), then Budget(U4),
Actual(V5) columns. This is repeated 12 times across the sheet. then
have "Buget Average" and an "Actual Average" cell. The budge average
is easy because all cells have values for all 12 months. How do I
average the actual since only a few months have data?

This shoud be simple but I have read 200 posts and have not found
anything. All suggestions were for ranges/adjacent cells.

+-------------------------------------------------------------------
|Filename: Budget2005 (20050726).pdf
|Download: http://www.excelforum.com/attachment.php?postid=4675
+-------------------------------------------------------------------

 

[Aladin Akyurek]

This is close but it only works on on rows (AH7, AH8, etc) without my
subtotals (AH9). I have attached a pdf of the spreadsheet (I Think) and
this is the actual formula:

=(J7+L7+N7+P7+R7+T7+V7+X7+Z7+AB7+AD7+AF7)/(COUNTA(J7,L7,N7,P7,R7,T7,V7,X7,Z7,AB7,AD7,AF7))

The row with the subtotal has a zero placed due to the formula so the
future months that have no actuals skew the average. The AVERAGE
function alone, as suggested, does not work at all.

[...]

Given the structure of the formula you posted:

=AVERAGE(IF(MOD(COLUMN(J7:AF7)-COLUMN(J7)+0,2)=0,IF(J7:AF7>0),J7:AF7)))

which needs to be confirmed with control+shift+enter, not just with enter.