MS-Excel Formula

[Guest]

How do I get a cell to round up by $1.00 without changing the sum total? I
know how to make it round up by one or two places. Example: Cell (A1=
$1,333)+(A2=$1,333)+(A3=1,333). Cell A4 is a sumtotal which equals $4000.
When I sum these cells, the total is 4000. However, If you manually add
these amounts you will see that the correct total really is 3999. I need to
make cell A1 equal $1334 without manually adding 1 at the end of the formula,
which ultimately affects the sum total cell. Does anybody have any
suggestions?

 

[Guest]

I'm guessing he values in A1, A2 and A3 are 1333.33. DO you want it to show
3999? IF so, go to Tools -> Options -> Calculations -> PRecision as
displayed? Please note that if you do this, your data will lose accuracy.

 

[Guest]

I'm guessing that Barb is correct in assuming that your values are 1,333.33.
Also that you're getting the normal behavior from the spreadsheet because of
the precision that you choose to display. If you want your total to appear
as 4,000 and 1 of your 3 values to appear as 1,334, then you could do the
following.

Change one value to 1,333.5. This should make it round up in your display,
but wouldn't be enough of a change to affect the way your total appears.

However, let me add that I don't think this is a good idea -- it's liable to
create confusion down the road. I'd suggest you either add decimal points to
your precision, or actually change all of the values to integers.

 

[Guest]

Yes the values are 1,333 in A1,A2, and A3. I need the result cell to show
4,000. Which it does. However, I need cell A1 to show 1,334 w/o changing
the 4,000 to 4,001.

Thanks - muzickdoc2006

 

[Fred Smith]

Why?

You must have 1,333.33 in each of A1, A2 and A3, which adds up to 4,000. But the
format for each cell is 0 decimal places, so they display 1,333.

The standard solution to this issue is to add a footnote saying "Numbers may not
add up due to rounding".

Another option is to increase the number of decimal places displayed.

You won't be able to get 1,334 into A1 without a circular reference error.

You could create a helper column, where B2=round(A2,0), B3=round(A3,0),
B4=round(A4,0), and B1=B4-B3-B2

That's all the solutions I can think of.