Subtract for null fields

[MikeKD]

Hi folks,

When kids audition for scholarships, they perform on 1-3 instruments.

I currently take the average of these three fields in a calculation field. 

 

Average ( Perf 1 grade ; Perf 2 grade ; Perf 3 grade )  * 3

 

(the *3 is just to give this score a higher importance compared with other test we run)

I could really do with applying a penalty for kids who only perform on one or two instruments.

 

How do I check for null fields and then subtract accordingly?

 

Cheers!

Mike

[Rick Whitelaw]

Why not simply enter 0 for the performance they don't do? Then there are no null values.

[MikeKD]

Thanks Rick,

If I do that, the penalty for having a null field is too severe.

(In effect I was doing that before, by just summing the fields, but it give results that penalised kids who were really musical but not all rounders).

 

Cheers!

Mike

[comment]

If I do that, the penalty for having a null field is too severe.

 

So what is the penalty that you do want to apply?

[MikeKD]

HI Comment,

Somewhere between 1-4 marks; I think it's going to take a bit of tweaking to come up with a formula that's fair!

I was probably going to make the penalty a global so I could tweak rather than hard coding it - that is once I've worked out how to get the formula working!!

 

Cheers,

MIke

[comment]

Somewhere between 1-4 marks

 

I am not sure what that means in numerical terms. Perhaps you could try something like =

3 * Average ( Perf 1 grade ; Perf 2 grade ; Perf 3 grade ) - gPenalty * ( 3 - Count ( Perf 1 grade ; Perf 2 grade ; Perf 3 grade ) )

This will subtract the amount of gPenalty for each empty field among the three.

 

 

--

Unrelated to your question, but those three fields should really be records in a related table.

[MikeKD]

 

 

Unrelated to your question, but those three fields should really be records in a related table.

Yes, you're right. I'm modifying a database I wrote a few years ago that's already got several years worth of data in it.

 

Thanks a lot - trying that formula now...

 

Cheers,

Mike

[MikeKD]

Yep, that all worked very nicely thanks. :-)