Feedback by UserVoice

# JoeU

1. ## XNPV should work for negative IRR

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

JoeU shared this idea  ·
2. ## Fix IRR & XIRR

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

JoeU shared this idea  ·
3. ## Please fix =IRR generating #NUM! error with large numbers

(thinking…)
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. ## BUG: Eliminate partial-formula evaluation "feature" for formulas of the form +...

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

JoeU shared this idea  ·
5. ## Semi Average Regression Line

(thinking…)
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. ## Support formatting or rounding to user-specified number of significant figures

(thinking…)
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. ## Allow for editing fractional seconds (hh:mm:ss.000)

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

JoeU shared this idea  ·
8. ## Do we need 15 decimal places?

(thinking…)
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. ## Add number formats for just hours for calculating time elapsed

(thinking…)
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. ## Engineering Notation

(thinking…)
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. ## Correct floating point bug

(thinking…)
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!