Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a total controlled by multiple variables?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a total controlled by multiple variables?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a total controlled by multiple variables?
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 --- Jay " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a total controlled by multiple variables?
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: XX -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total by 2 variables | Excel Worksheet Functions | |||
Calculating on multiple variables | Excel Discussion (Misc queries) | |||
How do i keep the total of 3 variables constant | Excel Discussion (Misc queries) | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
Calculating Percentages with Variables | Excel Worksheet Functions |