Help of Formula Needed (Excel-2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

=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 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hi Hans,

Forgot the attachment.

Regards
Baiju

Reply | Quote

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

Reply | Quote

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

Reply | Quote