Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find min sum from multiple categories and functions
Here is my objective. This is a practice table so I know how to set up my
really big one....... Headings: ( Bid Package ) ( Bidder ) ( $ ) (% bidded) Concrete A 100 26 Concrete B 120 25 Concrete C 85 24 Masonry D 200 37 Masonry E 210 39 Masonry F 240 40 Site G 270 34 Site H 280 36 Site I 250 34 So I have 3 different types of Bid Packages (over 20 in real life) and 3 bidders in each package. I want to be able calculate each possible scenario (match each package to each bidder in package) and find the sum through example below. I think there are 27 total possible scenarios (will be in the 1000s for my real one). If possible, I would like it to automatically find the minimum sum. Example of one sceranio: Concrete - A - 100 - 26 Masonry - D - 200 - 37 Site - G - 270 - 34 Totals: $570 - 97% 1. then take 100%-97%=3% 2. then multiply 3%x$570= $17.1 + $570 = $587.1 or 1.03% x $570 = $587.1 3. so the total for this scenario is $587.1 What is the best way to do all of this automatically? I was thinking pivot tables or solver but I don't know much about or solver. Be as thorough as you want. -- Calvin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find min sum from multiple categories and functions
Hi Calvin,
Lets Say that you have that information in Column b,c,d and e, row 1 you can enter let say in column H Concrete, Massonery and Site Column I will be to enter your escenarios In column J enter the formula as follow =VLOOKUP($I5,$C:$E,2,FALSE) that will bring the total $ In column K enter =VLOOKUP($I5,$C:$E,3,FALSE) will give you the total % Then you can totalize the totals under this information in row 4 and enter the formula =((100-K4)*J4)+J4 That will give you the results you are looking for "Calvin" wrote: Here is my objective. This is a practice table so I know how to set up my really big one....... Headings: ( Bid Package ) ( Bidder ) ( $ ) (% bidded) Concrete A 100 26 Concrete B 120 25 Concrete C 85 24 Masonry D 200 37 Masonry E 210 39 Masonry F 240 40 Site G 270 34 Site H 280 36 Site I 250 34 So I have 3 different types of Bid Packages (over 20 in real life) and 3 bidders in each package. I want to be able calculate each possible scenario (match each package to each bidder in package) and find the sum through example below. I think there are 27 total possible scenarios (will be in the 1000s for my real one). If possible, I would like it to automatically find the minimum sum. Example of one sceranio: Concrete - A - 100 - 26 Masonry - D - 200 - 37 Site - G - 270 - 34 Totals: $570 - 97% 1. then take 100%-97%=3% 2. then multiply 3%x$570= $17.1 + $570 = $587.1 or 1.03% x $570 = $587.1 3. so the total for this scenario is $587.1 What is the best way to do all of this automatically? I was thinking pivot tables or solver but I don't know much about or solver. Be as thorough as you want. -- Calvin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find min sum from multiple categories and functions
To the last formula sent to you, I make a mistake instead of (100-97) you
have to enter (100-97)/100 "Calvin" wrote: Here is my objective. This is a practice table so I know how to set up my really big one....... Headings: ( Bid Package ) ( Bidder ) ( $ ) (% bidded) Concrete A 100 26 Concrete B 120 25 Concrete C 85 24 Masonry D 200 37 Masonry E 210 39 Masonry F 240 40 Site G 270 34 Site H 280 36 Site I 250 34 So I have 3 different types of Bid Packages (over 20 in real life) and 3 bidders in each package. I want to be able calculate each possible scenario (match each package to each bidder in package) and find the sum through example below. I think there are 27 total possible scenarios (will be in the 1000s for my real one). If possible, I would like it to automatically find the minimum sum. Example of one sceranio: Concrete - A - 100 - 26 Masonry - D - 200 - 37 Site - G - 270 - 34 Totals: $570 - 97% 1. then take 100%-97%=3% 2. then multiply 3%x$570= $17.1 + $570 = $587.1 or 1.03% x $570 = $587.1 3. so the total for this scenario is $587.1 What is the best way to do all of this automatically? I was thinking pivot tables or solver but I don't know much about or solver. Be as thorough as you want. -- Calvin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting Multiple Categories | Excel Discussion (Misc queries) | |||
Assigining User Defined Functions to Categories in Addins | Excel Worksheet Functions | |||
How Do I Combine Rankings for Multiple Categories? | Excel Worksheet Functions | |||
SUMIF With Multiple Categories | Excel Worksheet Functions | |||
Multiple FIND functions in Conditional Formatting | Excel Worksheet Functions |