# Subtract for null fields

This topic is 2941 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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.

(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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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?

##### Share on other sites

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

##### Share on other sites

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.

• 1
##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

This topic is 2941 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account