Having trouble with rounding percentages

[rizzo93]

My table consists of several columns. Columns A-D are one group (Group 1). Columns E-H are another group (Group 2).

At the bottom of each column is a formula that:

• sums the values in each column above it
• and then divides that sum by the total number of projects to get a percentage

Problem 1
When I select the bottom cells in Group 1, they do not add up to 100%. Same thing for Group 2.

I figure this has something to do with rounding, but I'm not having success using the ROUND, ROUNDUP and ROUNDDOWN functions. For example:

=ROUNDDOWN(SUM(J5:J14)/$L$15,2)

($L$15 is the number of projects)

But if I apply that same formula to the other cells (referencing the appropriate cells), then it totally throws off my numbers. For instance, one total is literally a 1, but the formula makes it a 0.

Problem 2
I'm sure this is related to the issue above. The totals for Group 2 are used to make a pie chart. One of the slices says 45%, but the corresponding cell in my data is showing 46%.

I'm guessing that if the columns are properly handled with rounding that the numbers in the pie chart will follow suit. I also see a format code in the data label formatting panel, but if that's the way to go, I don't know what the format should be.

Any suggestions are welcome.

 

[SpillerBD]

You might be better off with Bankers Rounding. Regular round has an upward bias. ROUNDUP has a definite upward bias, and ROUNDDOWN has an obvious downward bias.
Access's & VBA's ROUND function works differently than Excel's worksheet function of the same name just to be confusing.

Add this function to your Personal Macro Workbook

 Public Function BRound(Val1 As Long, RoundVal As Integer) As Long
 BRound = Round(Val1, RoundVal)
 
 End Function

 

[rizzo93]

Thanks for the assist, SpillerBD.

I have only a little experience with adding script, but if I understood correctly, I went into the developer window and inserted a module with the code you provided. I don't, however, see it show up in my macros list. So I must have missed something.

But if the premise is to run that macro every time I want to round something, then I'm that would rely on me or some other user to know to execute that macro, correct?