Feedback by UserVoice

JoeU

My feedback

  1. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    JoeU shared this idea  · 
  2. 5 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    JoeU shared this idea  · 
  3. 5 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    JoeU commented  · 

    @MSFT: My own Newton-Raphson implementation often works when Excel (X)IRR does not. Often, my implementation does not require a "guess" when Excel (X)IRR does. My implementation calculates the exact derivative. I suspect Excel (X)IRR approximates the derivative, perhaps using the difference quotient. I suggest that Excel use the exact derivative.

    An error occurred while saving the comment
    JoeU commented  · 

    I think it would be helpful to MSFT if you provided a specific example. You provided one in answers.microsoft.com; but I demonstrated that could be remedied by using a closer "guess". You indicated there is another (?) example where a "guess" with 5 percentage decimal places did not work. I suggest that you provide that example here.

    JoeU supported this idea  · 
  4. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    JoeU shared this idea  · 
  5. 4 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    JoeU commented  · 

    While a semi-average trendline might be helpful to some people, MSFT is not likely to add the feature in time for you to use for your school project, unless your project will span several years. :-) Also, you might misunderstand the assignment. Semi-average and (linear) regression are two different methods for constructing a trendline. The assignment might want you to compare the two. In any case, I suggest that you post your inquiry to answers.microsoft.com in order to get some help with the mechanics of a semi-average trendline. I could do that here; but I don't believe this is the right forum for that.

  6. 20 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    JoeU commented  · 

    Errata.... --TEXT(A1,"0."&REPT("0",B1-1)&"E+0") rounds to B1 significant digits.

    An error occurred while saving the comment
    JoeU commented  · 

    Sorry for the late comment.... The built-in function to do this is TEXT with a format of the form "0.0***0E+0", where "***" is metasyntax for the ellipsis. For example, --TEXT(A1,"0E+0") rounds to 1 significant digit, and --TEXT(A1,"0.00000000000000E+0") rounds to 15 significant digits (14 zeros after the decimal point). More generally, --TEXT(A1,"0."&REPT("0",B1)&"E+0") rounds to B1 significant zeros. The double negate ("--") converts text to numeric. Arguably, users might prefer a new built-in that is easier to use, e.g. ROUND.SIG. That can even be expanded to included ROUNDUP.SIG and ROUNDDOWN.SIG.

  7. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    JoeU shared this idea  · 
  8. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    JoeU commented  · 

    I disagree with the suggestion. Generally, calculations should be rounded to the precision required, not to some arbitrary precision. Rounding to 12 decimal places does not hide rounding errors, in general. Two counter-examples....

    Example #1:

    A1: 34639.969
    A2: 5.273
    A3: =ROUND(A1-A2,12)
    A4: =ROUND(A1-A2,3)
    A5: =A3-34634.696=0
    A6: =A4-34634.696=0

    A6 returns TRUE. But A5 returns FALSE because A3 is actually 34634.6959999999. (That is, the binary representaton of that constant.)

    Example #2:

    A1: 8234.558
    A2: 8234
    A3: =ROUND(A1-A2,12)
    A4: =ROUND(A1-A2,3)
    A5: =A3-0.558=0
    A6: =A4-0.558=0

    Again, A6 returns TRUE. But A5 returns FALSE because A3 is actually 0.558000000001000. (Again, the binary representation of that constant.)

    Note: Ostensibly, "Precision as displayed" rounds to the "precision required" based on cell format. But PAD is fraught with too many problems to be useful, in general.

    An error occurred while saving the comment
    JoeU commented  · 

    I disagree with the suggestion. Generally, calculations should be rounded to the precision required, not to some arbitrary precision. Rounding to 12 decimal places does not hide rounding errors, in general. Two counter-examples....

    Example #1:

    A1: 34639.969
    A2: 5.273
    A3: =ROUND(A1-A2,12)
    A4: =ROUND(A1-A2,3)
    A5: =A3-34634.696=0
    A6: =A4-34634.696=0

    A6 returns TRUE. But A5 returns FALSE because A3 is actually 34634.6959999999. (That is, the binary representaton of that constant.)

    Example #2:

    A1: 8234.558
    A2: 8234
    A3: =ROUND(A1-A2,12)
    A4: =ROUND(A1-A2,3)
    A5: =A3-0.558=0
    A6: =A4-0.558=0

    Again, A6 returns TRUE. But A5 returns FALSE because A3 is actually 0.558000000001000. (Again, the binary representation of that constant.)

    Note: Ostensibly, "Precision as displayed" rounds to the "precision required" based on cell format. But PAD is fraught with too many problems to be useful, in general.

  9. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    JoeU commented  · 

    Must it be a format option per se? Are you aware that if A1 is 7:00 AM and B1 is 3:30 PM, then =(B1-A1)*24 is 8.5?

    Note that format options only affect the appearance, not the actual value. So a format option per se that displays 8.5 for =(B1-A1) would not change the fact that the actual value is 8:30. Consequently, if the formatted formula is in C1, =C1*15 would __not__ result in $127.50, the pay for 8.5 hours at $15 per hour.

  10. 9 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    JoeU commented  · 

    Does the format ##0.0E+0 meet the requirement?

  11. 20 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    JoeU commented  · 

    I disagree with the suggestion. I have written extensively over many years about the dubious and misnamed "reaches zero" heuristic. Corey is correct: it is inconsistently applied and implemented. Simply correcting it for adding or subtracting zero as the last term is only the tip of the iceberg. The heuristic should never have been implemented, in the first place. It causes more problems than it solves. For example, the heuristic for when =A1-A2 is replaced with zero is different from when A1=A2 returns TRUE, when A1 is (relatively) infinitesimally close to A2.

    An error occurred while saving the comment
    JoeU commented  · 

    Apologies: I clicked on "flag as inappropriate". Not sure that is proper. Don't see any way to unflag it. Newbie mistake!

Feedback and Knowledge Base