• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Sheet wont add up anything past $999.99

Explorer ,
Jan 17, 2024 Jan 17, 2024

Copy link to clipboard

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

Translate

Translate

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 guidelines
Community Expert ,
Jan 17, 2024 Jan 17, 2024

Copy link to clipboard

Copied

Do you use any format script in that field?

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

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

Votes

Translate

Translate

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 guidelines
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

Copied

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

If that doesn't help, post your file.

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

Copied

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

Votes

Translate

Translate

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 guidelines
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

Copied

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

tempsnip.png

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

Copied

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

Votes

Translate

Translate

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 guidelines
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

Copied

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

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

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

 

 

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

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

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

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?

Votes

Translate

Translate

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 guidelines
Community Expert ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

Copied

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

GST1+.01

Is tax always same 4.13?

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 18, 2024 Jan 18, 2024

Copy link to clipboard

Copied

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

Votes

Translate

Translate

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 guidelines
Community Expert ,
Jan 19, 2024 Jan 19, 2024

Copy link to clipboard

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?

 

 

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 19, 2024 Jan 19, 2024

Copy link to clipboard

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. 

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 19, 2024 Jan 19, 2024

Copy link to clipboard

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.

Votes

Translate

Translate

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 guidelines
Community Expert ,
Jan 19, 2024 Jan 19, 2024

Copy link to clipboard

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;

 

Votes

Translate

Translate

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 guidelines
Explorer ,
Jan 20, 2024 Jan 20, 2024

Copy link to clipboard

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! 

Votes

Translate

Translate

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 guidelines