User Profile
JoeUser2004
Bronze Contributor
Joined 8 years ago
User Widgets
Recent Discussions
Re: Can anyone explain this behaviour?
RedNectar wrote: But if I calculate the 49 value using a double inverse, i.e. =BITOR(1,1/1/49) [,] I get a #NUM! error Expanding on Sergei's and Hans's correct responses, it might be noted that 1/1/49 (sic) is not the same as the "double inverse" 1/(1/49). 1/1/49 displays 0.0204081632653061 , whereas 1/(1/49) displays 49. Mathematically, 1/1/49 is equivalent to 1/(1*49). Hans correctly explains that 1/(1/49) is not exactly 49, despite appearances. But even the 17-significant-digit value in the XML file is an approximation. In general, it is the minimum number of significant digits that is necessary and sufficient to reproduce the original binary value (*). ----- * Re: 17 "is the minimum number of significant digits that is necessary and sufficient to reproduce the original binary value" .... If Excel did not normally truncate such input to 15 significant digits. (Klunk!) But we can input such numbers with no loss of precision by using Power Query or VBA.Why can't I post long numbers (in the Excel subforum)?
Note that the above is an image. When I try to submit a response that included that as text, I get the following error (again, an image): Why? That is, what consequences is the error filter trying to protect us from? More to the point: Is there a workaround that allows me to enter that as text instead of an image? I want people to be able to copy-and-paste the content.Count how many pairs of dice sum >= 7?
Please help me help someone in another forum. The problem: if we toss a pair of dice (6-sided), what is the probability that their sum is 7 or more? As I demonstrate below, the answer comes down to enumerating and counting the number of pairs with sum >= 7. Then, the probability is (#paired sums >= 7) / #pairs, where #pairs is #sides^2. (The answer is 21/36 = 7/12.) My question is: is there a formula that calculates #paired sums >= 7 without doing the enumeration manually or relying on VBA? I'm guessing that we can use Excel 365 features such as LAMBDA etc. But I know nothing of those features. And less importantly, for my benefit, is there an Excel 2010 formula, without relying on VBA? My "manual" solution:Re: Problem with the EDATE function
Crossposted to answers.microsoft.com (click here), where I posted the following response on Jan 4, 2025. EDATE(date, n) returns the same day of the n-th month, or the last day when there is no corresponding date. I believe EDATE follows US law, if not international law. For example, "European Convention on the Calculation of Time-Limits" (click here), Article 4 paragraph 2. And yes, that has the unfortunate consequence that EDATE is not "invertible" (mathematically). That is, EDATE(EDATE(date), 1), -1) is not always "date"; for example, for DATE(2023, 10, 31).Re: Squaring with preceding minus
When I put =-5^2 in a cell, it gives me 25 as a result. [....] Is that the standard Excel behaviour? It is documented intentional behavior. See "Calculation operators and precedence in Excel". It is prudent to use parentheses to ensure the intended interpretation: either -(5^2) or (-5)^2. Or we can use the POWER function: -POWER(5, 2) or POWER(-5, 2). ----- TMI.... Common misunderstanding: Excel's order (unary negation before exponentiation) is "wrong" because it is done differently in math formulas. That is like arguing that the French are "wrong" because they write "black women" as "femmes noires", putting an adjective after the noun instead of before it. ( And the French would argue that English is "wrong". :wink: ) The point is: Excel and math are different languages, and each has its own rules of syntax. The only reason why "minus two to the power of four" (which is ambiguous in English) "obviously" seems to be -16 in math is because math has different syntax, namely: and the rule is: superscript expressions have higher precedence. Arguably, math has the "nice property" that -2^4 would be equivalent to 0 - 2^4, which is not true in Excel. OTOH, in math, we can easily write which is interpreted as 128 (128 = 2^7). Is Excel "wrong" because we must write 2^(4+3) instead of simply 2^4+3, adding parentheses to force a mathematical interpretation? ( Rhetorical. Of course not! ) Bottom line: Each language has its own syntax and rules for interpretation (semantics). It is only a coincidence -- or by purposeful design -- that some languages share some syntax and semantics. That does not make one or the other language "wrong" when there are differences.Re: XLOOKUP returning #N/A for some values when the values are there
Nabercrombie55 As Patrick2788 wrote, it behooves you to show us the formulas -- and the data as well. Even better (since cell=cell has different results), attach an Excel file that demonstrates the problem. Click on "browse files to attach" at the bottom of the reply applet. That said, I suspect that one or both cells are calculated, and their internal binary values differ infinitesimally. The remedy might be: be sure that both cells are explicitly rounded to the same precision. The difference might be invisible because Excel formats only up to 15 significant digits. And the equal operator ( "=" ) compares values that are rounded to 15 significant digits internally just for the purpose of the comparison. In contrast, lookup and match functions compare the full binary value, which is not limited 15 significant digits, contrary to most online documentation including MSFT's. Example:Can Excel 365 newspeak simplify the solution?
I'm trying to help someone in another forum. Can we use Excel 365 newspeak to simplify the implementation? Unfortunately, I don't speak Excel 365. I thought I might use this problem to learn by example. And when I say "Excel 365", I mean to include recent versions of Excel that have the same features -- Excel 2019 and later? The following image demonstrates how to calculate the discounted cash flow of increasing cash flows. Formulas: B4: 100000 B5 (copy down): =B4 * (1 + LOOKUP(A5, $F$4:$H$4, $F$5:$H$5)) C4 (copy down): =B4 / (1 + $B$1)^A4 C26: =SUM(C4:C24) C27: =B4 + NPV(B1, B5:B24) Can we eschew the DCF table and calculate sum(DCF) and/or npv(CF) using Excel 365 newspeak? In pseudo-code, the formulas might take the following forms: sum(DCF): =let(y=0, cf0=100000, cf=cf0, cf0 + sum(arrayof(lambda(y=y+1, cf=cf*(1+lookup(y, $F$4:$H$4, $F$5:$H$5)), cf / (1+$B$1)^y)))) npv(CF): =let(y=0, cf0=100000, cf=cf0, cf0 + npv($B$1, arrayof(lambda(y=y+1, cf=cf*(1+lookup(y, $F$4:$H$4, $F$5:$G$5), cf)))) Of course, I need some syntax to limit y=1 to 20. No idea what that might look like. The pseudo-LAMBDA expressions are intended to be recursive.Re: I have an Issue in calculate depreciation formulas
alymaghrabi2 Since your use of AMORLINC returns numeric values, obviously the problem is not related to the parameter separator (semicolon vs comma). Using the wrong parameter separator would result in a syntax error, not unexpected numeric results. ----- Unfortunately, the MSFT Excel support page (click here) for AMORLINC is light in details. And that is an understatement. :sigh: For more details, I suggest that we refer to the LibreOffice Calc documentation page (click here). Keep in mind that there might be differences between Calc and Excel. But to the point, the Calc documentation has some useful insights. And one point, in particular, might explain the "issue" that you have. First, I note that you are using day count basis 0, which treats the year as 360 days and each month as 30 days. Elsewhere, Excel documentation explains that for day count basis 0, month day 31 is treated the same as day 30, as we should expect. Thus, in the two examples whose results you say are wrong, the purchase date is effectively the same as the first period end-date, since DATE(2023,12,31) is treated the same as DATE(2023,12,30). Second, note this excerpt from the Calc documentation page: ``Calc and Excel behave differently when the date of purchase coincides with the end of an accounting period. Calc regards the initial period 0 as of zero length, and thus returns zero depreciation. Excel regards the initial period 0 as the first full period.`` Consequently, for your example, Excel should (intends to) return 25% of the cost (according to the Calc documentation page). And it does. I am not familiar with the French Accounting Standards. So, I cannot offer an opinion about whether the Excel behavior is correct or not. (Okay, I can't resist. :wink: Conceptually, I agree with Calc, although the devil is in details that I'm not taking the time to verify. But again, I don't know if the FAS says otherwise. :surprise: ) If you can document that the Excel behavior is contrary to the FAS, you might consider using the Office Feedback feature to complain to MSFT. But IMHO, that would be a waste of time. Even if MSFT concedes the error, it is not likely to fix it because of concerns for backward compatibility.
