Help on a anlysis formula

[Allierz]

I have 6 cells

A1 Customers Name
B1 Customers Logo
C1 Sales Jan - Dec 2001
D1 Sales Jan - Dec 2002
E1 Sales Jan - Dec 2003
F1 Sales YTD Lan - April 2004
G1 $ Difference 2002 - 2003
H1 % Difference 2002 - 2003
Cells A1 to E1 are entered manually
Cell F1 has a formula: =D2-C2
Cell G1 has a formula: =D2/C2-100%

I need to add another column to arrive a comparison of sales YTD in Cell
F1 and compare this to the figure in Cell E1 but for the same period.
Firgure in E1 is for 12 months, not 4 months. The answer I an looking
for is the % of diferrence be that + or -.

The sales sheets are spilt into Areas or Reps so they vary in the amount
of customers and are totalled at the bottom.

The other problem that I see is do I need to alter the formula every
month or can a formula be based on a date? The above is for figures at
the end of April. What happens in May?

Trust this is clear.

Many thanks for your help.

Allie

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Trevor]

First of all, I'll assume that you have column titles in row 1, and that
your values and formulas really start in row 2.

Also, I assume that you meant that Cells A2 to F2 are entered manually.
Similarly, I assume you meant that G2 is =E2-D2 (since I don't think you
meant that YTP Sales is 2002 sales minues 2001 sales) and that H2 is
=E2/D2-100% (which I wrote as =E2/D2-1 and formatted as a percent).

Finally, I assume taht if we're in May, then you'll enter YTD sales in
column F through last month (April).

Therefore, I suggest that you use the following formula in cell I1 to
calculate your column title:
="Against "&TEXT((MONTH(TODAY())-1)/12,"00%")&" of last year"

Then, starting with I2, you can place the following formula and copy it all
the way down the column:
=F2/(E2*(MID(I$1,9,2)/100))-1

This will give you the % difference between the YTD sales this year from the
equivalent portion of last year. Please rememebr, though, that this assumes
that your sales were steady last year; it will NOT show you if your sales
are exceeding or trailing last year's performance at this time. In order to
do that, you'd have to have the sales for last year broken into the same
time periods that you're comparing to.

Note that if you'd rather not use the column title as the place where you
determine how much of this year is represented in the YTD column, you could
put the following formula in a place like J1: =MONTH(TODAY())-1/12 and then
you formula down the column becomes =F2/(E2*K1)-1