hi driller
i couldnt agree more!!! i've made a mess out of trying to explain but here
goes another try.

Budget Goal Actual Diff +/-
100 95 95 5 formula below
What i need to see is:
If (Actual is less than Goal then Diff increases by .12, If actual is less
then Goal then Diff decreases by .96)

Budget Goal Actual Diff +/-
100 95 95 5 100
100 95 96 5.12 101
100 95 97 5.24 102
100 95 94 4.04 81
100 95 93 3.08 62
100 95 92 2.12 42

crossing my fingers to see if this helps. thanks again for helping.
gma









"driller" wrote:

gma,

i suggest to refresh/restart the questioned *subject* so you may receive
suggestive formula(s) from others that can able to analyze your query based
on fresh n specific infos provided.

--
regards

"gma" wrote:

hi driller
the formulas a
G5 is =B5-F5 or "Budget"-"Actual"
H5 is =min(200%,G5/E5) or "Diff Budget:Actual"/Savings Target"
Hope this helps and again THANK you for trying to help.

have a great day
gma

"driller" wrote:

gma,

Also pls. include again your new draft formula for G5 & H5.
B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 105 5.67 113%
100 5.00% 95 5 90 4.73 95%

without a draft formula to support your input on columns G & H, it will be
very hard for both of us to guess the relative formulas along every
additional lines.

you're welcome, hop this will help and have a great day too.
+regards+

"gma" wrote:

hi driller
i've inputted the number as you requested below plus i added additional
lines showing if the actual number drops below 90. i dont know what the
formulas are suppose to be in G5/H5, that my problem. those are the two
formulas that will calculate the numbers i'm trying to achieve.

Thanks for taking the time to help. Hope this information helps. Have a
great day.
gma


"driller" wrote:

gma,

others. as the actual number G5 <94 falls below the goal <95 i want the dif
budget/act number to have a minus sign, thus making the gain/loss to also
have a minus sign.

it will help if you place your new desired results on few "?" below where
the supposed *minus sign* on H5 is applicable. Also pls. include again your
new draft formula for G5 & H5.

B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 5 100%
100 5.00% 95 5 105 5.67 113%
100 5.00% 95 5 90 4.73 95%
100 5.00% 95 5 100 5.37 107%
100 5.00% 95 5 94 4.97 99%
100 5.00% 95 5 89 -1 -20%
100 5.00% 95 5 88 -2 -40%




hop this will help,
-regards-


"gma" wrote:

hi driller
i am satisfied except i want this line of the budget to be oppsite of the
others. as the actual number G5 <94 falls below the goal <95 i want the dif
budget/act number to have a minus sign, thus making the gain/loss to also
have a minus sign.
subsequently if the actual number is above the goal then the oppsite happens
the number increase. again the formulas are for the goal/s to go down. i need
this line to increse the numbers. thanks for being patient with me. i'm not
very good with excel.
gma


"driller" wrote:

gma,

it seems like your already satisfied with your enumerated formulas,
especially along the G5 & H5, based from the sample.
B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 103 13 130%

Or are there anything more you need to explain furthermore ?
Hop this may help.

regards,
"gma" wrote:

driller
hey its not your fault...i'm not explaining very well. the formula is set up
like this.
"budget-B5" is a fixed number <"100" i come up with from yearly budget.
"target savings %-C5" <5%is a fixed number also, and it represents further
savings of the budget and becomes the "goal-D5" or "95". The formula in
D5-goal is =abs((B5*C5)-B5) the "savings target-E5" formula is simply =B5-D5
or "budget-goal". The "diff budget/act-G5" formula is =B5-F5. As long as this
number equals the "savings target" number then you've met your "goal" and the
"gain or -loss" number is 100%. The "gain or -loss-H5" formula is
=min(200%,G5/E5). I have the min set because i have capped the gain. All the
other lines I have for this bonus works fine because the lower the number in
the "actual" the "gain or -loss" increases which is what i want. This
particular line, as the "actual" number goes up i need the "gain or -loss" to
go up or as it goes down the "gain or -loss" goes down. You asked if the
"-loss%" represents a negative number, it can. If the "actual" number falls
below the "budget" number then it put the "gain or -loss" to a negative
number. This is why i put in this formula a cap at 200% because the "actual"
could make the "gain" number go indefinite. Like its hard to explain, i'm
putting the formulas below. maybe if you put them in a spreadsheet you can
see what i'm tring to do.
CELL B5-BUDGET Fixed number "100"
CELL C5-TARGET SAVINGS % Fixed number "5%"
CELL D5-GOAL =ABS((b5*c5)-b5)
CELL E5- SAVINGS TARGET =B5-D5
CELL F5- ACTUAL Data input from P & L for the quarter
CELL G5- DIFF BUDGET/ACT =B5-F5
CELL H5F- GAIN OR -LOSS =MIN(200%,G5/E5)

