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

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

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