On 19/05/2008 16:33, John Kaufmann wrote:
In a message dated 2008.05.19 07:40 -0500, Harold Fuchs wrote:
S.No. Name Telugu Hindi English Total Percentage Grade Class
1 A 35 48 45 128 42.67% PASS
2 B 97 84 87 268 89.33% PASS
3 C 98 98 97 293 97.67% PASS
4 D 57 67 48 172 57.33% PASS
5 E 54 65 30 149 49.67% PASS
How can we classify their class as I class(>60%), II class(50%), III
class(>35%)
Assume the percentages are stored in Column G and that they are storted
as decimal fractions e.g 60% is stored as 0.6, then make a new column,
say H, for the "class" and set H1 to the formula:
=1*(G1>0.6)+2*AND(G1>=0.5;G1<0.6)+3*AND(G1<0.5;G1>0.35)
...
This works because a TRUE value is represented by a numeric 1 and a
FALSE
value by a numeric 0. Only one of the three expressions between the
"+" signs is TRUE (1) and the others are FALSE (0) so the arithmetic
works.
It's a very old trick; I think I first learnt it in about 1974 and it
wasn't new then.
Harold, this is slick, but is it really preferable to the more
straightforward
IF(G1>0.6;1;IF(G1>0.5;2;IF(G1>.35;3)))
[or IF(G1>0.6;"I";IF(G1>0.5;"II";IF(G1>.35;"III"))) ] ?
The nested IF() is not only traditional in spreadsheets, but has the
functional advantage of only requiring the entry of each breakpoint
once. Or did I miss something?
John,
Yes, yours is better. I didn't know one could nest IF's like that :-(
Hope you don't mind but I'm copying this to the unsubscribed OP.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
