MikeKD Posted November 18, 2014 Share Posted November 18, 2014 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 More sharing options...
Rick Whitelaw Posted November 18, 2014 Share Posted November 18, 2014 Why not simply enter 0 for the performance they don't do? Then there are no null values. Link to comment Share on other sites More sharing options...
MikeKD Posted November 18, 2014 Author Share Posted November 18, 2014 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 More sharing options...
comment Posted November 18, 2014 Share Posted November 18, 2014 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? Link to comment Share on other sites More sharing options...
MikeKD Posted November 18, 2014 Author Share Posted November 18, 2014 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 More sharing options...
comment Posted November 18, 2014 Share Posted November 18, 2014 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 Link to comment Share on other sites More sharing options...
MikeKD Posted November 18, 2014 Author Share Posted November 18, 2014 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 More sharing options...
MikeKD Posted November 18, 2014 Author Share Posted November 18, 2014 Yep, that all worked very nicely thanks. :-) Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3445 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 accountSign in
Already have an account? Sign in here.
Sign In Now