Results 1 to 15 of 15

20060918, 12:47 #1
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Help of Formula Needed (Excel2003)
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

20060918, 13:48 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help of Formula Needed (Excel2003)
This is a little shorter:
=IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(D20.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 D20.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

20060919, 10:31 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help of Formula Needed (Excel2003)
=IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(D20.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(D20.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

20060919, 10:38 #4
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Help of Formula Needed (Excel2003)
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

20060921, 11:19 #5
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Help of Formula Needed (Excel2003)
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

20060921, 11:28 #6
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Help of Formula Needed (Excel2003)
Hi Steve,
Just realised, when i use =IF(D2="","",OFFSET($G$1,IF(D2<$G$2,0,MATCH(D20.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

20060921, 17:16 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help of Formula Needed (Excel2003)
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

20060926, 07:19 #8
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Help of Formula Needed (Excel2003)
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

20060926, 07:48 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Help of Formula Needed (Excel2003)
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>

20060926, 09:05 #10
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Help of Formula Needed (Excel2003)
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

20060926, 09:16 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Help of Formula Needed (Excel2003)
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.

20060929, 07:26 #12
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Help of Formula Needed (Excel2003)
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

20060929, 07:32 #13
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Help of Formula Needed (Excel2003)
Hi Hans,
Forgot the attachment.
Regards
Baiju

20060929, 07:58 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Help of Formula Needed (Excel2003)
Are there situations in which the formula in column N (Check 1) does *NOT* produce the desired result?

20060929, 08:31 #15
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Help of Formula Needed (Excel2003)
Perhaps the formulas would work better if the lookup table were sorted in descending order. See the attached version.