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:

# of products	Average Balance	Payout	(helper column for avg bal range matching if necessary)
3	1952.25	*formula*	<payout here should yield $15 based on below table

<tbody>
</tbody>

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

PAYOUT # of products Average Balance $ - 1 0-99 $ - 1 100-250 $ - 1 251-1000 $ 5.00 1 1001-2500 $ 5.00 1 >2500 $ - 2 0-99 $ - 2 100-250 $ 5.00 2 251-1000 $ 5.00 2 1001-2500 $ 5.00 2 >2500 $ - 3 0-99 $ 5.00 3 100-250 $ 10.00 3 251-1000 $ 15.00 3 1001-2500 $ 20.00 3 >2500 $ - 4 0-99 $ 5.00 4 100-250 $ 15.00 4 251-1000 $ 20.00 4 1001-2500 $ 25.00 4 >2500 $ - 5 0-99 $ 5.00 5 100-250 $ 20.00 5 251-1000 $ 25.00 5 1001-2500 $ 30.00 5 >2500 $ - 6 0-99 $ 10.00 6 100-250 $ 25.00 6 251-1000 $ 30.00 6 1001-2500 $ 35.00 6 >2500 $ - 7 0-99 $ 10.00 7 100-250 $ 30.00 7 251-1000 $ 35.00 7 1001-2500 $ 45.00 7 >2500 $ - >=8 0-99 $ 10.00 >=8 100-250 $ 35.00 >=8 251-1000 $ 40.00 >=8 1001-2500 $ 50.00 >=8 >2500 <tbody> </tbody><colgroup><col><col><col></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[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

	1	2	3	4	5	6	7	8
0	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
100	$ -	$ -	$ 5.00	$ 5.00	$ 5.00	$ 10.00	$ 10.00	$ 10.00
251	$ -	$ 5.00	$ 10.00	$ 15.00	$ 20.00	$ 25.00	$ 30.00	$ 35.00
1001	$ 5.00	$ 5.00	$ 15.00	$ 20.00	$ 25.00	$ 30.00	$ 35.00	$ 40.00
2500	$ 5.00	$ 5.00	$ 20.00	$ 25.00	$ 30.00	$ 35.00	$ 45.00	$ 50.00

<tbody>
</tbody>

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