How do I get formulas to ignore text in operands like I do in excel, and not result in #VALUE!?
I don’t use Excel. Can you give a sample formula that works there and the expected result? Thanks.
Most probaply:
=IFERROR( your_top_secret_formula ; "")
Thanks. I got an answer that works. A sample formula is =A1+B2-C3+D4. If one of those cells in the formula has test such as “Hello”, I got #VALUE! The answer i got was to put my formulas into a Sum function. Thus: =Sum(A1+B2-C3+D4). Excel had an option to ignore text in operands, so they didn’t need to be put into a Sum function. I changed all my formulas. Now my next task is to fix the pagination that was messed up
Actually if error would not work because the result that i want is not blank. The that I want is the same result i would get if the cell with text had a zero in it. The SUM function achieves that purpose.
Basically, I want the ability to be able to put comments in cells that do not have values without disturbing the results
For comments use the cell comment functionality, no? Or =N("comment") which returns 0.
2 Likes
Calc has it, too: Options → Calc → Formula → Detailed Calculation Settings → Custom (conversion of text to numbers and more) → Details:
Calc provides every possible kind of footgun.
3 Likes
Though that setting has the side effect that erroneous text instead of numeric values as arithmetic operands may go unnoticed in calculations. The only sane setting is “Generate #VALUE! error”, followed (in sense of sanity) by “Convert only if unambiguous”. Excel uses the dreaded error prone “Convert also locale dependent”.
2 Likes
Have a look to =AGGREGATE(9,3,…)