# Finding Minimum Value of Calculation and Cell Reference

1. ## Finding Minimum Value of Calculation and Cell Reference

I have the following dilemma: we recieve multiple vendor quotes on parts lists with each vendor listing their own price and freight terms. Freight is almost always a simple percentage of the total order. The spread can basicaly end up that the vendor with the lowest price may also have the highest freight terms and therefore, when added together, ultimately have the highest price overall. I have this sort of worked out with a simple array formula but since accounting enters material costs and freight costs separately, I need a way for formula to sort of reverse engineer itself and tell me where it got it's results from (with cell references).

Sorry for the overly complex explanation, here's an example:
``Please Login or Register  to view this content.``
The results of this are that Vendor 2 has the best pricing at \$962.00 overall even though their base material cost is the second highest and so are their freight terms. I've got this worked out with {=MIN(Vendor_Pricing*(1+Freight_Terms))}.

So what'd I'd like to know is a method for breaking this down into the Vendor Pricing and Freight Terms cell references (or which Vendor reference was the cheapest overall) as these costs have to be accounted for separately and taxed differently. Thanks very much for reading!

2. ## Re: Finding Minimum Value of Calculation and Cell Reference

Something like this?

Had to create a 'Total' and 'Cell reference' row (currently hidden), but notice the value that is yielded under "Lowest Vendor" title in cell B8.

The formulas only go to column X, so you would need to adjust them if your excel sheet expanded beyond that.

3. ## Re: Finding Minimum Value of Calculation and Cell Reference

I very much appreciate the response and the example! At the risk of sounding ungrateful or insane, I do have a few conditions I'd like to meet to pull this off. Mainly, I would prefer an 'in-place' solution that didn't require re-structuring the worksheet with the bloat and inconvenience of either visible or hidden additional rows/columns to accomodate these extra steps. However, I'm also limited by not being able to use macros.

Below is an expanded view of what I am dealing with:
``Please Login or Register  to view this content.``

I'd like to get my calculation in the Lowest Bid column to list the lowest price considering freight charges, and then the Lowest Bidder column already contains a simple INDEX+MATCH function set to pull the vendor's name based on that price. The restrictions for not altering the structure of the worksheet/workbook are not mine; it's a proprietary solution I'm trying to make more efficient, but every change I'd like to make basically has one cell in which to accomplish it.

4. ## Re: Finding Minimum Value of Calculation and Cell Reference

Originally Posted by centerNegative
...I would prefer an 'in-place' solution that didn't require re-structuring the worksheet with the bloat and inconvenience of either visible or hidden additional rows/columns to accomodate these extra steps. However, I'm also limited by not being able to use macros.
You could use Rahbee's solution but put it in a new workbook that references the existing one. That would allow you to use macros or whatever restructuring is necessary, but with external links to the original data, which remains untouched.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1