Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Need a bit of help please.
Have a spreadsheet showing rents charged out. For example in column 1 annual rent, column 2 annual rent divided by 12, column 3 VAT is added at 17.5% and in column 3 the monthly rent is added to the VAT to give a total. Example £6,400.00 / 12 months = £533.33 VAT is £93.33 Total = £626.66 But in my total column I get £626.67 and can't seem to get rid of that extra penny. Is there any way to force the calculations to work to the nearest penny as £6,400.00 divided by 12 is actually £533.33333. What I want is it to disregard anything after the two decimal places. Hope that all makes sense. Thanks for any help in advance. Ash |
#2
![]() |
|||
|
|||
![]()
The total is 626.6667 and rounded it is 66.67, if you want to round down to
the nearest penny =FLOOR((6400/12)*1.175,0.01) -- Regards, Peo Sjoblom "Ash" wrote in message ... Need a bit of help please. Have a spreadsheet showing rents charged out. For example in column 1 annual rent, column 2 annual rent divided by 12, column 3 VAT is added at 17.5% and in column 3 the monthly rent is added to the VAT to give a total. Example £6,400.00 / 12 months = £533.33 VAT is £93.33 Total = £626.66 But in my total column I get £626.67 and can't seem to get rid of that extra penny. Is there any way to force the calculations to work to the nearest penny as £6,400.00 divided by 12 is actually £533.33333. What I want is it to disregard anything after the two decimal places. Hope that all makes sense. Thanks for any help in advance. Ash |
#3
![]() |
|||
|
|||
![]()
Ash
Looks like you need to round each result before you sum them. Use in b2 =round(A2/12,2) and in c2 =round(B2*0.175,2) before you add them together in d2 Regards Bill K "Peo Sjoblom" wrote in message ... The total is 626.6667 and rounded it is 66.67, if you want to round down to the nearest penny =FLOOR((6400/12)*1.175,0.01) -- Regards, Peo Sjoblom "Ash" wrote in message ... Need a bit of help please. Have a spreadsheet showing rents charged out. For example in column 1 annual rent, column 2 annual rent divided by 12, column 3 VAT is added at 17.5% and in column 3 the monthly rent is added to the VAT to give a total. Example £6,400.00 / 12 months = £533.33 VAT is £93.33 Total = £626.66 But in my total column I get £626.67 and can't seem to get rid of that extra penny. Is there any way to force the calculations to work to the nearest penny as £6,400.00 divided by 12 is actually £533.33333. What I want is it to disregard anything after the two decimal places. Hope that all makes sense. Thanks for any help in advance. Ash |
#4
![]() |
|||
|
|||
![]() "Bill Kuunders" wrote in message ... Ash Looks like you need to round each result before you sum them. Use in b2 =round(A2/12,2) and in c2 =round(B2*0.175,2) before you add them together in d2 Regards Bill K Thanks to both who replied. The Round solution seems to be working fine now. Ash |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Rounding up for down based on cell value | Excel Worksheet Functions | |||
Rounding Issues | Excel Discussion (Misc queries) | |||
Format a worksheet to carry out all calculations to 2 decimal plac | Excel Discussion (Misc queries) | |||
Format a worksheet to carry out all calculations to 2 decimal plac | Excel Discussion (Misc queries) | |||
specific rounding of formulas which may need to include an IF stat | Excel Worksheet Functions |