+ Reply to Thread
Results 1 to 4 of 4

Finding Minimum Value of Calculation and Cell Reference

  1. #1
    Registered User
    Join Date
    07-05-2005
    Posts
    11

    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. #2
    Registered User
    Join Date
    07-08-2011
    Location
    Norfolk, VA
    MS-Off Ver
    Excel 2010
    Posts
    41

    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.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-05-2005
    Posts
    11

    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. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,678

    Re: Finding Minimum Value of Calculation and Cell Reference

    Quote Originally Posted by centerNegative View Post
    ...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.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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