You could also invoke a LOOKUP formula instead of a chain of IFs...

=LOOKUP(B3,{0,2;6,3;11,4;15,5;20,6})

edmdas wrote:
WOW! Works a treat. So simple and so fast.
Knew it must be simple, learn something new everyday.
Thanks a great help!
:)

"Biff" wrote:


Hi!

Try removing the quotes from your
values "2", "3", "4", "5", "6"

When you enclose numbers in quotes Excel treats it as TEXT
and as a result the AVERAGE function ignores TEXT.

Biff


-----Original Message-----
I have set up a Markbook which calculates levels for test

scores.

In Cell B3 is the test score for an individual student in

cell B4 I have the

following formula:
=IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF

(B3<20,"5","6"))))

This pattern is repeated along the row with alternate

test scores and

formula cells which give the corresponding level.
As each test requires different scores for a given level

the numbers vary

slightly.
I have five test scores (ten cells of data).
I would like to average the levels (not the test scores)

I have.

In the last cell of the row I have entered the formula:
=AVERAGE(C3,E3,G3,I3,K3)
But all I get is the Div/0 result.
I have tried, F2, Ctrl, SHIFT, ENTER for the level cells

and the Average

cell but this has not worked.
There must be an easy way round this?
Any help would be appreciated, feeling rather dumb :)
.