Make last digit always display the same value.
 

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/comm...lic.excel.misc

Make last digit always display the same value.
 

Try this:

=IF(SUM(A1:A10),CEILING(SUM(A1:A10)+0.01,0.1)-0.01,"")

Biff

"Dave Plyer" wrote in message
...
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/comm...lic.excel.misc

Make last digit always display the same value.
 

On Fri, 13 Apr 2007 17:50:03 -0700, Dave Plyer
wrote:

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/comm...lic.excel.misc

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

Make last digit always display the same value.
 

On Fri, 13 Apr 2007 21:35:04 -0400, "T. Valko" wrote:

=IF(SUM(A1:A10),CEILING(SUM(A1:A10)+0.01,0.1)-0.01,"")

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

Make last digit always display the same value.
 

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.

Yes, I know!

Biff

"Ron Rosenfeld" wrote in message
...
On Fri, 13 Apr 2007 21:35:04 -0400, "T. Valko"
wrote:

=IF(SUM(A1:A10),CEILING(SUM(A1:A10)+0.01,0.1)-0.01,"")

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

Make last digit always display the same value.
 

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.

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/