Results 1 to 8 of 8

Thread: Condiitional Formula Help needed

  1. #1

    Condiitional Formula Help needed



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Please see the attached excel for the problem statement....and sample data

    Thanks for your help and time.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum.

    An approach using VLOOKUP is attached.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Should I be using NESTED IF statement? I need to find the range first and value next.....Can it be done with NEsted IFs?

    THanks for your time.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    I wouldn't. In order to do this with nested IF statements, you're looking at a monster formula that would be a real pain to maintain. With the setup I gave you, it will run more efficiently (probably not a big issue here), but more imporant is that you could easily insert a new percentage in the middle of the table if you needed to. (Just watch carefully as those %'s actually have decimals attached in order to match your >= rules.)

    If you can clarify a bit more as to what you're trying to find, I can help you with it...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Thanks...Is there a limitation on how many VLOOKUPS we can add to this formula?
    For example, if I want to add HIGH % values, will that matter?

  6. #6
    =B4*IF(B2="Low",VLOOKUP(B3,D6:E9,2,TRUE),(IF(B2="Med",VLOOKUP(B3,G6:H9,2,TRUE),VLOOKUP(B3,J6:K9,2,TRUE))))

    Will this be right?

  7. #7
    Can you explain the decimals concept for the >= rules?

    if I have the range is like this...what would be hte decimal values for this?

    >=90% to 100% = 4
    >=80% to <90% = 3
    >=70% to <80% = 2
    <70% = 1

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Sure, so in the set of data you just provided, you actually don't need to add decimals to the values. The difference is that in your earlier example you were asking for numbers >x%. Here you're asking for >=x%.

    So the table we'd build is as follows:
    A B
    1 0% 1
    2 70% 2
    3 80% 3
    4 90% 4
    VLOOKUP works like this: =VLOOKUP(Value to look up, Range to look in, Column to return, Exact or approximate match)
    For example: =VLOOKUP(69.9,A1:B4,2,TRUE)

    When you're using an approximate match in a VLOOKUP statement, the numbers in the first column must be in ascending order. The function then returns the corresponding column for the highest row that does not exceed the number you gave.

    So in this case, if we looked for a value of 69.9, VLOOKUP would look at the first row and perform the test "Is 69.9>=0", which it is. Then it would check "Is 69.9>=70", which is obviously not true. It would therefore return 1 (if we told it to look in the second column)

    If we looked up 70, we'd get "Is 70>=0", which it is, then "Is 70>=70", which it is and "Is 70>=80", which it's not. The last true result is 70, and it would therefore return us the 2 from that row.

    Does that make sense so far?

    So the issue we had in the previous scenario was that you were looking for >2%, where VLOOKUP want >=2%. So what I did to fix that was added an insignificant digit to each number to force it to not be equal when you put in a round percentage.

    As far as having more VLOOKUPS, sure you can do that. Ultimately though it might be better to build one large table and use a single VLOOKUP with a MATCh formula to pull out the column. That may sound a bit complicated, but we can help with it if you'd like to go that route.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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