Help with rounddown excel formula

I have this formula =(K10+L10-O10)*$P$77 the answer is 0.758

I want to use rounddown (I think) so that the answer is 0.75

I tried formatting the cell to 2 x decimal points but that takes it to 0.76

Anyone?

You could truncate the number using Excel's TRUNC fucntion.

I think you just want =ROUNDDOWN((K10+L10-O10)*$P$77, 2)

works in LibreOffice anyway and seems to be as defined for excel

Don't forget

salami += answer - rounded_down_answer;

That comes up with error message "too few arguments"

When I use. (K10+L10-O10)*$P$77+TRUNC(0.758,2) It returns 1.508

I think that?s because you?ve not only calculated your answer, but then added on the truncated form of 0.758. (0.758 + 0.75 = 1.508)

At a guess I would expect something more like =TRUNC((K10+L10-O10)*$P$77),2. (To be honest , I don?t do excel any more and the last 2 might be meant to be inside he last bracket).

I?m no expert but I can?t see why you?ve typed in the answer (0.758) that your sheet should be calculating.

Tim

Eliminate refs to cells we can't see. Paste in a cell

=ROUNDDOWN(0.199,2)

and report the result.

TRUNC((K10+L10-O10)*$P$77,2) that worked

Thats what it was calculating

ok so that rounded .758 to .75, which unfortunately didnt solve my problem. It has to do with my gas bill/statement, it is 1p out. All my figures on spreadsheet match the statement figures but for some reason excel is adding an extra 1p and I am damned if I can find it.

See attached, I`ll get it eventually, if I auto sum the 3 figures concerned in a separate cell it gives me the £15.90 (correct answer) but in the spreadshhet it returns £15.91 (the CV figure is correct) I just cant work out where it is picking up the extra 1p.

Maybe at the other end they are calculating to more significant figures than they are presenting in your bill. In which case "your" calculation might well be out by 1p.

If you've formatted all your cells to 2 decimal places, then sum those cells, the answer will include the extra decimal places you've hidden

A penny? And I thought it was for something important. ;-)

Life is way too short to worry about stuff like that.

Tim

The penny is not important but getting the spreadsheet to work properly is or more to the point understanding why it doesnt. :-)

I dont quite understand your reply.

Perhaps you typed a dot instead of the comma. You will have to give more details about your spreadsheet if you expect others to help you.

Are the three figures on the right of your screenshot of the sheet typed in, or are they calculated from elsewhere? Assumed column letters don't tie up with your posted formula. What is in cell $P$77? If it makes 0.758 from 15.90 it must be 0.0047672955

ok the problem is that they are calculating the results from multiplying the number of kWh by the price each, then the number of days times the standing charge per day. Then working out the VAT and adding it all up.

However they are also formatting the output at each stage to a currency

- which means the figures they show (and the ones you are using in your sheet) are not accurate representations of the intermediate values. So each time you take a result figure from the bill you are including a small rounding error into your source data. Once you add them all together you get a different bottom line.

If I recreate a bit of the calc, but do the sum in they way they do I get:

Item Qty Price Total

Gas 409 3.004 12.28636 SC 30 0.0952 2.856 VAT @ 5% 0.757118 Total 15.899478 Formatted £15.90

If however you add 12.29 + 2.86 + 0.76, you will get 15.91

Both your spreadsheet and the bill only show values to 2 decimal places.

Whereas the figures on which these calculations are based, are to 3 or more decimal places.

And so they could show a different final result, if rounding up is done at different stages of the calculation

Possibly for accounting purposes the utilities may need to calculate bill totals to 2 decimal places before applying VAT. If VAT is then applied to this figure and the total again rounded up to 2 decimal places then this may give a different result than if the rounding up is done only once on the final total; as it is in your spreadsheet.

michael adams

...

HMRC allows supplier to calculate, round and then total the VAT per line of an invoice, rather than calculate the net total, and then add VAT and round it.

eg. you might have calc VAT separately on standing charge and the energy supplied.