Nested if then else statement

[tzip123]

I need help ! I see that I can only do 7 "if then else statements" in
excel. I need to do about 20. I do not know VBA. Is there any
workaround in excel ? Here is a simple example of what I am trying to
do –

To be able to have each line in a spreadsheet do a search for a value
that can fall between anyone of 20 different areas (where each area has
a preset value) and show that value in a separate cell
Similar to a teachers grade book with 20 options instead of the regular
A,B,C,D etc.

96 – 100 value of 1
91 – 95 value of 2
86 – 90 value of 3
81 – 85 value of 4
76 – 80 value of 5
71 – 75 value of 6
66 – 70 value of 7
61 – 66 value of 8
56 – 60 value of 9
and so on

Any help would be greatly appreciated

 

[joeu2004]

96 – 100 value of 1
91 – 95 value of 2
86 – 90 value of 3
[....]

You could consider VLOOKUP. But since your numbers fit a
pattern, you might consider this (where A1 is the score on
the left):

=1+int((100-A1)/5)

 

[tzip123]

I tried to make things clear with my example, but I now see they are not
clear enough.
The example of a grade book is a bad example. I hope this example will
make things clearer. The data will look something like the following:

85 – 95 value of 1
78 – 87 value of 2
75 – 84 value of 3
90 – 100 value of 4
64 – 77 value of 5
58 – 68 value of 6
84 – 96 value of 7
62 – 70 value of 8
73 – 87 value of 9
etc.

I need to be able to have each line in a spreadsheet do a search for a
value that can fall between anyone of 20 different areas (where each
area has a preset value) and show that value in a separate cell.

so far I can only do 7 if then else statements in excel. I need to do
about 20. I do not know VBA. Is there any workaround in excel ? Any
help would be greatly appreciated

 

[tzip123]

Here is what my "if then else statement" looks like right now - - where
A6 is the value being looked up

IF(AND(A6>=J6,A6<=K6),3,IF(AND(A6>=L6,A6<=M6),4,IF(AND(A6>=N6,A6<=O6),5,IF(AND(A6>=P6,A6<=Q6),6,IF(AND(A6>=R6,A6<=S6),7,IF(AND(A6>=T6,A6<=U6),8,IF(AND(A6>=V6,A6<=W6),9,"")))))))

 

[Biff]

Still not registering!

If your value is 85 then it meets all of these criteria:

85 - 95 value of 1
78 - 87 value of 2
84 - 96 value of 7
73 - 87 value of 9

Biff

 

[Harlan Grove]

The example of a grade book is a bad example. I hope this example will
make things clearer. The data will look something like the following:

85 – 95 value of 1
78 – 87 value of 2

Clear as mud. Why should 86 give value 1 rather than value 2? Or is the
overlap just a typo on your part?

75 – 84 value of 3
90 – 100 value of 4
64 – 77 value of 5
58 – 68 value of 6
84 – 96 value of 7
62 – 70 value of 8
73 – 87 value of 9
etc.

More overlapping ranges. If you're selecting ranges top to bottom, you'll
never return value 8 (62-70) because the 58-68 range would capture 62-68 and
the 64-77 range would capture 68-70.

What are you trying to do? Does it have any rational mathematical basis?

 

[tzip123]

heh - this ain't no mud - lol
and the answer is simple - your 86 example -
the value will be 3, because in excel the if then else statement moves
from left to right and once that condition is met, the if then else
statement will end

and yes i know the numbers are overlapping - that is WHY I REDID THE
EXAMPLE - it does not often over lap but has the potential to overlap
so that is why I gave a new example

 

[prabhuraaman]

You need not the use the if statement at all
Do the following
Type the range of values in text format in one column and the
corresponding values in the next column(e.g)
A B
10-20 5
20-30 6
30- 40 7
Then go to your worksheet
Type your range in cell A1(eg)10-20 in text format
in cell A2,enter the following
formula,VLOOKUP(A1,sheet1!A1:B19,2,FALSE).
Change the cell ranges to fit your need and use excel help to know
about VLOOKUP function
For more queries and answers vist
http://groups.google.co.in/group/answers-for-everything

 

[Harlan Grove]

tzip123 wrote...
....

and yes i know the numbers are overlapping - that is WHY I REDID THE
EXAMPLE - it does not often over lap but has the potential to overlap
so that is why I gave a new example

OK, so you realize that if ranges could overlap, it becomes possible
that some lower ranges could be skipped because higher ranges exhaust
the possible values. Even so, if your tables spans multiple rows in 3
adjacent columns, say A1:C15, you could use INDEX and MATCH as follows.

=INDEX($C$1:$C$10,MATCH(1,(A12>=$A$1:$A$10)*(A12<=$B$1:$B$10),0))