Sheet wont add up anything past $999.99

[Dorothy26138473siql]

I will do my best to explain this but its complicated

I have an expense sheet that has multiple cells in a row that are calculated and extended right, that works, it adds up four cells and does a calculation to dive the GST in half, all good. However, to add up the entire sheet with the full GST I am adding up the sum of multiple rows. if I get to a point in the sheet where the total adds up to anything more than $999.99 it returns this in the box $1.#R if i click on that total box, it shows me the actual $ amount, but as soon as i click out of it all i can see is that error code above. 

Help?

[Nesa Nurani]

Do you use any format script in that field?

[Dorothy26138473siql]

Yes, to make it so the $0.00 doesnt show until something is added into that cell. However, I deleted the script and the problem still exists. 

Calculation is as follows: just adding up three fields. (which are sums of other longer fields, i think 12 rows i have in each so this sheet can get quite large its for expenses. 

Value of sum field is kmtotal, mealstotal, rectotal

Validation Script 

if((event.value == 0) || isNaN(event.value) || (event.value == Infinity))
event.value = "";
else
event.value = util.printf("$%,0.2f",event.value);

[Nesa Nurani]

Did you try removing 'else' part from the script and format field as number?

If that doesn't help, post your file.

[Dorothy26138473siql]

I removed "else" that didnt make a difference, not sure what you mean by "format field as number"

[Nesa Nurani]

In field properties, under format tab, you can select number of decimals and currency sign:

[Dorothy26138473siql]

Oh yes, it's set the same as you have in the picture. 

[Nesa Nurani]

Then there is no reason to use 'printf' in your script.

[Dorothy26138473siql]

ok, i took everything off starting with else, i thought you just meant the word else. I am using that script from someone else that gave it to me here  in the forums, i dont really know how to write them myself. So now I have this 

if((event.value == 0) || isNaN(event.value) || (event.value == Infinity))
event.value = ""; 

and it seems to be working, thank you!!

[Dorothy26138473siql]

can you help me with this calculation, this calculation takes a dollar amount of GST tax thats 4.13 divides that by two and rounds down so 2.06 but, i need to do a seperate calculation with the extra penny added to another calculation so i have one column that will spit out the rounded down and one that spits out the rounded up 

var v = Number(this.getField("GST1").valueAsString) / 2;
var nDecimals = 2;
event.value = Math.floor(v * Math.pow(10, nDecimals)) / Math.pow(10, nDecimals);

[Dorothy26138473siql]

I have this as a calculation script (Rectotal1 - GST1)+KM1+Meals1+GSThalf1 I need GST1 to divide in half rounded up, if that makes sense?

[Nesa Nurani]

If it's just one penny up, why just not add it to GST1?

GST1+.01

Is tax always same 4.13?

[Dorothy26138473siql]

No, unfortunately, every line will have a different amount. Some will divide equally some won't, all different amounts. 

[Nesa Nurani]

Replace Math.floor with Math.ceil, then if you want to use it in your calculation instead of event.value set it to variable and in your calculation replace 'GST1' with that variable name.

Do you use this: (Rectotal1 - GST1)+KM1+Meals1+GSThalf1 in simplified field notation or in custom calculation script, if the latter where is the rest of the script?

[Dorothy26138473siql]

This (Rectotal1 - GST1)+KM1+Meals1+GSThalf1 is in Simplified field , but it isn't woring right, I probably have something wrong.

Do you mean this for the script? It's in custom validation script

var v = Number(this.getField("GST1").valueAsString) / 2;
var nDecimals = 2;
event.value = Math.ceil(v * Math.pow(10, nDecimals)) / Math.pow(10, nDecimals);

and how do i add this so that it doesnt' show $0.00 before a number has been entered. I don't know how to put the two scripts together.

It still isn't working. I have to have something wrong. 

[Dorothy26138473siql]

I have attached the sheet. The last column needs to read 554.96 so that the two cells at the bottom "gsthalf1 and text1" add up to the total in Rectotal1 For the GST calculation the reasoning is we pay the government the rounded down amount of GST, the company eats the extra penny when it doesn't divide evenly. So we have to be able to post the numbers as calculated in the sheet to quick books so we need both values to calculate. GSThalf1 is rounded down the penny, that works. Each row will have different numbers put into them.

[Nesa Nurani]

You have a lot of calculation in the file, and you have good field naming you could replace a lot of calculations if you use loop to calculate entire columns with one script.

To answer your question, remove validation script from 'Program' field and instead of simplified field notation use this custom calculation script:

var gst = Number(this.getField("GST1").valueAsString);
var rectotal = Number(this.getField("Rectotal1").valueAsString);
var km = Number(this.getField("KM1").valueAsString);
var meals = Number(this.getField("Meals1").valueAsString);
var v = gst / 2;
var nDecimals = 2;
var gsthalf = Math.ceil(v * Math.pow(10, nDecimals)) / Math.pow(10, nDecimals);
event.value = (rectotal-gst)+km+meals+gsthalf;

[Dorothy26138473siql]

I am still pretty new at this, as I am sure you can tell 🙂 I do't really know what you mean by replacing with loop, I will look into that at a later date. For now, this is working perfectly, thank you so much for all your time and experitse it is very much appreciated!