Calculating based on two cell values, one of which will fall into a range of numbers

[jesstaylor]

Okay, so I have a payout to figure out based on two things in my sheet. One is a solid number (number of products) the other is an average balance that will fall into one of 5 ranges of values. # of products will be 1-8, anything over 8 should calculate at 8, but needs to still be able to show as more on the sheet (confusing I'm aware). Each value of products and corresponding range has a different payout value. I'm thinking this should be some type of VLOOKUP but I'm not sure.

So an example would look like:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]# of products[/TD]
[TD]Average Balance[/TD]
[TD]Payout[/TD]
[TD](helper column for avg bal range matching if necessary)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1952.25[/TD]
[TD]*formula*[/TD]
[TD]<payout here should yield $15 based on below table[/TD]
[/TR]
</tbody>[/TABLE]


If an error occurs I would like it to show a value of $0 in the Payout.
The actual payout table in the sheet looks ridiculous because of each product value/balance combo having a different value, it looks like this though: If I need to have # of products as 8 then >8 I can, the values for payout will be the same though. Any help with this would be greatly appreciated
[TABLE="class: grid, width: 95"]
<tbody>[TR]
[TD][TABLE="width: 265"]
<tbody>[TR="class: grid"]
[TD] PAYOUT [/TD]
[TD]# of products[/TD]
[TD]Average Balance[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]1[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]1[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]1[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]1[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]1[/TD]
[TD]>2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]2[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]2[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]2[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]2[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]2[/TD]
[TD]>2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]3[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]3[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 10.00 [/TD]
[TD="align: right"]3[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 15.00 [/TD]
[TD="align: right"]3[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 20.00 [/TD]
[TD="align: right"]3[/TD]
[TD]>2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]4[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]4[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 15.00 [/TD]
[TD="align: right"]4[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 20.00 [/TD]
[TD="align: right"]4[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 25.00 [/TD]
[TD="align: right"]4[/TD]
[TD]>2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]5[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 5.00 [/TD]
[TD="align: right"]5[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 20.00 [/TD]
[TD="align: right"]5[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 25.00 [/TD]
[TD="align: right"]5[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 30.00 [/TD]
[TD="align: right"]5[/TD]
[TD]>2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]6[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 10.00 [/TD]
[TD="align: right"]6[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 25.00 [/TD]
[TD="align: right"]6[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 30.00 [/TD]
[TD="align: right"]6[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 35.00 [/TD]
[TD="align: right"]6[/TD]
[TD]>2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD="align: right"]7[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 10.00 [/TD]
[TD="align: right"]7[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 30.00 [/TD]
[TD="align: right"]7[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 35.00 [/TD]
[TD="align: right"]7[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 45.00 [/TD]
[TD="align: right"]7[/TD]
[TD]>2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ - [/TD]
[TD]>=8[/TD]
[TD]0-99[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 10.00 [/TD]
[TD]>=8[/TD]
[TD]100-250[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 35.00 [/TD]
[TD]>=8[/TD]
[TD]251-1000[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 40.00 [/TD]
[TD]>=8[/TD]
[TD]1001-2500[/TD]
[/TR]
[TR="class: grid"]
[TD] $ 50.00 [/TD]
[TD]>=8[/TD]
[TD]>2500[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[JonHaywood]

Hi,

There is a relatively straightforward way to do what you want.

You could create a new worksheet (call it "Payout Table") and copy and paste the cells below into the top left (i.e. cell A1) of this new sheet...

Payout Table

[TABLE="class: grid, width: 809"]
<tbody>[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 10.00
[/TD]
[TD] $ 10.00
[/TD]
[TD] $ 10.00
[/TD]
[/TR]
[TR]
[TD]251
[/TD]
[TD] $ -
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 10.00
[/TD]
[TD] $ 15.00
[/TD]
[TD] $ 20.00
[/TD]
[TD] $ 25.00
[/TD]
[TD] $ 30.00
[/TD]
[TD] $ 35.00
[/TD]
[/TR]
[TR]
[TD]1001
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 15.00
[/TD]
[TD] $ 20.00
[/TD]
[TD] $ 25.00
[/TD]
[TD] $ 30.00
[/TD]
[TD] $ 35.00
[/TD]
[TD] $ 40.00
[/TD]
[/TR]
[TR]
[TD]2500
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 5.00
[/TD]
[TD] $ 20.00
[/TD]
[TD] $ 25.00
[/TD]
[TD] $ 30.00
[/TD]
[TD] $ 35.00
[/TD]
[TD] $ 45.00
[/TD]
[TD] $ 50.00
[/TD]
[/TR]
</tbody>[/TABLE]


Then, paste this formula into your main calculation sheet - in the example you gave, this would be pasted into cell C2...

=VLOOKUP(B2,'Payout Table'!$A$2:$I$6,1+(MATCH(A2,'Payout Table'!$B$1:$I$1)))

It seems to give correct payout results. It shouldn't show any errors so long as there is a number in the # Products and Avg Balance cells.

Hope it helps

Jon