Hi all,
I am in desperate need of some help with my IF statement (example attached). I am trying to say if column K (Rate Diff) is less than -0.25% then return a result of $0.00 (i.e. no bonus is paid for anything written 0.25% or more below the min rate). If it is -0.25% or above, then lookup the Bonus % on the LOOKUP REF tab (column 3) and multiply the percentage by column I (NAF). I can get it to work for anything less than 0, but adding in the negative just won't work for all rows. It's driving me mad. Here is what I currently have:
=IF($K2<-0.25,0,VLOOKUP($A2,'LOOKUP REF'!$A$3:$C$3,3,0)*$I2)
Can anyone help?
Thanks in advance!
Last edited by sambo77; 09-27-2011 at 03:29 AM.
Since you are dealing with percents, your test point must be 1/100th of the decimal value. Try:
Other than that, I'm not sure if you intend a VLOOKUP or HLOOKUP based on your lookup table.=IF($K2<(-0.0025),0,VLOOKUP($A2,'LOOKUP REF'!$A$3:$C$3,3,0)*$I2)
Cheers,
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
I think you just left out the % sign.
=IF($K2<-0.25%,0,VLOOKUP($A2,'LOOKUP REF'!$A$3:$C$3,3,0)*$I2)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Originally Posted by JBeaucaire
Wow, I had a feeling it would be something that simple that I had left out. I just added in the % sign and it's working a treat! Thanks so much JBeaucaire!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks