Sorry if it's a bit heavy for a nebie's first post but I have an order form that calculates the cost based on a price grid. Let's just say it's bulk t-shirts that can be purchased based on how many colors. The first column is the quantity with the other column headers being the number of colors. It's easy, for me, to calculate the price if I order exact quantities but what if I ordered 225, 3 color shirts. Is there a way to lookup the corresponding value when it's not exact?
On the order form the customer would but in his order quantity and then select the # of colors. I need the price to be based on the 200 row, 3 color column. I can do it if it's an even 200 because it becomes a nice simple query. I need close if it can be done. If I'm not making sense then shout it out and I'll try to be more specific.
225 shirts (3 color) @ $18.00.
Thanks for looking.
Qty 1 2 3 4 100 $15.00 $17.00 $19.00 $21.00 200 $14.00 $16.00 $18.00 $20.00 300 $13.00 $15.00 $17.00 $19.00 400 $12.00 $14.00 $16.00 $18.00 500 $11.00 $13.00 $15.00 $17.00 600 $10.00 $12.00 $14.00 $16.00 700 $9.00 $11.00 $13.00 $15.00 800 $8.00 $10.00 $12.00 $14.00 900 $7.00 $9.00 $11.00 $13.00 1000 $6.00 $8.00 $10.00 $12.00