percentage calculation from cumulative totals

**bearingman** · 06-04-2010, 04:37 AM

Hi there
I need a formula for finding the performance in a percentage against a target saving and a cumulative total. I've tried various ones but have failed miserably. Everytime I think I've cracked it the percentage keeps going down even when I hit target or go over?!?

Help please

Attached is a sample of what is needed, it will probably be simple for you guys but it has me stumped...

**teylyn** · 06-04-2010, 04:42 AM

For performance agains cumulative target, in E2, copied down

=C2/D2

format as percentage.

Is that what you need? If not, post your expected results.

cheers

**bearingman** · 06-04-2010, 05:04 AM

Not really Teylyn, attached is another attempt which works for a while but then if the target is reached or surpassed then the percentage stiil falls which to me seems wrong?
Thanks for taking the time to look, is it a simple maths error on my part? Am I missing something?

**teylyn** · 06-04-2010, 05:23 AM

bearingman,

you have a formula in column E. If that formula does not deliver what you want, can you please mock up the expected result manually and give a detailed logical description how the result should be achieved?

So far, I see a formula and it's working. If it's not the result you want, describe what you want instead.

**bearingman** · 06-04-2010, 06:12 AM

teylyn
I agree the formula seems to be working but the expected outcome seems wrong? If the target, 760, is achieved or surpassed I would expect the percentage to increase and not decrease?
As I say it may just be a fundamental maths error or misunderstanding but I can't get my head round it?

**teylyn** · 06-04-2010, 08:04 AM

bearingman,

I'm asking for the third time: what is the result you expect. Mock it up.

I have no clue what percentage you want to calculate. Describe in simple words what the logic shoudl be, i.e.

- percentage difference between daily target and daily savings
- percentage difference between accumulative target and accumulative savings

In your formula you reference the accumulated cells. How do you expect the result to be related to the daily target of 760 if you do not even reference that cell?

Unless you can state in words what you want to calculate (even if you don't know the formula, you should be able to describe what you want to see), I can't help you further.

**bearingman** · 06-04-2010, 08:44 AM

It would be the percentage difference between accumulative target and accumulative savings BUT as I have said before, the formula I have in place now SEEMS wrong?

The daily saving target is 760 if it is above, i.e 800, then the percentage shown drops? How can that be?

Basically all I need is a percentage figure showing how much I am above or below the accumulated target figure by entering the savings daily. As you can see the savings will vary and will sometimes be above the 760 and sometimes below.

I DO appreciate your help teylyn BUT as you may have gathered I'm no expert in excel hence my post on here.

**teylyn** · 06-05-2010, 06:28 AM

bearingman, I don't think the problem lies with Excel, but rather with understanding what you are calculating.

Maybe it will help if you visualise the values in a chart. Attached see a line chart with Accumulated Target and Accumulated Savings as two lines.

Note that the gap between the two lines gets less and less. That's what your formula shows as a percentage. You start off with 21% above target, and towards the end you are around 14% above target.

Since your formula calculates against the accumulative values, you see very little change from one value to the next.

The second chart shows the lines for the daily values. Here, you see a more dramatic difference between the day to day values.

I assume that you really want to calculate a performance based on the daily values rather than the accumulated ones.

Anything can be calculated. But you must specify clearly what you want to calculate before you can judge if a result is wrong or not.

If you want to calculate the difference between the daily target and daily savings, then I suggest you use a formula that actually references these cells, like

=(A3-B3)/B3

See attached file, column F.

cheers,