Spreadsheet: 226038d1365216310-problem-solving-items-in-a-range-cost-in-relation-to-a-quantity-silkscreen-pricing-quote
Sheet: Sheet1

Silk Screen Charges
 | 1 | 2 | 3 | 4 | 5 | 6
Qty. | 1 Colour | 2 Colour | 3 Colour | 4 Colour | 5 Colour | 6 Colour |  | Additional Charge Considerations
10 | 6.5 | 7.5 | 8.5 | 9.5 | 10.5 | 11.5 |  | Coloured Shirt - Screen printing starts one column over (Ex. 1 colour would be a 2 colour, 2 colour would be 3, etc.)
25 | 4.5 | 5.25 | 6 | 6.75 | 7.5 | 8.25 |  | [NAME] $5 per colour  **White is considered 2 colours as two layers must be applied.
50 | 3.5 | 4 | 4.5 | 5 | 5.5 | 6 |  | [NAME] - $10 per colour
75 | 2.75 | 3.15 | 3.55 | 3.95 | 4.35 | 4.75 |  | PMS [NAME]30 per colour
100 | 2.25 | 2.6 | 2.95 | 3.3 | 3.65 | 4 |  | [NAME] $40 per hour (1 hr. min.)
150 | 2 | 2.25 | 2.5 | 2.75 | 3 | 3.25 |  | Numbers - $4 per location
250 | 1.8 | 2 | 2.2 | 2.4 | 2.6 | 2.8 |  | Names - $5 per location
99999 | 1.7 | 1.8 | 1.9 | 2 | 2.1 | 2.2 |  | Additional locations are half the price of the first location, based on the highest colour quantity location.
Screen Set up: | 1 Colour | 2 Colour | 3 Colour | 4 Colour | 5 Colour | 6 Colour |  | Screen set up is a one time fee to create the screens for processing.
 | 15 | 30 | 45 | 60 | 75 | 90
Screen Cost Quote:  (All pricing is for silk screening.  Cost of shirts in addition.) |  |  |  |  |  |  |  |  |  | Ok all you people out there smarter than me... and that is a lot of you I am sure
Shirt Quantity: |  |  |  | 20 |  |  |  |  |  | Here is my dilemma. I have a silk screen company and would like to create a quote generating spreadsheet (what I have so far is attached). I offer prices for silk screening based on the quantity of both the number of shirts being screened as well as the number of colours being screened on each shirt in a matrix. The more shirts we are screening the less per shirt the screening cost is, but, the more colours the more per shirt the screening is as well.
[NAME] for 1st location: |  |  |  | 1
Additional Locations: |  |  |  | 1 |  |  |  |  |  | What I am looking for is to be able to enter all the blue fields and then be able to have all the other fields be calculated out. I have the formulas in that I know how to do, but I need help calculating the "Silk Screen Charge for 1st Location" as this is what everything else is based on. The pricing matrix is above and is needed to calculate this field. I need the calculation to take the quantity of shirts entered (E18) then compare that to the shirt quantity ranges (A3 - A11), then look at the quantity of colours to be screened (E19) and then multiply the quantity E18 by the price for the range associated with the colours from E19. I hope that makes sense.
Number Custom Colours: |  |  |  | 1
Number PMS Colours: |  |  |  | 1 |  |  |  |  |  | Please help if you can.
Location Qty for Numbers per shirt: |  |  |  | 1
Location Qty for Names per shirt: |  |  |  | 1
Number - Screen Set Up: |  |  |  | 1
Silk Screen Charge for 1st Location:
Silk Screen Charge for 2nd Location:
Custom Colour Charge:
PMS Colour Charge:
Numbers Charge:
Names Charge:
[NAME]:
Silk Screen Subtotal:
Per Shirt Cost:
Shirt Cost Subtotal:
[NAME]:
Tax:
Order Total: