Decimal places in Excel & calculations

[mudstuffin]

I have an Excel spreadsheet that has various sum calculations of monetary values. My problem is that it seems to be rounding the figure incorrectly, making it 1 pence out….! I have changed the formatting to 2 decimal places but this doesn’t seem to stop it. How can I correct it. Here is an example of what it does....

Value entered into cell by user: £ 266.90
Cell to right calculates 15% of above:- £ 40.04
Cell to right of that deducts above from entered value:- i.e £ 266.90-£ 40.04 = £226.87. This should be £226.86…!

Incidentally, I have a similar problem in another spreadsheet, with calculating values of text boxes in a userform. The textbox will not seem to format to two decimal places when the calculated value is 8.235. Again, when calculated with another value, it rounds up to give a figure that is 1 pence out. I am formatiing the textbox like this:- A = Format(A, "£ #,##.00&quot

Am I missing something here…?

I am sure the solution is simple, but it's one that I'm missing.

Thanks in anticipation.


mudstuffin

 

[xlbo]

The answer to your 1st query (and quite possibly your second)is that you have only FORMATTED the number to 2dp - the actual number behind this is 40.035 - you can see this if you look in your formula bar.
This is a very important point about excel (and many other apps)

Formatting does not change the actual number, it merely shows you the number in the format you have specified. When doing a calculation, it will still use the WHOLE number to as many dps as there are.

So, anyway, to get round it, you need to convert to a 2dp number like this
Say 266.90 is entered into A1
Presumably, you have something like = .15*A1 in B1
This should read = text(.15*A1,"0.00&quot
This will get rid of the extra decimal places - but it'll be text as opposed to a number. Because it is all numeric, excel can still do calculations with it. If you want an actual "number" as opposed to text that looks like a number, use:
=value(text(.15*A1,"0.00&quot)

HTH
Geoff

 

[mudstuffin]

Thanks xlbo.

This certainly helps, and it was on the lines of what I was thinking of, but I couldn't figure out how to do it.

It works with the cells in the spreadsheet. How can I implement this with the text boxes in the userform...?

Thanks again.

 

[larryww]

Consider
=Round(foo,2)

Available wherever fine Excel OR Access products are sold LOL

 

[xlbo]

Hi, to solve this I need to know the calc to get the value into the textbox 'cos the formatting / converting to a 2dp value will have to be done at the right point to stop dp errors but you could try :
A = Format(value(format(A,"0.00&quot), "£ #,##.00&quot

for now
HTH
Geoff

 

[Loomah]

Beware, I'm sure there is some really irritating 'feature' about the round function, in VB6 it rounds 5 down. This is reasonably predictable. But try these in the immediate window in the xl vbe just to see what happens.

?round(41.035,2)
?round(40.035,2)

I can't remember where I read about this and I don't know if Microsoft have fixed this particular 'feature' but depending on how many calculations you have and how important it is to be accurate to the penny this could have some effect on what you are doing. Sorry I don't have a solution to this feature but I'm sure there's one out there, if not on this site try

http://www.mvps.org

Not entirely related but worth sharing!!