Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.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 |
#3
Posted to microsoft.public.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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display entire 19 digit number | Excel Discussion (Misc queries) | |||
Display a 16 digit number | Excel Discussion (Misc queries) | |||
Display a digit before or after the decimal point in another cell | Excel Worksheet Functions | |||
How do you display 16 digit numbers in excel ? (it keeps convert. | Excel Discussion (Misc queries) | |||
When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) |