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.
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.
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
”.
Have a look to =AGGREGATE(9,3,…)