Excel 2000 - Limit to number of 'IF' statements? 2

[dsmith910]

Hi
I use Excel 2000 on Windows 2000XP. I have just tried entering the following function

=IF($K5<199,&quot;None&quot;,IF(K5<999,&quot;Flyball&quot;,IF(K5<2999,&quot;Intermediate&quot;,IF(K5<4999,&quot;Graduate&quot;,IF(K5<9999,&quot;Advanced&quot;,IF(K5<14999,&quot;Silver&quot;,IF(K5<19999,&quot;Gold&quot;,IF(K5<24999,&quot;Blue&quot)))))))

this works fine until I try and add an extra 'IF' thus:

=IF($J3<199,&quot;None&quot;,IF(J3<999,&quot;Flyball&quot;,IF(J3<2999,&quot;Intermediate&quot;,IF(J3<4999,&quot;Graduate&quot;,IF(J3<9999,&quot;Advanced&quot;,IF(J3<14999,&quot;Silver&quot;,IF(J3<19999,&quot;Gold&quot;,IF(J3<24999,&quot;Blue&quot;,IF(J3<29999,&quot;Platinum&quot))))))))

As soon as I add the extra 'IF' I get an error message saying there's something wrong with the formula.

If I have used too many 'IF' statements is there a more 'elegant' solution to my problem? I have at least 3 more levels to add.

Thanks

Doreen

 

[PepeLeMokko]

Try this post from xlbo
you can use NON NESTED if statements tho
=IF(A1=1,1,&quot;&quot&if(A1=2,2,&quot;&quot&if(A1=3,3,&quot;&quot.....etc etc ad nauseum

 

[dsmith910]

Hi

Many thanks I'm off to try this out.

Doreen

 

[dsmith910]

Hi

Still Stuck! My original formula is really asking for the return of a value when a number falls within a certain scope, i.e. if cell J3 falls between 999 and 2999 then it returns a value of Intermediate, etc. Once I nest the If statements this just doesn't seem to work no matter what I put in. Rats!!!

Doreen

 

[PepeLeMokko]

I suggest you use a vlookup function with the false attribute.
=vlookup(k5;$a$1:$b$8;2;false)
K5 is the lookup value
Col A with the values to look up and col B the results
Very important that values in the A col should be sorted in ascending order.

For more info have a look at the Help menu or use the lookup wizard
If you want to send me the sheet mail it to coyoteooc@swing.be

 

[HiCard]

You can try this.
1. build your array in a second worksheet (i.e. sheet 2)
A B C
1 100000 Titanium
2 89999 Steel
3 59999 Aluminum
4 24999 Platinum
5 19999 Blue
6 14999 Gold
7 9999 Silver
8 4999 Advanced
9 2999 Graduate
10 999 Intermediate
11 199 Flyball
12 0 n/a
2. In your worksheet, you can then set two new cells as
follows:
D3 =(MATCH(C3,Sheet2!$B$1:$B$12,-1))
assuming that the data you are looking up is in C3
D3 will then contain 1, 2, 3, 4 etc
E3 =LOOKUP(D3,Sheet2!$A$1:$A$12,Sheet2!$C$1:$C$12)
E3 will then contain the description gold,
silver etc.

 

[Zathras]

By now, you have probably discovered that some of the proposed &quot;solutions&quot; do not solve your problem. While I agree in principle with Coyoteooc that a separate table using VLOOKUP is the best way over-all (except you need TRUE and not FALSE as the third argument) and I agree that HiCard is on the right track (except the table is upside-down), it is possible to get more out of the IF statement by using a tree-structure.

Here is a formula with IF that will do the trick (This time -- but what happens when you add more break points? That is why VLOOKUP is better.)

=IF(K5<9999,IF($K5<199,&quot;None&quot;,IF(K5<999,&quot;Flyball&quot;,IF(K5<2999,&quot;Intermediate&quot;,IF(K5<4999,&quot;Graduate&quot;,&quot;Advanced&quot;)))),IF(K5<14999,&quot;Silver&quot;,IF(K5<19999,&quot;Gold&quot;,IF(K5<24999,&quot;Blue&quot;,&quot;Platinum&quot;))))

 

[Wray69]

Here is a way to do it that I know will go at least 26 deep, if you havent already found a way to accomplish what you want...

=IF(OR($K5<199,$K5<999,$K5<2999,$K5<4999,$K5<9999,$K5<14999),IF($K5<199,&quot;None&quot;,IF($K5<999,&quot;Flyball&quot;,IF($K5<2999,&quot;Intermedate&quot;,IF($K5<4999,&quot;Graduate&quot;,IF($K5<9999,&quot;Advanced&quot;,&quot;Silver&quot)))),IF(OR($K5<14999,$K5<19999,$K5<24999,$K5<59999,$K5<89999,$K5<100000),IF($K5<14999,&quot;Gold&quot;,IF($K5<19999,&quot;Blue&quot;,IF($K5<24999,&quot;Platinum&quot;,IF($K5<59999,&quot;Aluminum&quot;,IF($K5<89999,&quot;Steel&quot;,&quot;Titanium&quot))))))

Regards -

Wray

 

[dsmith910]

Wow!!!

Can't believe how many replies I've received whilst my computer has been off line! Am rushing off to try these solutions out - but at first glance I think the easiest may be the vlookup as its easier to 'see' - but this will be the third vlookup being used by this particular sheet - hopefully that wont matter.

Thanks a million for all the helping hands.

Doreen

 

[PepeLeMokko]

dsmith910,
just keep in mind that vlookup functions could slow down opening of the wbook
I advise you should reverse to manual calculation ( see Tools > Options and there calcualtion Tab - check &quot; Manual&quot;. When you need results, F9 will calculate. Also don't forget it is manual, to avoid some bitching), otherwise the vlookup will start calculating each time you open the wbook and you could be waiting...

 

[dsmith910]

Hi Guys

Tried out the vlookup idea last night - and it works fine. Have I done it right though - the only way I could think of was for the 'table' to contain 0 through to 30,000, it didn't like < or > so the only other option I could think of was to list each and every number, then add the 'levels' next to them. Hope this is what you meant, either way it works and it hasn't slowed things down too much - yet!

Thanks a million

Doreen

 

[Zathras]

You only need nine entries (but column headers are a good thing to have:

A1: 'Score
A2: 0
A3: 199
A4: 999
A5: 2999
A6: 4999
A7: 9999
A8: 14999
A9: 19999
A10: 24999
B1: 'Grade
B2: 'None
B3: 'Flyball
B4: 'Intermediate
B5: 'Graduate
B6: 'Advanced
B7: 'Silver
B8: 'Gold
B9: 'Blue
B10: 'Platinum
D2: 3000
E2: =VLOOKUP(D2,A2:B10,2)

Try various numbers in cell D2 and see what happens.

 

[xlbo]

Read Zathras' post carefully:
&quot;a separate table using VLOOKUP is the best way over-all (except you need TRUE and not FALSE as the third argument) and I agree that HiCard is on the right track (except the table is upside-down),&quot;
The 2 clues you need are in there

The FALSE as the 4th argument searches for an exact match
If you use TRUE, &quot;the next largest value that is less than lookup_value is returned&quot; (from excel help file). that is why the table would need to be the opposite way round from Hi-Card's
So, using
=vlookup(k5,$a$1:$b$12,2,TRUE)
as your formula, your table would look like this:

A B
0 N/A
199 Flyball
999 Intermediate
2999 Graduate
4999 Advanced
9999 Silver
14999 Gold
19999 Blue
24999 Platinum
59999 Aluminium
89999 Steel
100000 Titanium

If you want to give a star, give it to Zathras - he gave you all the info you need Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[dsmith910]

Hi Guys
Have finally managed to get time to check the vlookup out - and it works like a dream. Believe it or not my spreadsheet is now up and running and working hard. Some of my 'solutions' may not be as elegant as those you produce - but they work. Can't thank you enough - I may even throw away my 'quill'.

Doreen

PS: if you can think of a way for the spreadsheet to 'count' those cells within a column which have content - regardless of what that content is, I'd be grateful, i.e. if column A has a value either text or numerical in say 6 rows it would return a value of 6, column B may only have content in 4 rows,etc, etc. At the moment I've created 6 extra columns with =IF(A2=0,&quot;&quot;,1). The total of that column is then returned to A1. I noticed that there's a 'count' function but I couldn't seem to get it to work as it seems to be referring to numbers within a range not just any content?

 

[dsmith910]

Hi Guys
Have finally managed to get time to check the vlookup out - and it works like a dream. Believe it or not my spreadsheet is now up and running and working hard. Some of my 'solutions' may not be as elegant as those you produce - but they work. Can't thank you enough - I may even throw away my 'quill'.

Doreen

 

[TonyJollans]

I'm sure lots of people will jump in with this one and there's probably a better way but, provided you don't put this in Column A (which would cause a circular reference), you can get your count by using

65536-COUNTBLANK(A:A)

Enjoy,
Tony

 

[xlbo]

Use CountA rather than count - the &quot;A&quot; stands for &quot;All&quot; as opposed to Count which is for numeric entries only Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde