Formulas to ignore text

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: OptionsCalcFormulaDetailed Calculation SettingsCustom (conversion of text to numbers and more)Details:
image

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,…)