SOLVED

Highlighted
New Contributor

I'm having trouble with nested if formulas (I think).  I have a few different cells I'm trying to work but the idea is similar for most:  If a number is equal to, greater than, or less than a certain target, I need 3 different formulas for each of the conditions.  Example: If G24 = G25, then the value in H24 should be .75.  If G24 > G25, (anywhere up to 10% greater than G25) then the value in H24 should be on a scale from .75 to a max of 1.0.  If G24 < G25, then the value in H24 should be scaled down from .75.  I feel this should be simple but I'm struggling.

Below is the formula I had been using, which seems to work for the less than and equal to formulas, but I want is to where when G24 is exactly at 110% of G25, the value in H24 is exactly 1.0.  (Essentially what I'm saying is if I increase my performance by 10%, I get max "points" whereas if I just maintain status quo of 100%, I only get 75%).  Any help is appreciated!

=MIN(1,IF(G24<\$G\$25,(G24/\$G\$25)*0.75,IF(G24>\$G\$25,(LN(G24/\$G\$25)+0.75),0.75)))

4 Replies
Highlighted
Best Response confirmed by Badger_Z (New Contributor)
Solution

@Badger_Z I'm a mathematician and perhaps therefore don't understand the your intentions with the LN function. But if I interpret your question correctly, I believe the following formula works as intended:

``=IF(G24<\$G\$25,(G24/\$G\$25)*0.75,IF(G24>=1.1*\$G\$25,1,0.75+(G24-\$G\$25)/(\$G\$25*10%)*0.25))``

Highlighted

@Riny_van_Eekelen That looks like it worked! I don't know the rationale behind the LN formula.  I'm modifying a worksheet that has been in use since 2012 so I don't know the original intent of the LN formula.  I'm no mathematician so I really appreciate your help! Thank you!

Highlighted