Lots of stuff but i hope this helps. I REALLY appreciate you helping me.
thanks
gma

"driller" wrote:

gma....sorry for not understanding your point very well, your bonuses plan
have in H5 "gain or-Loss" column, does the "-Loss" means a negative % ? How
to know its a -Loss ?

For G5 : is there a proportionating formula or this is a "series of count
+/-" from *5* ?

lastly please fill in your value at the extended last 2 rows in the table
with "?" below

B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 *5* 100%
100 5.00% 95 5 98 6 120%
100 5.00% 95 5 93 4 80%
100 5.00% 95 5 105 7 140%
100 5.00% 95 5 103 13 130%
100 5.00% 95 5 94 4 82%

regards


"gma" wrote:

driller
as you can see any thing less than "95", decreases the "budget/act" and the
"gain/loss", but or the other hand any thing more than "95" will increase
those same numbers. i know i'm not explaining myself very well but this
spreadsheet is hard to explain. its a bonus plan for my managers. i wish i
could just email you a copy of the forumlas and you could see better what i'm
trying to do, or maybe what your asking below will help. hope this helps

"driller" wrote:

Hi gma,

pls fill values on "?" as sample explanation for a quicker approach.

B5 C5 D5 E5 F5 G5
H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 5 100%
100 5.00% 95 5 98 6 120%
100 5.00% 95 5 93 4 80%
100 5.00% 95 5 105 7 140%

regards

"gma" wrote:

Hi driller
i'm not explaining myself very well.
B5 C5 D5 E5 F5 G5
H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 5 100%
the way this works is: there is a budget number,B5 100, thats fixed or a
number we have set as the yearly budget ,and a goal
number,D5=abs((b5*c5)-b5), thats driven from the "% savings" C5 5%. the %
savings number is also fixed that we have set as a target savings of the
budget or in other words, we have a budget number but i'm asking that we also
try and beat the budget number by 5%, thus the target E5 =B5-D5 becomes the
number 5. the actual number is from the average of the quarter say 95. as the
actual number goes up i need G5 to increase or if the actual number decreases
i need G5 to decrease. Again not sure if i'm explaining myself very well.





"driller" wrote:

gma,

interpreting A1 as the index for your target f1, try with the following +/-
formula as fallows;

d1 = "input as-actual value"

Assumed as a respective input,
"When d1 is changed by either a smaller or larger value then i need e1 to
increase or decrease".
thus,
e1= (b1-d1)+c1
and where c1 is the goal for savings
f1=e1/c1 *100

so if actual/d1 = 98 ; then diff/e1 = 2 and gain/loss = 40.


--
regards,
driller

"gma" wrote:

i need to increase or decrease a cell. A1 is the consent value or base. B1 is
the goal which is a smaller value and d1 is the actual value. When d1 is
changed by either a smaller or larger value then i need e1 to increase or
decrease.
base goal savings actual diff gain/loss
A1 b1 c1 d1 e1 f1
100 95 5 95 5 100
fixed =a1-b1 moving =d1/a1 =e1/c1
if i change actual/d1 to say 98 i need the diff/e1 to increase or if i
change it to say 93 i need it to decrease. help!!