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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]