# Thread: Help of Formula Needed (Excel-2003)

1. ## 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. ## 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. ## 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. ## 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.

Regards
Baiju

5. ## 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. ## 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.

Regards
Baiju

7. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Help of Formula Needed (Excel-2003)

Hi Hans,

Forgot the attachment.

Regards
Baiju

14. ## 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. ## 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
•