Simplify this formula

[Sampoerna]

Hi,
I'm working on the value of cent which return to the value of 0 or 5 cents.
e.g. $0.03 = $0.05 or $0.08 = $0.10 etc. So use the following formula that
works for me.

B5 = $1.02
C5 =
IF(VALUE(RIGHT(TEXT(TRUNC(B5,2),"0.00"),1))>5,TRUNC(B5,1)+0.1,IF(VALUE(RIGHT(TEXT(TRUNC(B5,2),"0.00"),1))>0,TRUNC(B5,1)+0.05,B5))
which is equal to $1.05

Can anyone help to make it shorter?

Thanks

 

[joeu2004]

I'm working on the value of cent which return to
the value of 0 or 5 cents.
[....]
=IF(VALUE(RIGHT(TEXT(TRUNC(B5,2),"0.00"),1))>5,
TRUNC(B5,1)+0.1,
IF(VALUE(RIGH­T(TEXT(TRUNC(B5,2),"0.00"),1))>0,
TRUNC(B5,1)+0.05,B5))
[....]
Can anyone help to make it shorter?

First, I don't believe your formula does exactly what you expect.
Consider the case where B5 is 1.005. Your formula will return 1.005,
not 1.00. I think the last part of your formula should be TRUNC
(B5,2), not simply B5.

Second, I believe the following does the same thing (with my
"correction"):

=ceiling(trunc(B5,2),0.05)

I use TRUNC, as your original formula. If you have thought about it,
and that is truly what you want, fine.

But more commonly, people with your requirement have formatted the
cell with 2 decimal places, and they want the number that they __see__
(due to Excel rounding) to round up to the nearest multiple of 0.05
(for example). Thus, 1.005 would __appear__ to be 1.01, and they
expect the rounded-up value to be 1.05, not 1.00. If, on second
thought, you also prefer the latter, the following might be what you
want:

=ceiling(round(B5,2),0.05)

Lastly, you might consider what result you wish if the value in B5 is
1.001. If you want that to be 1.05, you might prefer simply:

=ceiling(B5,0.05)

HTH.


----- original posting -----

 

[Mike H]

Hi,

Try this

=ROUNDUP(B4/0.05,0)*0.05

Mike

 

[muddan madhu]

=IF(RIGHT(TRUNC(B5,2),1)>"5",TRUNC(B5,1)+0.1,IF(RIGHT(TRUNC(B5,2),1)

 

[Sampoerna]

Yes my formula does works! I'm using Excel 2003.
=IF(VALUE(RIGHT(TEXT(TRUNC(B5,2),"0.00"),1))>5,TRUNC(B5,1)+0.1,IF(VALUE(RIGHT(TEXT(TRUNC(B5,2),"0.00"),1))>0,TRUNC(B5,1)+0.05,B5))

But......! Your formulas are far so cute and I love it very much..

=ROUNDUP(B5/0.05,0)*0.05
=CEILING(TRUNC(B5,2),0.05)
=CEILING(ROUND(B5,2),0.05)
=CEILING(B5,0.05)

Thanks to both of you. Cheer!

 

[joeu2004]

Yes my formula does works! [....]
=IF([...],IF(VALUE(RIG­HT(TEXT(TRUNC(B5,2),"0.00"),1))>0,TRUNC(B5,1)+0.05,B5))

If your formula behaves as you want when TRUNC(B5,2) is of the form
x.x0, then none of the roundup/ceiling solutions truly works for you.
But I suspect you are simply not grasping the subtlety of the
situation.

(And if you did, I suspect you would want the roundup/ceiling solution
anyway.)

I suspect you enter and display values with just 2 decimal places, and
you think that exactly matches the underlying value in the cell.

Well, that is almost never the case. The only numbers of the form
x.x0 that exactly match the underlying value are x.00 and x.50
(assuming that "x" is no more than 15 and 14 significant digits
respectively). But, for example, if you enter 1.10 into a cell, the
actual value is
1.10000000000000,0088817841970012523233890533447265625. (The comma
demarcates the end of 15 significant digits to the left.)

If you only use your formula with B5 equal to a constant that you
entered, returning B5 instead of TRUNC(B5,2) makes no difference.

But if B5 is the result of some formula, the little differences
between internal and displayed values can cause significant
differences in the results of the formula -- large enough differences
so that, for example, a comparison with the constant 1.10 might return
false, even though 1.10 is displayed in the cell.

In such cases, I presume that you would prefer TRUNC(B5,2) so that
WYSIWYG. For example, a comparison with the constant 1.10 would
always return true if B5 appears to be 1.10 because both TRUNC(B5,2)
and the constant 1.10 would have the same (inexact) internal
representation.

Note: Sometimes Excel returns true for comparisons even if two
operands does not have the same internal represenation. But the Excel
heuristic to make that work is necessarily "inconsistent" because it
depends on just how different the internal representations are. There
is no right answer that will fit all applications (read: all personal
tastes).

HTH.


----- original posting -----

 

[Sampoerna]

Hi,

I guest that was a matter of limitations in formulas. And of course not all
formulas can handle the threat beyond the limit. In the first place I'm 100%
agreed with you. So I would like to hear more suggestions and ideas to deal
with the case when we jump to a matter of Accounting.

Thank you.

 

[joeu2004]

So I would like to hear more suggestions and ideas
to deal with the case when we jump to a matter of
Accounting.

I confess that I do not entirely understand you. But you might be
asking: if you simply have an accounting spreadsheet -- translation:
everything is entered in dollars and cents (or the local currency with
2 decimal places) -- do you need to worry about numbers with decimal
fractions beyond 2 decimal places?

The answer is "yes". The situation arises in two ways:

1. Many accounting spreadsheet have formulas that multiply or divide;
often, that creates longer decimal fractions. No surprise there. But
many people mistakenly think they solve the problem by formatting the
cell with 2 decimal places. Generally, that affects only the
__appearance__ of numbers, not the actual value in the cell.

2. Even accounting spreadsheets with formulas that only add or
subtract encounter numerical "errors" because of the way that Excel
(and most applications) represent numbers internally, namely using a
standard form called "binary floating pointing". This creates
arithmetic problems, one of which I explained by example in my
previous response. Note: I hasten to point out that the term
"numerical error" is mathmetician jargon. These anomalies are not
computational mistakes or defects. "Numerical artifact" might be a
better term to use.

The remedy for both is the same; there are several possible remedies,
actually. I prefer the prolific use of ROUND or related functions
(like TRUNC), depending on your requirements. Alternatively, you
might set the "Precision as displayed" option under Tools > Options >
Calculations. But that can have unintended consequences if you are
not careful with cell formats. Moreover, it does not completely
eliminate the need to use ROUND et al in some circumstances. It is
important to note that "Precision as displayed" actually means
"Precision of result as formatted". It does not affect intermediate
computation, notably computation of expressions in IF() comparisons.

For more information, the following links might be helpful, posted by
others. (But I suspect they are "over the top" for most people.)

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html

HTH.


----- original posting -----

 

[Sampoerna]

Thank joeu2004,

That was outstanding and brief explainations. .

Right now, I still can be happy as long as I deal with 15 digits.
Sure I have a lot of homework to do beyond this case.

So, the real problem is not only the formula, but the excel application
itself cannot store the exact number when it exceeded more than 15 digits.

I mean what the of formula when the cell cannot stored the number
accurately?

Thanks for any suggestion or idea.