Help please! Running mothly cumulative average vs target

R

Rob B

Can anyone help me with this? I have two rows, target revenue and
actual revenue. The target revenue cells are all filled out at the
beginning of the year. As months pass and actuals are recoreded, the
last cell (current cumulative average) is updated. Row1 is the sum of
the targets and Row2 is the sum of the actuals. My problem is that I
want the sum of the targets (used in the cumulative to date
calculation) to only go as far as the actuals go.

So, for instance, if we are in February and revenue for the first two
months is 1000 total target, and actuals is 900, the output of the
current cumulative cell is 90%. However, that is if I manually do not
sum the target column, but instead sum the first two months. If I
have the 'sum the entire row like the actuals, the yearly taregt
revenue is a large number like 6000 and the resulting percentage is
around 16%, a much lower number.

Any ideas? Thanks,

Rob
 
F

Frank Kabel

Hi Rob
try the following to calculate the total for row 2 (assumtpion the data
starts in column A)
=SUM(OFFSET($A$2,0,0,1,COUNTA($1:$1)))

HTH
Frank
 
R

Rob B

Hi Nigel,

It keeps telling me I have too few arguments. Because of formatting,
the target row is B10:M10 and the actual row is B11:M11. Using your
formula with my rows, I've typed the following formula in the
cumulative target row:

=SUMIF(B10:M11),">0",B10:M10)

Any ideas? Thanks,

Rob
 
R

Rob B

Ok, never mind, I figured it out. I used the following formula pasted
from my excel worksheet:

=SUMIF(B11:M11,">0",B10:M10)

so using your formula, it would have been:

=SUMIF(B3:M3,">0",B2:M2)

Thanks for putting me on the right track!!!!! :)

-Rob
 
Top