Results 1 to 8 of 8

Thread: Formula Required

  1. #1

    Formula Required



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

    In cell G32, this cell returns a number from 0 through to 59.
    In cell G33, I would like cell G33 to return a result based on the following....
    If G32 is either 0,1,2,3, then cell G33 is to return 0.
    If G32 is either 4,5,6,7,8, then cell G33 is to return 0.1.
    If G32 is either 9,10,11,12,13,14, then cell G33 is to return 0.2.
    Look forward to getting a formula. Thank you..

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    try in G33:
    =VLOOKUP(G32,{0,0;4,0.1;9,0.2;15,999},2)
    or:
    =INDEX({0;0.1;0.2;999},MATCH(G32,{0;4;9;15}))

    You don't say what you want returned for values above 14, so I threw in 999.

  3. #3
    p45cal....many thanks for your time in creating the formula for me..it works great....yes that's right, I didn't say what values I wanted returned above 14 as any number above that I have is dealt with in another cell....I acknowledge you going beyond what I asked....thank you very much..
    So, since I am not concerned with numbers above 14, what will be the shortened formula then?
    Last edited by Secant; 2014-05-21 at 10:52 PM.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    in either formula, replace the 999 with what you want to see in the cell, eg. for nothing:
    =VLOOKUP(G32,{0,0;4,0.1;9,0.2;15,""},2)
    for some text:
    =VLOOKUP(G32,{0,0;4,0.1;9,0.2;15,"this is above 14"},2)

  5. #5
    Ok, thanks for your help.

  6. #6
    What is the number 2 at the end of the formula for?

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Secant View Post
    What is the number 2 at the end of the formula for?

    VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    from Help:

    col_index_num Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

    {0,0;4,0.1;9,0.2;15,999}
    is really a table:
    0 0
    4 0.1
    9 0.2
    15 999
    Last edited by p45cal; 2014-05-21 at 11:45 PM.

  8. #8
    Thanks for the response....it all makes sense to me.

Posting Permissions

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