gain-loss

[gma]

A1 B1 C1 D1 E1 F1 G1
100 5.00% 95 5 95 5 100%
i need G1 to increase as E1 increases. the way it works now if E1 increases
then G1 decreases. the way the formula works is A1 is the base B1 is the
targeted percentage differance between A1 and C1. F1 is the gain or loss of
E1 and C1. So if E1 goes below C1 then I need G1 to decrease and if E1
increases then G1 needs to increase. Can someone help?

 

[Fred Smith]

We can try to guess what's going on, but that probably won't help you very
much. To get help that's of value, post the formulas you are using.

Regards,
Fred.

 

[Shane Devenshire]

Hi,

What formulas do you currently have in place and what is the logic for each
calculations supposed to be?

I'm going to take a guess:

in G1 enter:

=E1/C1

maybe you have this as =C1/E1?

 

[gma]

Here goes:
A1 DATA INPUT CELL Budget Number "100"
B1 DATA INPUT CELL Target Savings % "5%"
C1 =ABS((A1*B1)-A1) Goal "95"
D1 =A1-C1 Savins Target "5"
E1 DATA INPUT CELL Actual "95"
F1 =A1-E1 Diff Budget/ Actual "5"
G1 =MIN(200%,F1/D1) Gain or Loss percentage "100%"

If the data inputed to cell E1 is lower then the "Actual" number then I need
G1's number to drop. If the number is higher, then I need to to increase.
Does this make sense?

 

[Fred Smith]

In G1, do you want gain/loss percentage from Target? Is that the idea. If
the target was $95, and the actual is $90, you've "gained" (saved) $5. So
you want (95-90)/95 = 5.3%. To calculate percentage difference, it's:
=(original - new) / original

In your case, that's
=(c1-e1)/c1

If you want the difference from budget, as opposed to target, it's:
=(a1-e1)/a1
or
=f1/a1

Also, your calculation in C1 should be:
=a1-a1*b1

There's no value in the ABS function here, and it will mask errors in data
entry.

Regards,
Fred.

 

[gma]

The objective is to have the 'gain-loss" go up in value as the "Actual"
number goes up in value and the reverse if it goes down.The base is "100" and
the goal is 95. If the "Actual is 95 than the gain-loss would be 100. If the
actual goes to 98 than i need the gain-loss to go to something like 105. Does
this make sense?
Target
Savings Savings Diff
Budget % Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 5 100%