Forum Discussion
Creating an array
Hi!
I am trying to set up a spreadsheet that takes the total $ amount of one cell, and gives me a figure in another cell, based on whether the $ in the first cell is less then, greater than, or in between a certain $ amount.
Example
| A | B | C | D | E | F | G |
| DATE | Lot # | NAME | Design Total | 0.5% | 0.75% | 1.00% |
| 4/16/18 | 218 QM | Allen | $ 31,816.00 | 159.08 | 318.16 | |
| 4/17/18 | 113 HVM | Stauffer | $ 17,848.00 | 89.24 | 133.86 | 178.48 |
| 4/18/18 | 306 KC | Liegel | $ 15,601.00 | 78.01 | 117.01 | 156.01 |
I want column E to give me .5 if the $ in column D is less than $25k, I want it to give me .75% if the $ is in between $25,001 and $32,000, and I want it to give me 1% if the $ in column D is greater than $32k
Can you help me so that I can eliminate column F and G and just show the amount, if, it meets the criteria, just in column E?
Thank you!
Amy
Hi Amy,
That could be
=D3*LOOKUP(D3,{0,25000,32001},{0.005,0.075,0.01})in E3 and down.
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})
