Syntax or formula problems in "if" statements

[jbade]

[TABLE="width: 606"]
<tbody>[TR]
[TD="class: xl68, width: 224, bgcolor: transparent"]ARTURO DIAZ
[/TD]
[TD="width: 94, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]$30,000.00
[/TD]
[TD="class: xl64, bgcolor: transparent"]Proportional
[/TD]
[TD="class: xl64, bgcolor: transparent"]Max %
[/TD]
[TD="class: xl64, bgcolor: transparent"]Max $
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]% Change
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]Contribution
[/TD]
[TD="class: xl64, bgcolor: transparent"]Change
[/TD]
[TD="class: xl64, bgcolor: transparent"]Achievable
[/TD]
[TD="class: xl64, bgcolor: transparent"]Target
[/TD]
[TD="class: xl64, bgcolor: transparent"]Actual
[/TD]
[TD="class: xl64, bgcolor: transparent"]% of Target
[/TD]
[TD="class: xl64, bgcolor: transparent"]Achieved
[/TD]
[TD="class: xl64, bgcolor: transparent"]in Pay
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sales Target (#)
[/TD]
[TD="class: xl67, bgcolor: transparent"]50%
[/TD]
[TD="class: xl67, bgcolor: transparent"]5%
[/TD]
[TD="class: xl65, bgcolor: transparent"]$750.00
[/TD]
[TD="class: xl64, bgcolor: transparent"]3000
[/TD]
[TD="class: xl64, bgcolor: transparent"]3500
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]$0.00
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customers Retained/Repeated (%)
[/TD]
[TD="class: xl67, bgcolor: transparent"]30%
[/TD]
[TD="class: xl67, bgcolor: transparent"]5%
[/TD]
[TD="class: xl65, bgcolor: transparent"]$450.00
[/TD]
[TD="class: xl67, bgcolor: transparent"]70%
[/TD]
[TD="class: xl67, bgcolor: transparent"]60%
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]$0.00
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Unpaid Time Off (hrs)
[/TD]
[TD="class: xl67, bgcolor: transparent"]20%
[/TD]
[TD="class: xl67, bgcolor: transparent"]5%
[/TD]
[TD="class: xl65, bgcolor: transparent"]$300.00
[/TD]
[TD="class: xl64, bgcolor: transparent"]15
[/TD]
[TD="class: xl64, bgcolor: transparent"]45
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]$0.00
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]$1,500.00
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]$0.00
[/TD]
[TD="class: xl66, bgcolor: transparent"]0.0%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New Annual Pay
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$30,000.00
[/TD]
[/TR]
</tbody>[/TABLE]


I've not been able to formulate an IF statement that will allow me to complete the "% of Target" column in worksheet above. In essence, I want it to say: IF Actual>=Target, then % of Target = 1+(Actual-Target)/Target. When i have categories like Sales Target and Customers Retained where a larger Actual result is better than a Target, this works fine. BUT, when i have a category where a higher Actual Unpaid Time Off is NOT better than a lower target, I can't seem to come up with the correct Nesting or "sign" to give the correct answer.
Would very much appreciate any help.

 

[Asala42]

It sounds like you want to force the "low numbers = better" metrics to reflect %of Goals over 100% when they do better than their targets. If that's the case you can use an inverse measurement, but it would require some flag to indicate which measurements mean lower numbers = better if you want to use a single formula.

For example putting in a flag :
=IF(MetricDirection="High",Actual/Target,Target/Actual)

So in your example above you have a line item for 45 hours of Unpaid time off with a target of 15, that would reflect the user was 33.3% of target (15/45).

 

[jbade]

Asala42, thank you for taking the time to respond. I'm not familiar with the flag concept, so will have to read further. I'll clarify the example in case you've additional input. RE: Sales Target - If Target <Actual, then calculate the change [(actual-target)/target]. This provides a positive result to which I have added "1" and multiplied that value x Achievable. However, in the situation where I get the Null, (actual is less than target), I subtract the change from "1".

The problem is with the Unpaid Hours where an Actual >Target is a negative result. I can't quite get the formulas and signs to work.

Using your answer, the user is actually (45-15)/15 = 200% below the target. hence my difficulty in setting up the formulas.

What is Metric Direction?

 

[Asala42]

Ah I see, so in that example you'd want to see -200%? Not 33%

To elaborate on the Metric Direction/flag stuff: All I'm going for there is to identify and pass to Excel which measurements are deemed better with lower numbers (Unpaid Time Off) vs. those that are deemed better with higher numbers ($Sales). One way is to add a helper column with entries of "Low" or "High", for example. Then you can reference that in your formula.

If you want to show a %Above or Below Target measurement though that's a slightly different formula from "%of Target".

To Revise you may want to use something like this:
=IF(J4="Low",-1,1)*(F4-E4)/E4

Where
F4 = Actual
E4 = Target
J4 = the text "Low" signifying that Unpaid Time Off prefers a low number, and will in turn reverse the sign.

 

[jbade]

Thank you. Yes, i was thinking i might have to go to helper columns, but was trying to avoid. I'll play a bit more with your suggestion. Thanks again.