Multiply Value if falls between 2 values

[foustus82]

I'm looking to see what formula I could create that would multiple two cell's values together if the value in cell 1 falls between a set range of numbers.

So,

(Assume the formula would reside in cell C1)

If Cell A1 = 0 to 20,000, then multiply A1 by value in B1
If Cell A1 = 20,001 to 25,000, then multiply A1 by value in B2
If Cell A1 = 25,001 to 35,750, then multiply A1 by value in B3

And so on.

Any thoughts?

 

[Aladin Akyurek]

I'm looking to see what formula I could create that would multiple two cell's values together if the value in cell 1 falls between a set range of numbers.

So,

(Assume the formula would reside in cell C1)

If Cell A1 = 0 to 20,000, then multiply A1 by value in B1
If Cell A1 = 20,001 to 25,000, then multiply A1 by value in B2
If Cell A1 = 25,001 to 35,750, then multiply A1 by value in B3

And so on.

Any thoughts?

Try...

=LOOKUP(A1,{0,20001.25001},B1:B3)*A1

 

[foustus82]

Is there a way to nest lookups? I need the value in Cell A1 to be matched to the appropriate number range (I have 6 number ranges in all)


Here are the ranges:

$0.00 $20,000.00
$20,001.00 $30,000.00
$30,001.00 $37,500.00
$37,501.00 $45,000.00
$45,001.00 $50,000.00
$50,001.00 $100,000.00

Each range has a corresponding percentage that is paid. So, for example, if a month's sales fell between the first range ($0 to $20k), then one would earn x% in commission. So, the monthly sales total is what is in Cell A1 (for our example). That total is obviously dynamic based on what is sold. So, the formula would need to take the month-end total, then look up that value in the 6 ranges I have and see which range it falls in and thereby, multiply it by the commission rate for that particular range.

So, if the month-end sales total was 47,800.00, then the formula would run 47,800.00 against the ranges and see that it falls in the range that is 45,001.00 to 50,000.00 and then would multiply it by the corresponding commission rate for that range. Nothing mathematically needs to be done with the ranges, they are just my "look up boundaries."

Sorry of I'm being confusing.

 

[thisoldman]

This is the table and VLOOKUP formula that I would use.

*	A	B	C	D	E
*	*	*
*
*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*

<colgroup><col style="width:30px; "><col style="width:78px;"><col style="width:83px;"><col style="width:20px;"><col style="width:106px;"><col style="width:78px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ccccff"]Total Sales[/TD]
[TD="bgcolor: #ccccff"]Commission[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]$0.01[/TD]
[TD="align: right"]1.0%[/TD]

[TD="bgcolor: #ccccff"]My Sales[/TD]
[TD="align: right"]$30,000.00[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]$20,000.01[/TD]
[TD="align: right"]2.5%[/TD]

[TD="bgcolor: #ccccff"]My Commission[/TD]
[TD="align: right"]2.5%[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]$30,000.01[/TD]
[TD="align: right"]5.0%[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]$37,500.01[/TD]
[TD="align: right"]7.5%[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]$45,000.01[/TD]
[TD="align: right"]10.0%[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]$50,000.01[/TD]
[TD="align: right"]12.5%[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]$100,000.01[/TD]
[TD="align: right"]15.0%[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E3 =VLOOKUP(E2, $A$2:$B$8, 2) <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

This is the table and VLOOKUP formula that I would use.

*	A	B	C	D	E
*	*	*
*
*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*

<TBODY>
[TD="bgcolor: #cacaca, align: center"]1
[/TD]
[TD="bgcolor: #ccccff"]Total Sales
[/TD]
[TD="bgcolor: #ccccff"]Commission
[/TD]

[TD="bgcolor: #cacaca, align: center"]2
[/TD]
[TD="align: right"]$0.01
[/TD]
[TD="align: right"]1.0%
[/TD]

[TD="bgcolor: #ccccff"]My Sales
[/TD]
[TD="align: right"]$30,000.00
[/TD]

[TD="bgcolor: #cacaca, align: center"]3
[/TD]
[TD="align: right"]$20,000.01
[/TD]
[TD="align: right"]2.5%
[/TD]

[TD="bgcolor: #ccccff"]My Commission
[/TD]
[TD="align: right"]2.5%
[/TD]

[TD="bgcolor: #cacaca, align: center"]4
[/TD]
[TD="align: right"]$30,000.01
[/TD]
[TD="align: right"]5.0%
[/TD]

[TD="bgcolor: #cacaca, align: center"]5
[/TD]
[TD="align: right"]$37,500.01
[/TD]
[TD="align: right"]7.5%
[/TD]

[TD="bgcolor: #cacaca, align: center"]6
[/TD]
[TD="align: right"]$45,000.01
[/TD]
[TD="align: right"]10.0%
[/TD]

[TD="bgcolor: #cacaca, align: center"]7
[/TD]
[TD="align: right"]$50,000.01
[/TD]
[TD="align: right"]12.5%
[/TD]

[TD="bgcolor: #cacaca, align: center"]8
[/TD]
[TD="align: right"]$100,000.01
[/TD]
[TD="align: right"]15.0%
[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula E3 =VLOOKUP(E2, $A$2:$B$8, 2) <TBODY> </TBODY>

<TBODY>
</TBODY>

Change A2 to $0.00 for convenience.

In E3 enter, whichever you prefer...

=VLOOKUP(E2,$A$2:$B$8,2,1)*E2

=LOOKUP(E2,$A$2:$A$8,$B$2:$B$8)*E2

=INDEX($B$2:$B$8,MATCH(E2,$A$2:$A$8,1))

 

[foustus82]

Thanks for the help!! This works great

 

[Aladin Akyurek]

Thanks for the help!! This works great

You are welcome. Thanks for providing feedback.