ExcelBanter - View Single Post

Harlan Grove

wrote...
Ragdyer wrote:
Care to comment on:
=LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three
Quarters","Full"})

Su try putting -9.9e307 into A1. Klunk!

Eventually, you will hit upon the smallest possible negative number --
at least, what you think is the smallest. But what happens to the
correctness of the spreadsheet when the world moves on to 128-bit
floating point as the "norm"? (The 80-bit and 128-bit formats are
already defined by the standard.)
....
I prefer to write formulas that always work -- unless there is good
reason for a short-cut. IMHO, if(cond,"",lookup(...)) is not so
complex that it deserves a short-cut that does not always work.
....

Too many darn pedantic trees in the way to see the forest?

You want fairly simple, general, and FTHOI concise,

=CHOOSE(MIN(MAX(A1,37)-36,5),"Empty","One Quarter","Half","Three
Quarters","Full")

You want it simpler at the cost of a bit less concise,

=LOOKUP(MAX(A1,37),{37,"Empty";38,"One Quarter";39,"Half";40,"Three
Quarters";
41,"Full"})

As long as your theoretical 1024-bit CPU can handle integers in the
range 37 to 41, this should work.