Average Non-Adjacent Cells in Budget Spreadsheet

  • Thread starter Thread starter OPB3
  • Start date Start date
O

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.
 
Just run AVERAGE as normal, it ignores blank cells

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
=(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.
 
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.


Tim said:
=(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.



OPB3 said:
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.
Click to expand...

+-------------------------------------------------------------------
|Filename: Budget2005 (20050726).pdf
|Download: http://www.excelforum.com/attachment.php?postid=4675
+-------------------------------------------------------------------
Click to expand...
 
OPB3 said:
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.
Click to expand...
[...]

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.
 
Back
Top