Any Excel Gurus out there?

[a3dm86]

I need some help please.

I am capturing marks and i have to retain the best 6 marks out of 8 tutorials.
I'm using this formula>
=(LARGE(D4:K4,1)+LARGE(D4:K4,2)+LARGE(D4:K4,3)+LARGE(D4:K4,4)+LARGE(D4:K4,5)+LARGE(D4:K4,6))/6

eg.
70 90 60 60 69 70 53 =69.83

The problem arises when a student did less than 6 tuts and an error message pops up.

How do i make blank cells return as 0 in the formula?

I could enter zeros to make the the above formula work, but that would mean the student attempted the tut and got 0.

Thanks in advance.

 

[ettubrute]

=(IF(ISERROR(LARGE(D4:K4,1)),0,LARGE(D4:K4,1))
+IF(ISERROR(LARGE(D4:K4,2)),0,LARGE(D4:K4,2))
+IF(ISERROR(LARGE(D4:K4,3)),0,LARGE(D4:K4,3))
+IF(ISERROR(LARGE(D4:K4,4)),0,LARGE(D4:K4,4))
+IF(ISERROR(LARGE(D4:K4,5)),0,LARGE(D4:K4,5))
+IF(ISERROR(LARGE(D4:K4,6)),0,LARGE(D4:K4,6)))/6

Maybe not the most elegant, but it works!

 

[ettubrute]

Just played some more, and you could also use the function IFERROR:

=IFERROR(LARGE(D4:K4,1)),0)+IFERROR(LARGE(D4:K4,2)),0)+IFERROR(LARGE(D4:K4,3)),0)
+IFERROR(LARGE(D4:K4,4)),0)+IFERROR(LARGE(D4:K4,5)),0)+IFERROR(LARGE(D4:K4,6)),0)

Looks a lot better!

 

[a3dm86]

Cool, thanks alot. I really appreciate it!

 

[a3dm86]

For the second one it says "you've entered too few arguments"

Found out why, too many brackets!
This one works
=(IFERROR(LARGE(D4:K4,1),0)+IFERROR(LARGE(D4:K4,2),0)+IFERROR(LARGE(D4:K4,3),0)+IFERROR(LARGE(D4:K4,4),0)+IFERROR(LARGE(D4:K4,5),0)+IFERROR(LARGE(D4:K4,6),0))/6

 

[a3dm86]

The first one works like a peach!

 

[ettubrute]

Enjoy!

 

[vanHunks]

If this hasn't occurred to you already, do you want the average over six tests or the number of tests actually taken ? If the latter then you'll need to count the number of scores. IYDKIA use one of the COUNTx functions. I discovered the LARGE() function recently myself - very handy.