percent average over range of cells

[oneleaf2]

How do I get the "Year-to-date" percentage over a range of cells?

In my worksheet,

-- cells B thru M (Jul03 thru Jun04) show the total for each mont
(i.e. Jul [58], Aug [53], Sep [56])

-- cells N thru X show the percent increase/decrease from month t
month (i.e. Aug [-8.6%], Sep [5.7%])

-- cell Y is my "Year-to-Date" percentage. How do I calcuate th
running percent average here?

Do I use the AVERAGE function?
Upon which range of cells would I make my calculation -- (B to M) or (
to X)?
And how do I avoid error messages in my calculation due to null cell
of the months that do not yet have any data (April thru June)?

Thanks in advance for any suggestions.
I'm clueless on the math as well as the Excel syntax

 

[BrianB]

The worksheet AVERAGE() function should work for you. Set the range t
cover the calculated percentages and empty cells in this area a
required.

NB. The function *excludes empty* cells from the calculation bu
*includes cells that have a zero numeric value*. If you set your vie
to display zero values you will see the difference. If you do see zer
values you can delete these in the normal way to make the cell empty

 

[oneleaf2]

BrianB,

=AVERAGE(N2:X2) (which is Aug04 thru Jun04) gives me the "#DIV/0!
message in Column Y, but only because Mar-Jun cells also displa
"#DIV/0!" since there is no data for these months yet.

However,
=AVERAGE(N2:T2) worked like a charm, since Aug-Feb cells contain soun
percentages.

At least I have a calculation that works, even if I have to update i
every month. So, thank you!

Ultimately it would be great to plug-in some kind of code that woul
filter out these "#DIV/0!" cases, and calculate the YTD% without havin
to do any work at all!

cheers,

oneleaf

 

[AlfD]

Hi!

Did I see you were taking an average of the percentages?
This is a tricky area, since it only works accurately if each of th
percentages is calculated using the same denominator.

A silly example, but a serious one too:

Increase 1 = 20%
Increase 2 = 10%

Average of 20 & 10 is 15.

But: start with £100 and increase by 20% --> £120.
Then take £120 and increase by 10% --> £132.

Overall increase = 32%.
Average increase = 16%.

These errors will build up as you continue the process (just a
compound interest does).

You need to go back to the raw figures (the ones the percentages cam
from) and do a rolling sum --> rolling average on those, if you want t
be precise.

Al

 

[BrianB]

Generally we avoid error messages due to div by zero by something lik
:-

=IF(B1=0,0,A1/B1