Mar 24 2020 06:54 PM
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)))
Mar 24 2020 11:37 PM - edited Mar 24 2020 11:39 PM
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))
Mar 25 2020 01:14 PM
@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!
Mar 25 2020 10:38 PM
@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!
Mar 26 2020 11:01 AM
@Riny_van_Eekelen Well you could've fooled me! LOL Thanks for your help. Much appreciated. :)
Mar 24 2020 11:37 PM - edited Mar 24 2020 11:39 PM
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))