• Global community
• Language:
• Deutsch
• English
• Español
• Français
• Português
• 日本語コミュニティ
Dedicated community for Japanese speakers
• 한국 커뮤니티
Dedicated community for Korean speakers
Exit
• Sheet wont add up anything past \$999.99

# Sheet wont add up anything past \$999.99

Explorer ,
Jan 17, 2024 Jan 17, 2024

Copied

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?

TOPICS
General troubleshooting

Views

306

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
17 Replies 17
Community Expert ,
Jan 17, 2024 Jan 17, 2024

Copied

Do you use any format script in that field?

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copied

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);

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copied

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

If that doesn't help, post your file.

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copied

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

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copied

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

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copied

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

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copied

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

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copied

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!!

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copied

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);

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copied

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?

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copied

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

GST1+.01

Is tax always same 4.13?

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copied

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

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Community Expert ,
Jan 19, 2024 Jan 19, 2024

Copied

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?

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 19, 2024 Jan 19, 2024

Copied

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.

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 19, 2024 Jan 19, 2024

Copied

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.

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Community Expert ,
Jan 19, 2024 Jan 19, 2024

Copied

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;``````

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Explorer ,
Jan 20, 2024 Jan 20, 2024

Copied

LATEST

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!