+ Reply to Thread
Results 1 to 2 of 2

Price Calculation Problems

  1. #1
    Registered User
    Join Date
    05-12-2012
    Location
    honolulu
    MS-Off Ver
    Excel 2008
    Posts
    1

    Price Calculation Problems

    I am still pretty new to excel, but I am trying to make a spreadsheet that will calculate a price based on a number of variables for my job. I made a series of tables with the variable and the price and now I m trying to figure out how to make it return a price based on user input. so far I have been able to make it work using string of IF formulas, but I am running into some problems and so i am trying to find a more efficient method.

    Basically this is how i have it set up.

    I have a simple table where one column is a type of material and the next column is a price based on the material next to it ie:

    wood 4
    plastic 3
    cardboard 2


    I have a drop down menu (A1) linked to the first column. I am trying to create a simple formula where, depending on which material the drop down cell is, it will return the price next to it. So far I can make it work using a formula similar to the following:

    =IF(A1=A2,B2,IF(A1=A3,B3,IF(A1=A4,B4,"")))


    This works, but I have some lists with 20+ values so I get stuck with the nested function limit. I managed to work around it, but its sloppy and I am looking for a more efficient formula. if tried a couple of different array formulas but I haven't been able to make any of them work. Ive googled everything I could think of to no avail. It seems like there is probably something simple I'm overlooking, but I haven't found it yet. Could someone point me in a direction at least?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Price Calculation Problems

    Make a table of material and price and the use VLOOKUP

    For example, =VLOOKUP(A1, $G$1:$H$100,2,FALSE)

    That uses the value in A1 to search a table in columns G and H and returns the value from the second column if a match is found in the first column. The FALSE says it must be an exact match.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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