Jump to content

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. 

 

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

Link to comment
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

Link to comment
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

Link to comment
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.

  • Like 1
Link to comment
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

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.