if within a range, then multiply by X

[tommyloan]

Hello... I need help on using a particular multiplier based on ranges. I am using the SUM function to add column I4 thru I15, and give the total in I16. Then, I need to multiply the number in I16 by the correct multiplier (based on the below chart) and show the result in I17. So for example, if the total of I4 thru I15 is shown in I16 to be $472,000, then I need to multiply by 0.43% and show $2,029.60 in I17. Or if total sales is $2,780,000, then I need to multiply by 0.72% and show $20,016 in I17. In the below chart, "0" is H44, "$499,000" is I44, and "0.43%" is J44. So the chart is saying: if $0 thru $499,999, multiply by 0.43% / if $500,000 thru $999,999 multiply by 0.47% / etc, etc. Thank you in advance.

[TABLE="width: 201"]
<tbody>[TR]
[TD]0[/TD]
[TD]$499,999[/TD]
[TD]0.43%[/TD]
[/TR]
[TR]
[TD]500000[/TD]
[TD]$999,999[/TD]
[TD]0.47%[/TD]
[/TR]
[TR]
[TD]1000000[/TD]
[TD]$1,499,000[/TD]
[TD]0.57%[/TD]
[/TR]
[TR]
[TD]1500000[/TD]
[TD]$1,999,999[/TD]
[TD]0.62%[/TD]
[/TR]
[TR]
[TD]2000000[/TD]
[TD]$2,499,999[/TD]
[TD]0.67%[/TD]
[/TR]
[TR]
[TD]2500000[/TD]
[TD]$2,999,999[/TD]
[TD]0.72%[/TD]
[/TR]
[TR]
[TD]3000000[/TD]
[TD]$3,499,999[/TD]
[TD]0.77%[/TD]
[/TR]
[TR]
[TD]3500000[/TD]
[TD]$3,999,999[/TD]
[TD]0.82%[/TD]
[/TR]
[TR]
[TD]4000000[/TD]
[TD]$10,000,000[/TD]
[TD]0.87%[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Joe4]

You can do this using VLOOKUP, specifically by setting the last (4th) argument to TRUE.

So, enter that chart somewhere in your workbook, highlight it all, and name the range (I'll use the name "MyRange" for this example).
Then, you can use the following formula in cell I17:

=VLOOKUP(I16,MyRange,3,True)

See here for an explanation of how it works: https://exceljet.net/tips/how-to-use-vlookup-for-approximate-matches

 

[tommyloan]

Ok... but how do I name the range? I've never done that before. Thank you.

 

[Joe4]

Very easy.
See: http://www.contextures.com/xlNames01.html#videoname
You do not need to name the range, but I think it makes it easier to work with (you do not need to worry about locking cell references).

BTW, Google is your friend with these type of things. Just Google "Excel name range", and you will gets lots of different links to tutorials and videos!

 

[tommyloan]

Got it, it worked, thank you my friend

 

[Joe4]

You are welcome!