Thread: Shorten Formula ? (Excel 2002/2003)

1. Shorten Formula ? (Excel 2002/2003)

Hi

Can someone help with shortening this Formula please.

=IF(AND(V62<30),D62,IF(AND(V62>30)*AND(V62<40),40, IF(AND(V62>40)*AND(V62<50),E62,IF(AND(V62>E62)*AND (V62<80),80,IF(AND(V62>80)*AND(V62<100),100,IF(AND (V62>100)*AND(V62<120),120,IF(AND(V62>120)*AND(V62 <180),180,V62)))))))

2. Re: Shorten Formula ? (Excel 2002/2003)

Can you explain in words what the formula is supposed to do?

3. Re: Shorten Formula ? (Excel 2002/2003)

Hi Hans

At this point in time I can't, I will take a closer look and get back to you.

Thanks

4. Re: Shorten Formula ? (Excel 2002/2003)

The formula as it is looks very strange, but it can be shortened to

=IF(V62<30,D62,IF(V62<40,40,IF(V62<50,E62,IF(V62<8 0,80,IF(V62<100,100,IF(V62<120,120,IF(V62<180,180, V62)))))))

Since I don't know its purpose, its impossible for me to know if it does what you want. But I think you should either change the single occurrence of E62 in the formula to 50, or else change 50 to E62.

It may be possible to shorten the formula further using MATCH and INDEX.

5. Re: Shorten Formula ? (Excel 2002/2003)

Hi Hans

Thank you for your efforts, I will try it against the old code and see if I get the same result, I have just inherited this workbook and am trying to unravel it.

Thanks again