If value falls between two prices in a range, return calculation

[B5rocksass]

I am working on a template that will autocalculate the price when a specific quantity is entered (after rounding the quantity to the nearest skid size first). Ideally, I'd like to move the prices to a different tab or hide them, so the template only shows only the retrieved price. Please assist.

Ox Paper BD 20210112.xlsx
	A	B	C	D	E	F	G	H	I	J
1	HOW IT IS NOW					Unit Price by Qty Tier
2	Qty	UOM	Item No	Qty Per Skid	Final Qty	500	750	1000	1500	SubTotal
3	500	EA	ITEM 1	200	600	$ 40.50	$ 33.83	$ 30.50	$ 27.17	$ 24,300.00
4	1,300	EA	ITEM 2	156	1404	$ 1.29	$ 1.22	$ 1.19	$ 1.15	$ 1,670.76
5
6	IDEAL
7	Qty	UOM	Item No	Qty Per Skid	Final Qty	Unit Price	SubTotal
8	500	EA	ITEM 1	200	600	$ 40.50	$ 24,300.00
9	1,300	EA	ITEM 2	156	1404	$ 1.19	$ 1,670.76
Sheet3

Cell Formulas
Range	Formula
E8:E9,E3:E4	E3	=D3*(ROUNDUP(A3/D3,0))
J3	J3	=E3*F3
J4	J4	=E4*H4
G8:G9	G8	=E8*F8

 

[Fluff]

What determines which row of prices should be looked at?

 

[B5rocksass]

What determines which row of prices should be looked at?

The final quantity. if its higher than the tier but lower than another tier, return that tier pricing, but needs to have a nested IF with the possibility of all tiers in use.

 

[Fluff]

Yes, but rows 3 & 4 have different prices, what determines which row should be used?

 

[B5rocksass]

Yes, but rows 3 & 4 have different prices, what determines which row should be used?

I guess the values in the header need to be specifically written into the formula? If the header 500/750/1000/1500 needs to be removed, i guess the unit price formula should state those values as text, right?

 

[Fluff]

It has nothing to do with the headers, that simply shows which column to get the price from.
What I am asking is which ROW so if something is in the 750 tier what decides if the price is 33.83 or 1.22?

 

[B5rocksass]

I guess there would need to be an Index/Match per the Item Number, with a link to a table that breaks it down on a subsequent page? I'm sorry but I'm not sure what you are asking

 

[Fluff]

With this

+Fluff v2.xlsm
	F	G	H	I
2	500	750	1000	1500
3	40.5	33.83	30.5	27.17
4	1.29	1.22	1.19	1.15
Master

+Fluff v2.xlsm
	A	B	C	D	E	F	G
6	IDEAL
7	Qty	UOM	Item No	Qty Per Skid	Final Qty	Unit Price	SubTotal
8	500	EA	ITEM 1	200	600	40.5	24300
9	1300	EA	ITEM 2	156	1404	1.19	1670.76
Master

Cell Formulas
Range	Formula
E8:E9	E8	=D8*(ROUNDUP(A8/D8,0))
F8:F9	F8	=INDEX(F3:I3,MATCH(E8,$F$2:$I$2,1))
G8:G9	G8	=E8*F8

How do you know that F8 is 40.5 & not 1.29, likewise why is F9 1.19 & not 30.5?

 

[B5rocksass]

I guess with something like this on a subsequent (hidden) tab?

Ox Paper BD 20210112.xlsx
	A	B	C
1	PART NO	Qty Price Breaks	Unit Price
2	ITEM 1	500	$ 40.50
3	ITEM 1	750	$ 33.83
4	ITEM 1	1,000	$ 30.50
5	ITEM 1	1,500	$ 27.17
6	ITEM 2	500	$ 1.29
7	ITEM 2	750	$ 1.22
8	ITEM 2	1,000	$ 1.19
9	ITEM 2	1,500	$ 1.15
Sheet4

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1						Unit Price by Qty Tier
2	Qty	UOM	Item No	Qty Per Skid	Final Qty	Unit Price	SubTotal
3	500	EA	ITEM 1	200	600	40.5	24300
4	1300	EA	ITEM 2	156	1404	1.19	1670.76
Master

Cell Formulas
Range	Formula
E3:E4	E3	=D3*(ROUNDUP(A3/D3,0))
F3:F4	F3	=INDEX($P$3:$S$4,MATCH(C3,$O$3:$O$4,0),MATCH(E3,$P$2:$S$2,1))
G3:G4	G3	=E3*F3

+Fluff v2.xlsm
	O	P	Q	R	S
1
2		500	750	1000	1500
3	ITEM 1	40.5	33.83	30.5	27.17
4	ITEM 2	1.29	1.22	1.19	1.15
Master