Formula when Goal is a Negative Number

[Montano]

I need to do a formula for a bonus spreadsheet. The goal is a negative number. I need to write the formula for the % achieved of goal so that it works whether the actual is negative or positive against the negative goal.

Also, the result of the answer requires two qualifiers: If it less than 80%, the percent achieve must default to zero; and if it is greater than 120%, is must default to 120% max.

Can you tell me how to achieve this? Can it be done in one cell?

 

[cranebill]

Yes it can be done in one cell... what do you mean by the goal is a negative number.

What cells are you using to determine % and whatnot...

 

[Montano]

Here is an example:

Goal (7,000)
Actual - can be a positive number or negative against the
goal of (7,000)
% Achieved: _______

If this category represents 40% of the bonus plan, then the % achieved is used to arrived at how much of the 40% is achieved. However, the qualifier is that anything below 80%, they get zero % of the bonus, and it also maxes out at achieving 120%.

Thanks!

 

[cranebill]

Oh ok... so the "Actual" isn't a negative number like -1. Rather it is just a whole number that is more or less than "Goal" correct?

 

[AnotherHiggins]

Try this:

=IF(A3>=A1*0.8,0,IF(A3<=A1*1.2,1.2,A3/A1))

Where A1 is the Goal and A3 is the actual.

It doesn't really matter that your goal is -7000 as opposed to +7000, all that changes is the "greater than" and "less than" signs are transposed.

John

That which does not kill me postpones the inevitable.

 

[AnotherHiggins]

cranebill,

FYI: (7000) is negative 7000.

John

That which does not kill me postpones the inevitable.

 

[AnotherHiggins]

Oh yeah, Montano, be sure to format the cell with the formula in it as percentage.

John

That which does not kill me postpones the inevitable.