Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help of Formula Needed (Excel-2003)

    Hi Excel Experts,

    I have been working on an excel file that i received for the purpose of arriving at the %increase. This was developed by someone else. I need to understand a formula. So posting this query. I am looking at a better or a simpler way of doing the same it there is one.

    Can someone help me understand the formula in C2 in the attached file and also if could suggest a better or simpler formula, would be great.

    Regards
    Baiju

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help of Formula Needed (Excel-2003)

    This is a little shorter:
    =IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(D2-0.00000000001,$G$2:$G$11,1))+1,MATCH(IF(A2=B2,E2,3 ),$H$1:$L$1,0)))

    or even something like:
    =IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(ROUND( D2,3),$G$2:$G$11,1))+1,MATCH(IF(A2=B2,E2,3),$H$1:$ L$1,0)))

    If the D2-0.00000000001 was just for rounding requirements

    You could shorten and get rid of checking for items < the min of your value if you ensured you would never get any low values.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help of Formula Needed (Excel-2003)

    =IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(D2-0.00000000001,$G$2:$G$11,1))+1,MATCH(IF(A2=B2,E2,3 ),$H$1:$L$1,0)))

    The formula first looks at D2. If D2 is blank (or has null string in it) then the formula will yield a null string

    If D2 has something in it it extracts something from the table which starts in G1 using OFFSET. Offset reads from a cell and goes a particular number of rows down and columns to the right from that cell. (See OFFSET in Help for more info)

    The row is given by:
    IF(D2<$G$2,0,MATCH(D2-0.00000000001,$G$2:$G$11,1))+1
    If D2< G2 (the lowest value in the table) then the lookup is not in the table and it gives a 0+1 = 1 so it goes 1 row down from G1 (into the 2nd row)
    If D2>=G2 it uses a match to find the closest value to it. (See MATCH in help for more info).

    The column is given by:
    MATCH(IF(A2=B2,E2,3),$H$1:$L$1,0)

    If Col A and Col B is the same, it uses the value in Col E. If they are different a rank of 3 is used. The MATCH just looks at the column headings for a match to the rank.

    So starting at G1 you find the COMPA value using the row "match" and the rank finding the column "match"

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help of Formula Needed (Excel-2003)

    Hi Steve,

    Thanks for the the formula.

    However, i would like to know exactly what this formula does, coz i havent figured it out.

    If i understand the formula, should be able to let you know what exactly i need.

    Can you please help me understand the formula steve.

    Regards
    Baiju

  5. #5
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help of Formula Needed (Excel-2003)

    Hi Steve,

    Thanks for explaining the formula, I am moving toward completing my task.

    However, while i was using the formula, i realised that, if the value in the Col = D2, it is still going 1 row down, whereas i need the value from the same row

    Can you help me out on the same.

    Regards
    Baiju

  6. #6
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help of Formula Needed (Excel-2003)

    Hi Steve,

    Just realised, when i use =IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(D2-0.00000000001,$G$2:$G$11,1))+1,MATCH(IF(A2=B2,E2,3 ),$H$1:$L$1,0))) it works fine, however when i use
    =IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(ROUND( D2,3),$G$2:$G$11,1))+1,MATCH(IF(A2=B2,E2,3),$H$1:$ L$1,0)))
    i face the problem.

    Can you please help me out.

    Regards
    Baiju

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help of Formula Needed (Excel-2003)

    The I would use the first 1. It seems to not be a rounding issue but that you want to be a little less than the value in D2.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help of Formula Needed (Excel-2003)

    Hi Steve,

    When i use the formula with the rounding off. When i get a match for the exact % it goes 1 row down thus not giving me the exact %Hike.

    Can you suggest a better formula if any for the same.
    Wherein in my table starting I1 the minimum value will always be 80%. max 1000% and my rating will be 1, 2, 3+,3,4.

    I need the formula to check % in Col D & rating in col E and match with the table starting I1 and give the % based on the hike.

    Looking forward to your help of this.

    Regards
    Baiju

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help of Formula Needed (Excel-2003)

    The -0.00000000001 will cause the formula to move one row down if there is an exact match for the value in column D.
    If you omit -0.00000000001 that won't happen.
    As an alternative, you could round the value of D2 in the formula to 3 or 4 decimal places:
    <code>
    =IF(D2="","",OFFSET($G$1,IF(ROUND(D2,3)<$G$2,1,MAT CH(ROUND(D2,3),$G$2:$G$11)+1),MATCH(IF(A2=B2,E2,3) ,$H$1:$L$1,0)))</code>

  10. #10
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help of Formula Needed (Excel-2003)

    HI Hans,

    Thanks for your formula. Its giving me the same result. When i get an exact match in D2, it goes one row down in Col I, whereas when i use the -0.00000000000001 i get the right match.

    Regards
    Baiju

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help of Formula Needed (Excel-2003)

    I'm not sure anymore what your problem is. Do you have a working formula, or don't you? If not, could you attach a smaller sample workbook with some examples of values for which the result is correct, and some for which the result is incorrect? Please indicate the latter in some way, for example by using a different color, or by putting an X in column F. Thanks in advance.

  12. #12
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help of Formula Needed (Excel-2003)

    Hi Hans,

    I am attaching the file with both the formulas which are giving me two different results. Hope this will help you understand what the problem is.

    The results are highlighted.

    Regards
    Baiju

  13. #13
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help of Formula Needed (Excel-2003)

    Hi Hans,

    Forgot the attachment.

    Regards
    Baiju

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help of Formula Needed (Excel-2003)

    Are there situations in which the formula in column N (Check 1) does *NOT* produce the desired result?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help of Formula Needed (Excel-2003)

    Perhaps the formulas would work better if the lookup table were sorted in descending order. See the attached version.

Posting Permissions

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