Oct 06 2022 08:23 AM
Oct 06 2022 08:23 AM
Oct 06 2022 08:31 AM
Does this do what you want?
=IF(W231-W218>=90, 1199.26, IF(W231-W219>=60, 1132.84, IF(W231-W220>=30, 166.42, 0)))
=IFS(W231-W218>=90, 1199.26, W231-W219>=60, 1132.84, W231-W220>=30, 166.42, TRUE, 0)
Note that the order of the conditions had to be reversed.
Oct 06 2022 08:45 AM
@JoeJP wrote: ``What is wrong with this equation``
To answer your question, the syntax '11/1/22 (leading apostrophe) is valid only as a constant entered by itself into a cell. As the first character, the apostrophe (single-quote) tells Excel not to interpret the characters to the right.
Ostensibly, you might enter "11/1/22" (matching double-quotes). However, I assume you are entering dates in the form DMY. That might not work if the workbook is opened on a computer that uses MDY dates, for example.
DATE(2022,11,1) is more reliable (portable) than DATEVALUE("11/1/22").
Oct 06 2022 08:47 AM
Curious: Is Excel now returning error codes like Error 504 now instead of #VALUE, for exampe ?!
Not according to my google search. But then again, my google search skills are poor.
OTOH, I believe that other spreadsheet apps (Google Sheets, for example?) do return error codes.
Oct 06 2022 09:07 AM
Excel doesn't return error numbers in formulas as far as I know.
Error 504 appears to be a possible error message when Excel crashes, indicating a memory leak...
Oct 06 2022 11:13 AM
Thank you Hans. Your suggestion works, why I don't know. If each IF statement is entered by itself,
the equations work but not when ganged together.
Oct 06 2022 11:16 AM - edited Oct 06 2022 11:17 AM
@Hans Vogelaar wrote: ``Error 504 appears to be a possible error message when Excel crashes, indicating a memory leak``
Thanks. I ask because in another context, a user claims that Excel returns "Err:502" where I would expect #NUM among a series of (improper) XIRR formulas. Clearly, Excel is not "crashing" (terminating unexpectedly) in those instances.
One non-MSFT website claims that Excel returns Error xxx as a "logic error" (distinct from a crash) due to a memory "fault" (computer error) in handling data. But I would be surprised if Excel does not in fact abort when that happens.
Oh well, it's good to know that Excel has not changed the way that it reports user errors. Thanks.