Formula conversions or new formula needed
Posted by Steve on October 10, 2000 9:04 AM
Hi, I'm working on converting a Lotus 1-2-3 worksheet into an Excel file. Now, for about 20-25 certain cells, the formulas didn't convert (i.e. "formula failed to convert"), and it's because there are more than 7 sublevels of the formula (i.e. 9 IF statements). I need to know how I can make this a working formula in Excel. Here is an Example of the conditions for one cell that need to be put into one working formula...
For B58
IF B58 >=100,000 AND <200,000, $D424
IF B58 >=200,000 AND <300,000, $E424
IF B58 >=300,000 AND <350,000, $F424
IF B58 >=350,000 AND <400,000, $G424
IF B58 >=400,000 AND <600,000, $H424
IF B58 >=600,000 AND <700,000, $I424
IF B58 >=700,000 AND IF B58 >=800,000 AND <1,000,000, $K424
IF B58 >=1,000,000, $L424, ""
I cannot shorten the amounts (i.e. take out 350,000) because the Lotus program uses those amounts all over the program. HELP!
Posted by Ben O. on October 10, 2000 11:37 AM
Try =HLOOKUP(B58,D423:L424,2)
By itself that formula won't work--D424:L423 need to have values that the formula will compare B58 to, and return the value of the cell directly beneath it (in row 424) if it matches.
So in D423 you would need a formula like IF(AND(B58>=100000,B58<200000),B58,"") which will return the value of B58 if it meets your criteria (nothing if it doesn't). Do the same thing for the rest of the cells in row 423. Then the HLOOKUP formula I gave you will work.
As always, I'm sure there are better ways of doing this!
-Ben
Posted by Loren on October 10, 2000 11:39 AM
Ans:
Refer to Aaron Blood's website. He is AB, in the
Excel games item of Oct. 9 just below. He has an
example of beating the Nested If problem.
