Help please! Running mothly cumulative average vs target

  • Thread starter Thread starter Rob B
  • Start date Start date
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
 
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
 
Use the sumif function in the target total cell to derive the number of
actual filled in actual row
Assuming the targets are in B2 to M2 and actual are in B3 to M3 then
=SUMIF(B32:M3),">0",B2:M2)

The only potential flaw is that if the actual for the month is zero then the
sum wont change, but then your sales director might be looking for a new job
as well!!

Cheers
Nigel
 
Sorry B2:M3 in first function not B32:M3 !

Nigel said:
Use the sumif function in the target total cell to derive the number of
actual filled in actual row
Assuming the targets are in B2 to M2 and actual are in B3 to M3 then
=SUMIF(B32:M3),">0",B2:M2)

The only potential flaw is that if the actual for the month is zero then the
sum wont change, but then your sales director might be looking for a new job
as well!!

Cheers
Nigel







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
Click to expand...
=---
 
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
 
Back
Top