Formula for Price Comparison

Bitty

New Member
Joined
Dec 27, 2013
Messages
4
Company 1
Company 2
Company 3
Company 4
Destination
Origin
0 (Min $)
100 lbs.
1000 lbs.
0 (Min $)
100 lbs.
500 lbs.
0 (Min $)
100 lbs.
500 lbs.
0 (Min $)
100 lbs.
500 lbs.
DFW
ATL
$25.95
$7.76
$7.12
$39.82
$13.36
$13.09
$40.00
$11.13
$10.89
$26.00
$10.47
$9.97
CVG
CLT
$34.60
$13.80
$13.80
$39.82
$23.58
$23.12
$40.00
$19.00
$19.00
$39.50
$20.94
$19.95
GSP
EWR
$45.00
$11.25
$10.50
$39.82
$21.81
$20.82
$40.00
$19.00
$19.00
$33.50
$21.64
$19.95

<tbody>
</tbody>

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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,302
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 27, 2013
Messages
4
Thank you Rijnsent. I have found a formula that works for this spreadsheet. I appreciate your help.
 

Forum statistics

Threads
1,141,712
Messages
5,708,007
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top