Round off Issues and more...

[parttime_guy]

Dear All,

Problems in round off issues...

Attached is the file with a proposed round off solution. I am not confident in using this solution in the Office - Kindly test/review the same.

Your feedback would be of great value.

Thanks & Best regards

[Bob Phillips]

How about

=ROUND(B15+0.05,0)

[parttime_guy]

Dear XLD,

Thx for showing interest in the post. The figures keep changing (and I don't know which cell could have the max decimal). The totals could vary too. Currently there could be 4 rows with a sub-total (it could increase to 5 or more). Any Ideas.

I have attached below 3 senarios with 5 rows - kindly review.

Thx-n-BR

[Bob Phillips]

I am not sure what you are trying to achieve. If your problem is that when you round the numbers it doesn't always add up to the same as you started with, well, yes, that can happen. If you want to avoid it, y0u have to determine what strategy you want to enforce with the difference, and apply it.

[parttime_guy]

Dear XLD,

UR right, but I was trying to automate the process in case to round off does not match - hence the working.

Problem is the boss does not want decimals - any ideas.

In the solutions - I am using the figures with MAX decimals and adding 1 to it automatically - the results do match with the Org. totals.

I am doing it right - or is there another way of solving this decimal issue.

[Bob Phillips]

I am afraid very little of that enlightened me in any way.

If he doesn't want decimals, he needs to set a rule to deal with the decimals, I can't say you are doing it right, it is a business problem not a technical one.

[parttime_guy]

Dear XLD,

This is my last post xld (will not bother u anymore with this decimal issue), I have modified the Excel Sheet (attached below) - but I have hit a big brick wall .

Kindly review one last time

Need ur final comments

....and I give up!!!

Thx-n-BR

[Bob Phillips]

You are not bothering me, but you are not helping me either.

I have no idea what your objective is, other than some vague notion of rounding, and an even vaguer notion of not having any decimals. I think you want to round all of the amounts to whole numbers in some way, but still come up with an overall total the same as you started with. This is not possible if the starting total is not a whole number, and even if it is, as I said, the roudning to be applied is a business problem, that needs a business rule. Once you decide upon that, implementing it will be a doddle.

[Paul_Hossler]

I was confused by the data in your example. Can you post a VERY simple example maybe, with out a lot of unneeded data?

Not sure of the reason behind the request, but I doubt if there's a reliable way to have the SUM of ROUNDed numbers = the SUM of the original numbers

Example:

Ten numbers all = 1.4.

The real sum = 14.0

Each of the 10 1.4's ROUNDs to 1.00, so the sum of the ROUNDed numbers = 10.0

Paul

[parttime_guy]

Thx XLD for ur patience, I take ur point maybe have a discussion with the Boss in length.

Best regards