Excel rounding problem ~ help needed urgently 1

[OracleScorpion]

Hi,

When in Excel, you have a bunch of number with for example 5 digits after the comma. The numbers only show two numbers after the comma though and when a SUM has to be made, also the rounded up value has to be used and not the exact number.

Please don't say that we then have to type in just two digits, there are different calculations behind them which we can not alter, because they would mess up other things.

Can someone please help me?

 

[Loomah]

Hi
I'm not 100% sure what you are asking here but have you had a look at the
ROUND
ROUNDUP
ROUNDDOWN
CEILING &
FLOOR functions in help?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[theo1881]

also a little confused, are you saying you have figures such as 1.23456 and the cell is only showing 1.23 and that the sum of five of these for example should be as follows

1.23456
1.23456
1.23456
1.23456
1.23456
-------
6.1278

but you are getting

1.23
1.23
1.23
1.23
1.23
----
6.15

Laters, phat, headshape

 

[KenWright]

=SUM(ROUND(A1:A10,2)) entered using CTRL+SHIFT+ENTER

or

=SUMPRODUCT(ROUND(A1:A10,2)) entered normally

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[theo1881]

sorry top one should be 6.1728

Laters, phat, headshape

 

[KenWright]

Just for info, this is a common requirement when creating charts, as you would generally try and make sure that the bottom line number added up to the sum of the ones above, and when rounding is taking place, the visual maths don't always work.

Real Rounded 1dp
1.25 1.3
1.25 1.3
1.25 1.3
1.25 1.3
----- -----
5.00 5.0 << Looks wrong by 0.2

Trade off is that the data isn't 100% accurate, but for Chartmanship purposes, it will usually suffice, though you may end up adjusting an entry if it looks too far out of whack.

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[OracleScorpion]

Okay, thanks a bunch for the tips, I'm going to try them out. I have here a bunch of number to use as an example, because it is hard to explain.

Here you see what Excel gives us, because he counts with more than 2 numbers. But the client only sees the rounded numbers and as you can calculate yourself, the number showing should be 234.013,90 (instead of 89).

It may seem like an idiotic little thing, but when we send this to the banks, they start making problems about those 2 cents...



N1 N2 (N1/1000)*N2

127.350,00 480,00 61.128,00
188.480,00 480,00 90.470,40
64.395,00 417,00 26.852,72
65.090,00 417,00 27.142,53
451.115,00 63,00 28.420,25

SUM 234.013,89

 

[xlbo]

But the ,89 figure is actually more accurate is it not ??
Would you not be better off showing the other data to the appropriate amount of decimal places so that it matches up to the accurate calculation result ???

If not, go Tools>Options>Calculation and tick "Precision As Displayed

 

[OracleScorpion]

No, we have to show the numbers as rounded. Difficult to explain, has to do with the weights and whatever. You know, money

I'll try the Precision right away, but won't this affect the other numbers as well then?

 

[KenWright]

Did you read any of my notes? What part of those two formulas doesn't do exactly what you need?

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

If they want rounded numbers and to have the initial numbers visually add up, you will need to use a ROUND function on your (N1/1000)*N2 formula (or on any formula that the result is dependant on)

 

[OracleScorpion]

Ken : I tried them, but kept getting errors

 

[GlennUK]

Hi OracleScorpio,

you'd do better by telling Ken what the errors are exactly, instead of just saying "kept getting errors". I'm sure he's not psychic.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[OracleScorpion]

Eh... the error is literally "the formula contains an error". I can't be more specific because that is all Excel is tell me...

 

[GlennUK]

That's more information than before. Think about how many other types of error it could have been.

Can you post the formula that you're trying to enter please.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[OracleScorpion]

=SUM(ROUND(A1:A10,2))

But then of course with the range that I need.
I keeps flipping back to the range, as if it can't accept a range there...

 

[GlennUK]

You say ...

But then of course with the range that I need.

... why can't you post exactly what you are trying to enter?

So, what cell are you trying to enter this into? On the same sheet as the range being summed, or not? What key combination are you pressing when you are trying to enter the formula?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[OracleScorpion]

Well I am just trying it on different sheets, that's why at the moment it doesn't matter what cells I'm trying. Oh and they are on the same sheet. But what do you mean with key combination?

 

[KenWright]

Use the SUMPRODUCT syntax because you may not array enter the other one correctly, as you have to get the ocmbination right.

Forget the formula for a second, and in any empty cell type = and then go select the range you are trying to sum and hit enter. It will say #VALUE, however just copy and paste the formula that now appears in the formula bar and post it back here please.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[OracleScorpion]

=D39