Make last digit always display the same value.

D

Dave Plyer

I add a series of prices. Regardless of the last digit in the sum, I want to
make the digit a 9. For example, the calculated sum of prices is $5.00. I
want $5.09 displayed. If the sum is $5.87, I want $5.89 displayed. Help,
please.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...b-3a752d6cbdb0&dg=microsoft.public.excel.misc
 
R

Ron Rosenfeld

I add a series of prices. Regardless of the last digit in the sum, I want to
make the digit a 9. For example, the calculated sum of prices is $5.00. I
want $5.09 displayed. If the sum is $5.87, I want $5.89 displayed. Help,
please.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...b-3a752d6cbdb0&dg=microsoft.public.excel.misc
Click to expand...

There are several unspecified parameters in your request.

If there will always be at least one value, and if the SUM will always be a
positive number, then a simple function would be:

=INT(SUM(A1:A10)*10)/10+0.09

If there are no entries, then the original formula will give $0.09,

=IF(COUNT(rng)=0,"",INT(SUM(rng)*10)/10+0.09)
will give a blank cell, or:

=IF(COUNT(rng)=0,0,INT(SUM(rng)*10)/10+0.09)
will give a zero.

If the SUM might be negative, then:

=INT(SUM(rng)*10)/10+0.09*SIGN(SUM(rng))

will not only always end with a "9", but it will also display a 0 if there are
no entries (or if the sum is zero). If that's not what you want, you might
need to wrap it in one of the IF statements.

--ron
 
R

Ron Rosenfeld

=IF(SUM(A1:A10),CEILING(SUM(A1:A10)+0.01,0.1)-0.01,"")
Click to expand...

I would point out that your function will return a NULL string if there are no
entries in the range; and will also return a NULL string if the SUM of the
entries is zero.

That may or may not be appropriate for this poster.
--ron
 
T

T. Valko

I would point out that your function will return a NULL string if there
are no
entries in the range; and will also return a NULL string if the SUM of the
entries is zero.
Click to expand...

Yes, I know!

Biff
 
S

Stan Brown

Fri, 13 Apr 2007 17:50:03 -0700 from Dave Plyer
I add a series of prices. Regardless of the last digit in the sum, I want to
make the digit a 9. For example, the calculated sum of prices is $5.00. I
want $5.09 displayed. If the sum is $5.87, I want $5.89 displayed. Help,
please.
Click to expand...

Assuming that the sum is in A20, I believe this formula would work:
=FLOOR(A20,.1)+.09

The FLOOR function chops off any pennies or nickels, and the +.09
adds the ending 9 that you want.
 
Top