Calculation not to exceed a certain amount

[drocha]

I am trying to create the following calculation.

Required number of credits- credits completed= credits needed.

What I need to have happen is for the total not to exceed the required number of credits but still show a total if the the difference is less than the required number of credits- credits completed.

I used the following equation for it not to exeed the required number of credits, but if the balance of credits needed is less than the required credits, it only shows the max number allowed.  

var v1 = Number(this.getField("30").value);
var v2 = Number(this.getField("ABENGCOMP").value);
event.value = Math.max(30, v1-v2);

The highlighted section in the image below shows where the issue is.  The totals on these should be 5 and 3, respectively.

Thanks in advance.

[try67]

Use Math.min, not Math.max...

[drocha]

I changed max to min but still not getting the results I need.  Both should read 5 in the credits needed column.

Thanks

[try67]

Can you share the actual file with us? You can attach it to the original message using the tiny paperclip icon at the bottom when you edit it, or upload it to a file-sharing website (like Dropbox, Google Drive, Adobe Cloud, etc.), generate a share link and then post it here.

[drocha]

https://drive.google.com/file/d/1wp-kkrtk_1MODFoc3Ij1usRWAiZ41cfh/view?usp=sharing

[try67]

I'm not seeing where the code is located...

[try67]

Ah, I see it now. You put it in the wrong place. It needs to go under the Custom Calculation Script option, not the Simplified Field Notation one.

[drocha]

Hmm... moving it didn't work either.

Think I found an easier way to explain, but not sure how to write it to have the calculation work.

If Credits Completed >= Required Credits (varies depending on the course), Credits Needed = zero.  But, if Credits Completed <= Required Credits (varies depending on the course), Credits Needed = difference between Required Credits and Credits Completed.

It's been a long time since I've done an "If, Then" equation I can't quite remember the correct formatting.

[try67]

Let's take the first set of fields. You can use this code as the custom calculation script of "ABENGNEEDED":

var creditsRequired = 30;
var creditsCompleted = Number(this.getField("ABENGCOMP").valueAsString);
if (creditsCompleted>=creditsRequired) event.value = 0;
else event.value = creditsRequired - creditsCompleted;

(you have a mistake in your specification since you used the "=" symbol in both conditions, but since the result is the same either way (0), it doesn't really matter.)

I think your biggest misunderstanding was that the Credits Required values need to be just a number, since you don't have fields for them.

[drocha]

Perfect!!  Works like a dream.

Thanks for your help! 🙂

[drocha]

The calculation for that column is adding 10 to the total.  Can you look at the formula for the Credits Needed Total calculation to see if you can see something I don't?

https://drive.google.com/file/d/13Uj1lNv8wKxP97zAnm6i2uj_X-3v_TfY/view?usp=sharing

[try67]

You have to add "event.value = " to the start of the code in order for the value to actually be applied to the field.

Also, check the console because there are errors in some of your scripts, and also check the fields calculation order, to make sure the total fields are calculated last.

[drocha]

Perfect!!  Thanks for your help! 🙂

[drocha]

I'm back! lol

Need help again with the same form.  In looking more closely at what is being requested, is there a way for the number entered into the Credits Completed cell able to autocorrect itself to equal the number of Required Credits if the amount entered is more than the number of Required Credits?  So let's say in Algebra/Math 1 the student earned 20 credits, what needs to show in the cell is 10, which is the max number of Required Credits.  I tried using a similar calculation to that in the Credits Needed column but couldn't quite get it to work correctly.

If this is possible, the total at the end of that column would need to equal the total number of Required Credits based on the max allowed for each subject.

Here is the link for the sheet.

https://drive.google.com/file/d/1A-vw1c3etoYomAzUvWRa-TMxjZ7AYTMu/view?usp=sharing

Hope I explained this clear enough.  Please let me know if you have any questions.

Thanks in advance 🙂

[try67]

Sure. For example, for the first field you can use the following custom Validation script:

if (Number(event.value)>40) event.value = 40;

[drocha]

That worked perfectly for the calculation of Credits Completed.  Now, for the total in that column... 

I need for it to calculate that column with either the total of the actual number entered if it is equal to the Required Credits or the adjusted number it self calculates to if the number entered is greater than the Required Credits.  Right now it only totals the actual number entered.  Is that possible?

[try67]

That should not be the case. The validation code I provided you changes the actual value of the field, so if you apply it to all of them then the total can't exceed the sum of the Required Credits. If it's not working like that then share the file, please.

[drocha]

I tested it by entering 10 credits more than the required credits in each course.  The calculation for each individual course works perfectly, but the calculation for the total in that column totals the numbers entered.

https://drive.google.com/drive/folders/15ZMP8kBphERaQQYE5OK7DO1dtQi6xeQs?usp=sharing

[try67]

You didn't use the code as I've instructed.

[drocha]

So sorry.  Thank you for clarifying.  Works perfectly!

Thanks again for all your help 🙂