Hi - I'm analysing an export of numbers that only provides two decimal places, which is causing errors when, for example, 26.6666666 becomes 26.67 and 30.8333333 becomes 30.83.
The resultant calculations are therefore off.
I'm hoping that someone out there will be able to guide me as to how to 'force' Numbers to recognise that these decimals should be as originally intended which would produce the correct calculation that I need.
Many thanks
Adam
I suppose you export a Numbers file to a CSV file, right? The exported values are the displayed ones and not the internal ones. So even if the internal value is 0.142856142856 but you format it to 0.14 (Number, 2 decimals), the CSV file will contain 0.14.
Therefore, what you have to do before exporting is select all the values and set their format to Automatic to display all decimals.
I suppose you export a Numbers file to a CSV file, right? The exported values are the displayed ones and not the internal ones. So even if the internal value is 0.142856142856 but you format it to 0.14 (Number, 2 decimals), the CSV file will contain 0.14.
Therefore, what you have to do before exporting is select all the values and set their format to Automatic to display all decimals.
At Least 3 Character Username wrote:
Unless anyone has a better solution, I'll go with the LOOKUP table suggestion that you made.
You don't really need a lookup table. The MROUND function will do the job:
If the imported value is in A2, then:
=MROUND(A2,1/6)
Fill this down an adjacent column, then remove the formulas by double-clicking that column's letter (which selects all its body cells) followed by command-c and Edit > Paste Formula Results. Then, if you want, delete the original column with the unwanted rounded values.
SG
Obviously, Numbers cannot guess what is the original value behind what it's receiving. Is 1.33 in fact 1.3287674, 1.33, 1.33134 or 1.333333333?
If the received values are ALWAYS sixths, 0, 1/6, 1/3, 1/2, 2/3, 5/6, you could build a small conversion table to recreate the real value. You can always adapt to other fractions as well.
Formula for the conversion, in Table2::B2 in the picture below.
=TRUNC(A2)+XLOOKUP(MOD(A2,1),Sixths::A,Sixths::B,0)
If all the following are true
Then you can correct the VAT in a new column using the formula
=ROUND(F×6,0)÷6
Using that new column you can calculate the taxable amount and from there you can calculate the non-taxable amount.
Numbers will not be able "correct" the CSV data upon import because it has no way of knowing what the "correct" values are. In fact, Ii all of those numbers are from the CSV, they all tie together so there is no way of knowing if the problem is the non-taxable, taxable, or VAT number. They all go together. You change one and at least one of the others has to change also.
Are all the columns from the CSV or are some calculated in your spreadsheet after importing the CSV? I am assuming the data from the CSV is only the Total values and the VAT amounts and you calculated the non-taxable and taxable values. Is that correct? I'm trying to think of a way for you to correct the results but they all tie together so it isn't readily apparent how to guarantee the result of the "correction" will be correct.
Also, it looks like the VAT is not the 20% that it says in the header. It appears to be 16.666...% (i.e., 1/6).
Thanks, but the export comes from another program in which the export options unfortunately are limited. I'm wondering if there's a way that Numbers could recognise, say 1.33 as 1.333333 or 2.67 as 2.666666....
Here's a screenshot. The non-taxable column shouldn't have the small errors of 0.02 in it, which is caused by the rounding error in the VAT column. (30.83 should be 30.8333333, 26.67 should be 26.666666, you get the idea...)
Unless anyone has a better solution, I'll go with the LOOKUP table suggestion that you made.
The VAT appears to be 1/6 of the taxable amount. It is not 20% (1/5) of the taxable amount.
Which columns of your table are from the CSV data and which are calculated by formula in your spreadsheet? If all come from the CSV then I don't see how to say with 100% certainty that the numbers in the non-taxable column (or VAT column) are incorrect. For example, how do you know for sure that 40.02 is incorrect? How do we know with 100% certainty that it is supposed to be 40.00?
That's a better formula.
I'm still wondering which columns are from the CSV file, which ones are calculated, and if the columns other than VAT are always going to be whole numbers. It falls apart if they are not. I think at least one has to be guaranteed to be whole numbers but possibly it takes two or all three.
On a different note, the forum now sort our posts. The default seems to be "best" which jumbles them all up chronologically and makes no sense. Any way to set the default to "oldest"?
You can set them by Oldest in your Profile & Preferences.
What irritates me even more is that since the new voting system has been implemented the emails on the same discussion are not grouped by subject, probably because they have a different UID.
Badunit wrote:
the forum now sort our posts. The default seems to be "best" which jumbles them all up chronologically and makes no sense. Any way to set the default to "oldest"?
Can also choose order here under OP.
Struggling to adapt to the new system too. Hope it's better for somebody. So far just confusion for me.
SG
Thanks. I find the Apple forums software extremely frustrating and much harder use for searching posts or posting a new question than every other forum I belong to, but I won't rant on that in this thread. I set my preference to "oldest" and am biting my tongue.
How is there a problem? Does the file also contain the result of operations that you are trying to match?
Please post an example of the number and how they are used in a calculations
The VAT isn't 20% as part of the total is tax exempt. That's why I'm trying to breakdown the order into taxable and non-taxable from the export that spits out the total figure and the reduced VAT.
Perfect.
The Genius Award definitely goes to you!
Rounding Up, Down, All Around
