SOLVED

formula help please

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

@Badger_Z Actuallt, I made a mistake in my previous post. I forgot to add the word "not" before "a mathematician". I really am not! That's why I couldn't figure out why you had used LN to capture the 10%. Anyhow, glad that I could you help you!

Highlighted

@Riny_van_Eekelen Well you could've fooled me!  LOL  Thanks for your help.  Much appreciated.