Formula for Price Comparison

[Bitty]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Company 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Company 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Company 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Company 4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Destination
[/TD]
[TD]Origin
[/TD]
[TD]0 (Min $)
[/TD]
[TD]100 lbs.
[/TD]
[TD]1000 lbs.
[/TD]
[TD]0 (Min $)
[/TD]
[TD]100 lbs.
[/TD]
[TD]500 lbs.
[/TD]
[TD]0 (Min $)
[/TD]
[TD]100 lbs.
[/TD]
[TD]500 lbs.
[/TD]
[TD]0 (Min $)
[/TD]
[TD]100 lbs.
[/TD]
[TD]500 lbs.
[/TD]
[/TR]
[TR]
[TD]DFW
[/TD]
[TD]ATL
[/TD]
[TD]$25.95
[/TD]
[TD]$7.76
[/TD]
[TD]$7.12
[/TD]
[TD]$39.82
[/TD]
[TD]$13.36
[/TD]
[TD]$13.09
[/TD]
[TD]$40.00
[/TD]
[TD]$11.13
[/TD]
[TD]$10.89
[/TD]
[TD]$26.00
[/TD]
[TD]$10.47
[/TD]
[TD]$9.97
[/TD]
[/TR]
[TR]
[TD]CVG
[/TD]
[TD]CLT
[/TD]
[TD]$34.60
[/TD]
[TD]$13.80
[/TD]
[TD]$13.80
[/TD]
[TD]$39.82
[/TD]
[TD]$23.58
[/TD]
[TD]$23.12
[/TD]
[TD]$40.00
[/TD]
[TD]$19.00
[/TD]
[TD]$19.00
[/TD]
[TD]$39.50
[/TD]
[TD]$20.94
[/TD]
[TD]$19.95
[/TD]
[/TR]
[TR]
[TD]GSP
[/TD]
[TD]EWR
[/TD]
[TD]$45.00
[/TD]
[TD]$11.25
[/TD]
[TD]$10.50
[/TD]
[TD]$39.82
[/TD]
[TD]$21.81
[/TD]
[TD]$20.82
[/TD]
[TD]$40.00
[/TD]
[TD]$19.00
[/TD]
[TD]$19.00
[/TD]
[TD]$33.50
[/TD]
[TD]$21.64
[/TD]
[TD]$19.95
[/TD]
[/TR]
</tbody>[/TABLE]

Hello All,

I am attempting to put together a price comparison spreadsheet, but I am having trouble figuring a formula to return the necessary information. There is approximately 9,000 Origin/Destination combinations to be searched in the formula. What I need is:

1.) A formula to search for the origin, destination, and closest weight.
2.) I then need the formula to return the lowest cost (of four competing carrier's).
3.) I will then need a second formula to return the name of the carrier.
4.) If possible I would like to rank all four carrier's in descending order.

Please see example above...


I am unsure of how to post an example of my worksheet. I believe seeing an example would help tremendously. Please reply, if only to help with posting example.

Thank you,

Bitty

 

[Rijnsent]

Hi Bitty,

I assume you have 3 cells with the "input data": Origin, Destination and weight.

Firstly, to simplify the processing I would create an extra column in your data, linking the origin and destination, like "DFW-ATL" (the formula for that is e.g. =B3&"-"&C3, that would be a package from ATL to DFW). If you do that with your input data too, you could use a simple MATCH formula to lookup the row where the right information is (the prices that is).

Secondly, the pricing. I am not really getting what the prices would be, but I guess the easiest would be to calculate the 4 prices (you can get the numbers with the MATCH result and an OFFSET formula) and take the MIN. With another match and offset you could find out which of the 4 companies that would be.

I hope those pointers help you going, if not, don't hesitate to respond. And if you could then give an example of how you would e.g. calculate the price of a 150 lbs or 1230 lbs package, that would be helpfull.

Cheers,

Koen

 

[Bitty]

Thank you Rijnsent. I have found a formula that works for this spreadsheet. I appreciate your help.