Pricing Calculations

[LaPointer]

I'm working on a purchase order form and I'm having some difficulties formulating the price column. Basically, the price changes based on the quantity of garments and the amount of stitches per garment. There is a price chart within the excel workbook with the x-axis reflecting the number of garments for the order, and the y-axis reflecting the number of stitches per garment.
This chart is in the range D4:K16.

All I've come up with so far is "IF (garment qty) is [value-value], AND (stitches per garment) is [value-value], THEN (garment qty * price from chart range)" although, I have not been able to create a functioning formula.

To give a bit more detail, the price chart has a range for garments and stitches (column D is for between 1-5 garments, column E is for between 6-23 garments, etc... and row 4 is for between 1-3999 stitches, row 5 is for between 4000-4999 stitches, etc...)

Any help would be greatly appreciated!

 

[cerfani]

try taking a screenshot of the data tables

also a chart is one thing and a table is something else, you seem to describe them interchangeably. You don't look up anything from a chart, rather you the lookup the price from a table, charts read tables and display them graphically. It is kinda hard to go off your description alone because when you say chart I dont think you actually mean a chart.

here is a quick example of doing a 2 dimensional lookup... ... garment qty in A1, stitches in B1

=INDEX($D$4:$E$5,IF(B1<4000,1,2),IF(A1<6,1,2))

so that will see if the sticth qty is <4000 and if it is it will reference row 4 or else row 5 and also if garment qty < 6 it will reference column D or else E.

 

[jtm2000]

Sounds like you need to INDEX MATCH the table to find the related value. But a screenshot would be hugely valuable.

 

[cerfani]

i just edited my post with a formula example

 

[LaPointer]

i just edited my post with a formula example

Thank you for your help. I'm not sure how to post the screenshot, so I'm hoping this works.

Additionally, I did in fact mean table. There are no charts being used

 

[jtm2000]

Thanks!

Does the pricing input page have standardized amounts or is it an exact number for both stitches and quantity?

I'm assuming there's a product table that refers to the number of stitches and then the quantity is set by a field?

 

[cerfani]

just keep adding to my formula... more IFS and make the table being indexed larger

A1 = Garment Qty
B1 = Stitch Qty

=INDEX($D$4:$K$16,IF(B1<=3000,1,IF(B1<=4000,2,IF(...))),IF(A1<6,1,IF(A1<24,2,IF(...))))

your formula will be longer because you need an if function for each column/row

the last column and row can be the last false result

hope that makes sense

 

[LaPointer]

just keep adding to my formula... more IFS and make the table being indexed larger

A1 = Garment Qty
B1 = Stitch Qty

=INDEX($D$4:$K$16,IF(B1<=3000,1,IF(B1<=4000,2,IF(...))),IF(A1<6,1,IF(A1<24,2,IF(...))))

your formula will be longer because you need an if function for each column/row

the last column and row can be the last false result

hope that makes sense

That did the trick -- thanks for your help!