# rounding issues

1. ## rounding issues

Ok, This is really confusing for me to try and explain but I will do it the best I can.

I have a formula that looks like this:

=IF(ISERROR((C101/G119)*G104)," ",((C101/G119)*G104))

There are about 15 cells with formulas similar to this. They are calculating a value for a row and at the end all of them should add up should equal C101.

The problem is the value that formula returns has to be two digits and I know it almost never is. But it ALWAYS equils C101. However because it only shows two digits somtimes if you manulally add up the value that you see it does not its off by +/-.01.

I need to fix it so that it does equal C101 AND if you were to add up the digits (with 2 decimal places) it will also equil C101.

The formula I have above is complicated as it is How would I make it to account for this error?

Tom

P.S. I am sorry if it is confusing.

2. Tom

One way would be to use the ROUND function to round the results of 14 of the 15 formulas to 2 places, and have the last cell be C101 - the sum of the 14 other cells.

HTH

rylo

3. That would work, unfortunately I forgot to mention that most days people will only do three out of the 15 tasks and it is impossible to know which tasks they will be doing.

Thank you,
Tom

4. Tom

Hmmm. Makes things more interesting.

Can you put up a sample sheet with some representative examples. May be able to work out the last filled item and use that as the balancing item.

rylo

5. Here ya go.

Sorry it took so long I have inserted comments to hopefully make it a bit easier. I have also only used 3 departments for now also but just remember that whatever solution is found has to work even if one of the departments entered is blank.

Thank you again for your help. I will be playing around with it some more.

Tom

6. I can tell if it is going to give an error if there is an odd number of odd numbers. (after multiplying by 100 of course).

=IF(ISODD(ROUND(B4/C11*C9,2)*100), ROUND(B4/C11*C9,2)+(IF(ROUND(B9, 1)>B9, -0.01, 0.01)), ROUND(B4/C11*C9,2))

I think I may need to add that formula to every single cell... I will spend then next 45 minutes seeing if that works lol.

After all is said and done that does not work. It makes it worse.

7. Bump for my frustration

8. Hi

I found the example file now too small. Can you update it to have about 10 rows and columns, and have items missing in a representative fashion.

Then show what you would expect to see where for each scenario, with descriptions where relevant.

rylo

9. hi Tom

Excel's accuracy is limited to 15 digits, calculations can be affected by this and the displayed results can appear to be different too...

One option is to save a copy of your workbook, open it in a new instance of Excel (ie have NO other files open - as the following may (?) affect other files too) & see if changing the "precision as displayed" option under Tools-Options in the copy of your file helps with the rounding difference (check out the Help files or Google for more details).

I'm not sure if the above will actually help you & I think that any adjustment should be made to B5, the "Hours Worked" cell, rather than the calculated cells. Cell B5 is hardcoded in the example file as 7.5 ie only one decimal place (dp), so there is always the potential for a difference. Where is cell B5 sourced from/can this cell be displayed as 2 dp?
If cell B5 can't be changed then I'd include a balancing cell or formula as Rylo suggested (although to an extent this defeats the purpose of providing a total for comparison/cross-casting).

Also, I realise this is just an example file but I would suggest maintaining a consistent layout for both of the tables in your actual file rather than having dept's using columns in one table & rows in the other. For example, have the "Tally Hours" & "Hours" in cells A5 & A6 with the dept results in the respective columns or even the other way around with the departments all going down the page with the other info listed across the columns. This will make maintainance of the formulae easier (b/c you'll be able to copy formulae all the way across/down with the use of absolute/relative referencing) & possibly improve readability as well.

hth
Rob

10. I am posting a larger example. If you manually add the values in Tally Hours it does not equil 7.5.

They NEED to equal 7.5 (or whatever the person enters there) because this is used on a time sheet so the total has to equal the amount of hours the person worked. Its ok if the values for each department is off by a little bit.

I am sorry if the layout here is a bit confusing but there are two different programs used to count how much work is done and they have different layouts thats why it is layed out the way it is, to make it easier for the user to copy the information.

And just remember that normaly there are only about 3 or 4 different departments processed but it is impossible to know which ones they will be.

Again thank you for your help.
Tom

11. In B8 and copy down,

=IF(C8=0, 0, ROUND(C8 * (\$B\$4 - SUM(\$B\$7:B7) ) / (\$C\$23 - SUM(\$C\$7:C7)), 2) )

If hours were recorded instead in tenths, then

=IF(C8=0, 0, ROUND(C8 * (\$B\$4 - SUM(\$B\$7:B7) ) / (\$C\$23 - SUM(\$C\$7:C7)), 1) )

12. Hi

Another option

B8: =IF(COUNTIF(\$C\$8:C8,"<>0")=COUNTIF(\$C\$8:\$C\$22,"<>0"),\$B\$4-SUM(\$B\$8:B8),ROUND(B4/C23*C8,2))
C8: =IF(COUNTIF(\$C\$8:C9,"<>0")=COUNTIF(\$C\$8:\$C\$22,"<>0"),\$B\$4-SUM(\$B\$8:B8),ROUND(\$B\$4/\$C\$23*C9,2))

Copy down from C8 as required.

rylo

13. Got it working! Thank you all for your help this problem has beein a PITA.

Tom

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1