Set Precision as displayed on or off

[john drake]

Hi everyone as my Excel skills are not great I would appreciate some help with
a spreadsheet I set up to show me the net cost and vat of supplies.
The trouble was by line 6 I noticed that the totals were out by .01 after a lot of searching I found that by setting Set Precision as displayed on
the totals were correct but I received a warning data will permanently lose accuracy, so I am not sure when should I be using this option and is this the correct approach to solve it my problem?

John.

 

[MrKowz]

I wouldn't reccomend that option. Instead, consider using ROUND on your formulas.

 

[john drake]

Many thanks for your reply and I have now put rounding in my formula but it is still not correct.
Please could you be kind enough to tell me what I am doing wrong.
I am taking the gross value in cell A and finding what the net cost is before 17.5%tax (cell B) Cell C is the 17.5% tax, but in this example it is wrong.
Cell A B C
Gross Net TAX
80 68.09 11.92
Cell B1 is =ROUND(IF(A1<> 0,A1/117.5*100,0),2)
Cell C1 is =ROUND(IF(B1<> 0,B1*17.5/100,0),2) I know if I change the rounding on c1 to1 it would be correct but the other calculation in the other columns would then be out.
I would be grateful if you could assist me.

John

 

[MrKowz]

Why not just do it this way?

Excel Workbook
	A	B	C
1	Gross	Net	Tax
2	80	68.09	11.91
Sheet3

Excel 2003

Cell Formulas
Range	Formula
B2	=ROUND(A2/(1+0.175),2)
C2	=A2-B2

 

[john drake]

Thank you for the response, I have simplified the formula as you recommended but as there is a large amount of data in the spread sheet I check the totals at the end of the sheet by doing a Sum on the column,if you then take the gross total figure and use the formula to work out the net figure as before it is different. see example below, both arrive at the same Gross figure but give a different Net and Tax amount picture, this is only a small example from the sheet.I would be grateful if you could shed some light on why totalling via SUM would differ from the formula result and how do I get them to agree. the formula I am using is as you advised.
Regards John.

<table style="border-collapse: collapse; width: 342pt;" border="0" cellpadding="0" cellspacing="0" width="456"><col style="width: 86pt;" width="114"> <col style="width: 41pt;" width="55"> <col style="width: 42pt;" width="56"> <col style="width: 45pt;" width="60"> <col style="width: 66pt;" width="88"> <col style="width: 62pt;" width="83"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt; width: 86pt;" width="114" height="17"> </td> <td class="xl71" style="border-left: medium none; width: 41pt;" width="55">Gross</td> <td class="xl71" style="border-left: medium none; width: 42pt;" width="56"> </td> <td class="xl71" style="border-left: medium none; width: 45pt;" width="60">Net</td> <td class="xl71" style="border-left: medium none; width: 66pt;" width="88"> </td> <td class="xl71" style="border-left: medium none; width: 62pt;" width="83">Tax</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl71" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">C</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl72" style="border-top: medium none; border-left: medium none;">7600</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">6468.09</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">1131.91</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl72" style="border-top: medium none; border-left: medium none;">65</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">55.32</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">9.68</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl72" style="border-top: medium none; border-left: medium none;">55</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">46.81</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">8.19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl75" style="border-top: medium none; height: 12.75pt;" height="17">Sum Totals</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">7720</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">6570.22</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">1149.78</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="border-top: medium none; height: 12.75pt;" height="17"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl75" style="border-top: medium none; height: 12.75pt;" height="17">Formula</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">7720</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74" style="border-top: medium none; border-left: medium none;">6570.21</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;">1149.79

</td> </tr> </tbody></table>

 

[MrKowz]

Again, you come across rounding issues.

Take for example the dataset:

Excel Workbook
	A
1	24.0049
2	24.0049
3	24.0049
4	24.0049
5	24.0049
6	24.0049
7	24.0049
8	24.0049
9	24.0049
10	24.0049
Sheet1

#VALUE!

Excel 2003

Cell Formulas
Range	Formula
D3	=B3/1.175
D4	=B4/1.175
D5	=B5/1.175
D7	=SUM(D3:D5)
D9	=B9/1.175
F3	=B3-D3
F4	=B4-D4
F5	=B5-D5
F7	=SUM(F3:F5)
F9	=B9-D9

Then, if you just format the cells to show only two decimal places, you can make it look like:

Excel Workbook
	A	B	C	D	E	F
1		Gross		Net		Tax
2		A		B		C
3		7600.00		6468.09		1131.91
4		65.00		55.32		9.68
5		55.00		46.81		8.19
6
7	Sum Totals	7720.00		6570.21		1149.79
8
9	Formula	7720.00		6570.21		1149.79
10
Sheet1

Excel 2003

[/B]

 

[john drake]

Thank you very much for taking the time to explain where I was going wrong and providing a clear explanation with the example.
I have now set my formulas as advised and will stick to this method in future.

Thank you.

 

[wut]