Forum Discussion
Creating an array
Okay that is not working, it is only giving me the .5% total, I need it to give me a .5% if the total in column D is less than $25,000. If the total in D3 is in between $25,000 and $32,000, I need it to total the amount in D3 times .75%, and if the total in D3 is greater than $32,000, I need to total in E3 to be, D3 times 1.%
Hopefully that makes better sense!
I think Sergei may have had it right the first time, but there was one typo. I think the formula should be:
=D3*LOOKUP(D3,{0,25000,32001},{0.005,0.0075,0.01})
Oops... Yes, sorry for the typo
Shelley Maness wrote:I think Sergei may have had it right the first time, but there was one typo. I think the formula should be:
=D3*LOOKUP(D3,{0,25000,32001},{0.005,0.0075,0.01})
Also, I'm not sure how critical this formula is, but you should be aware that if Column D amounts could potentially include cents, the previous formula would apply 0.75% to amounts between $32,000.01 and $32,000.99. It's a very tiny margin of error, but these amounts are technically greater than $32,000 so they should be multiplied by 1.0%. To be precise, you should change the formula to:
=D3*LOOKUP(D3,{0,25000,32000.00},{0.005,0.0075,0.01})
