More Logical Test Needed.

  • Thread starter Thread starter Krappo
  • Start date Start date
K

Krappo

The following formula is unacceptable as
the Logical are more than 7.

=IF(AND(100>=B4,B4>=96),1,IF(AND(95>=B4,B4>=90),2,IF(AND(89>=B4,B4>=81),3,IF
(AND(80>=B4,B4>=76),4,IF(AND(75>=B4,B4>=66),5,IF(AND(65>=B4,B4>=61),6,IF(AND
(60>=B4,B4>=56),7,IF(AND(55>=B4,B4>=50),8,IF(AND(49>=B4,B4>=0),9,"")))))))

Is there any possible way / method to
make it work?

How to simplify this formula to make it
shorterned?

Thanks in advance.

Krappo.
 
The following formula is unacceptable as
the Logical are more than 7.

=IF(AND(100>=B4,B4>=96),1,IF(AND(95>=B4,B4>=90),2,IF(AND(89>=B4,B4>=81),3,IF
(AND(80>=B4,B4>=76),4,IF(AND(75>=B4,B4>=66),5,IF(AND(65>=B4,B4>=61),6,IF(AND
(60>=B4,B4>=56),7,IF(AND(55>=B4,B4>=50),8,IF(AND(49>=B4,B4>=0),9,"")))))))

Is there any possible way / method to
make it work?

How to simplify this formula to make it
shorterned?
Click to expand...

Use [V]LOOKUP.

=LOOKUP(B4,{-1E+300;0;50;56;61;66;76;81;90;96;100.000001},
{"";9;8;7;6;5;4;3;2;1;""})
 
I read an article in pcmag and they use the lookup
statement, you may search for the info in pcmag.com
 
What a relief!

Thanks Harlan and Ken, it works
great!

And thanks for the info puronombre.
 
Back
Top