Not sure which formula to use?

[samilynn]

Hi guys, I have this table on one spreadsheet. On a different spreadsheet, I want to enter just the Item Number and a Qty, and then a vlookup (I think?) that would give me the proper bonus, based on the item and what qty I am selling.

Can you guide me in the right direction with which formula(s) to use?

Thanks,

Samantha

ITEM	QTY	BONUS	QTY	BONUS	QTY	BONUS
Q7588	1	$2.00	10	$25.00	50	$200.00
R4261	2	$10.00	25	$225.00	75	$900.00
B4490	1	$20.00

 

[Anfinsen]

Hey Sami,
Just so I understand, if you sell 1-9 of Item Q7588, you get 2.00
if you sell 10-49, you get 25, and if you sell 50 or more, you get 200?

 

[samilynn]

Hey Sami,
Just so I understand, if you sell 1-9 of Item Q7588, you get 2.00
if you sell 10-49, you get 25, and if you sell 50 or more, you get 200?

Hi Anfinsen...

if I sell 1-9, you get 2.00 a piece (so if I sell 5 you get 10.00)
if I sell 10, then you get 25.00 per every 10 (so if I sell 20, you get 50.00)
etc

Thank you!!

Sammy

 

[Anfinsen]

Sami,
simply from a mathematical perspective, it would be easier to list all you bonus amounts per piece, and not have to add extra math on top. Something like below.

ITEM	QTY	BONUS PER PIECE	QTY	BONUS PER PIECE	QTY	BONUS PER PIECE
Q7588	1​	$2.00​	10​	$2.50​	50​	$4.00​
R4261	2​	$10.00​	25​	$9.00​	75​	$12.00​
B4490	1​	$20.00​

 

[samilynn]

Sami,
simply from a mathematical perspective, it would be easier to list all you bonus amounts per piece, and not have to add extra math on top. Something like below.

ITEM	QTY	BONUS PER PIECE	QTY	BONUS PER PIECE	QTY	BONUS PER PIECE
Q7588	1​	$2.00​	10​	$2.50​	50​	$4.00​
R4261	2​	$10.00​	25​	$9.00​	75​	$12.00​
B4490	1​	$20.00​

Hi Anfinsen, you're right, but unfortunately I don't have that option.

I guess I could replace the simple reference (say E2), with a formula like E2/D2, which will divide the total bonus by the required qty, to give me the bonus amount per piece.

 

[Anfinsen]

Hi Anfinsen, you're right, but unfortunately I don't have that option.

I guess I could replace the simple reference (say E2), with a formula like E2/D2, which will divide the total bonus by the required qty, to give me the bonus amount per piece.

Sami,
There isn't (that I am aware of) a way to do this all in one formula, with the way your data is structured, without the help of some helper columns.

In your new sheet, try using this table, the first two headers, "Enter Item Below" and "Enter QTY Below", are input fields, the rest of the fields would be static formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ITEM	QTY	BONUS PER PIECE	QTY	BONUS PER PIECE	QTY	BONUS PER PIECE		Enter Item Below	Enter Qty Sold Below	Bonus Amount	QTY 1	QTY 2	QTY 3	Bonus 1	Bonus 2	Bonus 3
2	Q7588	1	$2.00	10	$2.50	50	$4.00		Q7588	49	122.5	1	10	50	2	2.5	4
3	R4261	2	$10.00	25	$9.00	75	$12.00
4	B4490	1	$20.00
5
6
7
Sheet1

Cell Formulas
Range	Formula
K2	K2	=IFS(J2<M2,J2*O2,AND(J2>=MIN(M2, N2),J2<=MAX(M2, N2)), J2*P2,J2>N2,J2*Q2)
L2	L2	=VLOOKUP(I2,A:B,2,)
M2	M2	=VLOOKUP(I2,A:D,4,)
N2	N2	=VLOOKUP(I2,A:F,6,)
O2	O2	=VLOOKUP(I2,A:C,3,)
P2	P2	=VLOOKUP(I2,A:E,5,)
Q2	Q2	=VLOOKUP(I2,A:G,7,)