Calculating a total controlled by multiple variables?

[nokia4436]

I want to calculate the total for something, but that total is
controlled by multiple variables.

In my setup (I'm trying to make a calculator for the cost of tshirt
printing) I have:

1. Categories for number of tshirts (Four values: 1 to 9, 10 to 19, 20
to 39, 40+) Each category has its own price, this price however is
based on the following:

2. Categories for number of colours of prints: (Four values: 1 Colour,
2 Colour, 3 Colour & 4 Colour).

3. Number of tshirt (Can vary, user supplies value)

These three variables together control the total price for the
tshirts.

For example, say a user wanted 14, 3 colour tshirts. On my two way
table (with category for tshirts at top, and category for colours at
side) this would give me the price of £12.20 PER tshirt (because it is
within the 10-19 category, and is 3 colours). This would then be
multiplied by 14 (the number of tshirts the user has specified they
want a quote for) giving me the total im after (£170.80).

How can I get excel to do this with the minimum fuss? I tried it with
IF statements, but that way I would have to do one for each individual
price, which is not a very efficient method. Is there a much quicker
way to do it? I hope I explained it properly!

Regards

 

[Christopher King]

Maybe you need to look at the problem differently: think of the discount as
a percent reduction from a base price. Then, you could look up the percent
reduction in one table (sort of a one-dimensional table), and look up the
base price in the table you already have. Multiply them together, and you've
got the price.
Does that help?
Chris

 

[Jay]

Hi nokia -

Here's one way:

1. Build a 4x4 table of unit costs on a worksheet. For example:
Colors
Quantity 1 2 3 4
1 to 9 £12.20 £12.70 £13.20 £13.95
10 to 19 £11.20 £11.70 £12.20 £12.95
20 to 39 £10.30 £10.80 £11.30 £12.05
40+ £9.20 £9.70 £10.20 £10.95

2. Name the 16-cell range that contains dollar values "UnitCost" (Insert,
Name, Define, highlight the dollar values, and provide the name "UnitCost").

3. Paste the following procedure into the worksheet's module. Clicking on
any dollar value will fire the procedure and produce the total cost:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set isect = Intersect(Target, Range("UnitCost"))
If Not isect Is Nothing Then
qty = InputBox("How Many?")
If qty = "" Then Exit Sub
MsgBox "Total Cost = " & Format(ActiveCell.Value * qty, "£#,###.00")
End If
End Sub

 

[Jim Cone]

I prepared a table on a worksheet that uses only one formula
to determine the total cost of an order.
Email me providing your real name and location and I will forward
the workbook to you at no charge.
Remove XXX from my email address: (e-mail address removed)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message I want to calculate the total for something, but that total is
controlled by multiple variables.
In my setup (I'm trying to make a calculator for the cost of tshirt
printing) I have:

1. Categories for number of tshirts (Four values: 1 to 9, 10 to 19, 20
to 39, 40+) Each category has its own price, this price however is
based on the following:

2. Categories for number of colours of prints: (Four values: 1 Colour,
2 Colour, 3 Colour & 4 Colour).

3. Number of tshirt (Can vary, user supplies value)

These three variables together control the total price for the
tshirts.
For example, say a user wanted 14, 3 colour tshirts. On my two way
table (with category for tshirts at top, and category for colours at
side) this would give me the price of £12.20 PER tshirt (because it is
within the 10-19 category, and is 3 colours). This would then be
multiplied by 14 (the number of tshirts the user has specified they
want a quote for) giving me the total im after (£170.80).

How can I get excel to do this with the minimum fuss? I tried it with
IF statements, but that way I would have to do one for each individual
price, which is not a very efficient method. Is there a much quicker
way to do it? I hope I explained it properly!
Regards