+ Reply to Thread
Results 1 to 5 of 5

Please help me... can formula be fixed or does this need to be a lookup function

  1. #1
    Registered User
    Join Date
    09-11-2014
    Location
    Baton Rouge
    MS-Off Ver
    Excel
    Posts
    3

    Please help me... can formula be fixed or does this need to be a lookup function

    I have the attached spreadsheet with multiple columns of data. I'm wanting the column AK to be calculated based upon multiple criteria. Partially determined by the inputs in columns N and Y but also use the information in the two charts at the bottom of the worksheet.

    My current formula gives me #value errors as the min function is based upon a cells value as well as two cells being multiplied. However, when multiplying the value, or in this case the %, may not always be applicable, and the input will be "n/a". When n/a is input, I still want the calculation to return the min of the other values that could be calculated.

    My formula in AK is very long and it seems there should be a better way.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Please help me... can formula be fixed or does this need to be a lookup function

    wow that is one heck of a formula

    firstly....i dont see any scenario for NO in column
    so i assume if not eligble basically it does nothing?

    if that is the case your formula can be cut back significantly by just doing YES check as separate exercise

    secondly
    Y column...
    1,2,3,4 are the options? (*is 0 an option here?)

    then
    3rd criteria would be
    Lot, Model ,Sold ,Spec
    so 4 options again?

    so total combinations is 4x4 = 16 + 1 for NO
    17 combinations is that correct?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Please help me... can formula be fixed or does this need to be a lookup function

    ok broke down your formula some more

    seems you have a side combination for 3rd criteria if it is SOLD
    looks at AG72 to see if it is contract or n/a

  4. #4
    Registered User
    Join Date
    09-11-2014
    Location
    Baton Rouge
    MS-Off Ver
    Excel
    Posts
    3

    Re: Please help me... can formula be fixed or does this need to be a lookup function

    Yes, within the formula, the first portion of each AND scenario includes if column Y = 0.

    So 1st criteria is column F (yes/no), then 2nd is column N (lot/model/spec/sold), then 3rd is column Y (y/n) but then the sold option in column N contains a sub option to also capture if the heading in row 80 is n/a or contract. After all those options are captured, I then need to MIN calculation to ignore if n/a is where a % value would be in the charts at the bottom.

    If column Y = 0, it needs to pull from the chart in rows 73 through 76 / columns AB though AE.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Please help me... can formula be fixed or does this need to be a lookup function

    ok i started UDF for you
    i looked at the code you have already and it seems you should be able to finish it yourself....i hope so..its very time consuming to map

    i should get the general idea though

    i attached the mapping text

    i have only did Lot and SPEC so far
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup function two fixed and one conditional matches
    By Winship in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-04-2013, 10:43 AM
  2. Replies: 5
    Last Post: 02-24-2013, 09:36 AM
  3. [SOLVED] Is there any formula to calculate a fixed increase with a fixed price?
    By ec4excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-09-2012, 12:15 PM
  4. Replies: 2
    Last Post: 11-22-2011, 12:33 PM
  5. Replies: 4
    Last Post: 10-31-2010, 09:37 PM

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