Polling any type, 'Blank' cell to empty text (string)

[Lupp]

REALLY OFFTEN I have to take over some grouped data from elsewhere and want the type unchanged during that. OK, I have to accept that there isn’t a consistent typing in spreadsheets. But…

It is very annoying that I have to use ugly complicated formulae for the purpose. Taking no special precautions I get the numerical value 0 (zero) if an empty cell (ISBLANK(CellReferece) = TRUE()) is referenced. And I definitely don’t want that.

I often use =IF(CellReference="";"";CellReference) or =IF(ISNUMBER(CellReference);CellReference;T(CellReference)) and even more sophisticated formulae.

Why?
In many cases the cell reference is calculated by use of INDIRECT() or of INDEX(), this frequently combined. The expressions get soon VERY complicated (calculating the same reference twice or thrice) and VERY ugly then.

Ther must be a better way. You know one?

[begin EDIT1]@mariosv (and everyone)- Think I cannot give a SMALL example. My problems arise from my “obsession” solving recurring tasks by reusable “engines”. Thus it will require some enthusiasm and a bit of time, of course, to study my example and looking out for enhancements. Thank you!

ask35716PollAnyType001.ods[end EDIT1]

[mariosv]

Hi Lupp, maybe T() is your function. It returns a value if there is a text, otherwise return an empty string.

[Lupp]

Thank you, “mariosv”. I already tried that recently. It’s a mess though as I need calculating a reference thrice if I want to ALSO POLL numeric data with the same formula. =IF(ISNUMBER(K38);K38;T(K38)) may do. Having an INDEX(INDIRECT(…)…) construct will not look well - and won’t be efficient.
And I WANT to have a formula applicale to any “value type” without adaptions in advance.

An “untyped system” should do better.

[mariosv]

Please attach a little sample, maybe someone has a trick.

[Lupp]

See my Comment to mariosv’s next answer!

[mariosv]

A formula like {=IF(D46;OFFSET($E$6;$D46-1;0;1;$A$12);"")} (entered as array) seems works fine, even without the IF.

edit 20140621
Several samples with OFFSET()

[Lupp]

Thank you very much, mariosv! This will often do. I never had used OFFSET() in such a context and never had it applied consciously to empty (blank) cells. Given the help text “Returns the value of a cell offset by …” I never would expect that OFFSET() is handling blank cells as it does. Returning the empty string refering to a blank cell does even work for a 1-cell range with OFFSET(). forced to array context. This did never work when I tried it with other means. Will this persist … ?

[Lupp]

… 1-cell-range … array-context …

I mean {=OFFSET(K73;0;0;1;1)} and this IS a detour but by far not as ugly and inefficient as other constructs I had tried to get empty-strings (next to blank) in cells refering to blank ones. A function, say “COPY()”, providing control parameters for the purpose and not restricted to array context would, of course, be better.

[mariosv]

Hi Lupp, I think it’s possible to do using INDIRECT() as array. The issue with arrays is when they change their size, they are not auto adapted. But I think there are other options as filters with a copy destination or pivot table.

[Lupp]

Hello mariosv. The issue with INDIRECT() you named is preclusive for me. Trying to avoid that I would prefer to even use INDIRECT() or INDEX(INDIRECT()) calculating references to single cells and that “forced to array context” (1-cell-array). BUT - even if done by such a construct referring to SINGLE cells one by one will return 0 (zero) for a blank cell and this will … See my comment to your 1. answer! From my many experiments it was ONLY using OFFSET() as you suggested what worked as wanted.

[Lupp]

@mariosv again - Pursuing your hint with OFFSET() applied to ONE-CELL-RANGES by setting the last 2 parameters to1 AND forcing the expression to array context (Ctrl+Shift+Enter) led exactly to the results I wanted. The construct is working perfectly for me. BUT: I cannot find any hint saying me this behaviour will persist over time and versions. I already quoted the help text. More serious: The odf-specification for OFFSET() just says: “Returns: Reference” - as it does for “bad” INDIRECT().

[mariosv]

I think you can’t be worried about it will be changed, even maybe it’s not the most known function, but commonly used, very useful; and I have not seen comments about to change it.

[Lupp]

My worrying comes from the fact that there seems to be NO SPECIFICATION at all describing the effects using INDIRECT() or OFFSET() both forced to array context when asked returning a single cell reference or a (1x1 array?) concerning A BLANK CELL. Both are SPECIFIED EQUALLY but BEHAVE DIFFERENT as far as I can see. Somebody might want to change that - and I couldn’t object reasonably.

[Lupp]

CORRECTION: The right idea just tested: Both functions MAKE THE SAME DIFFERENCE and are capable to return either a SingleCellReference or a OneCellArrayReference! In the first case I get what I do NOT want: Evaluating the referenced cell produces 0 (zero) if the cell was blank. In the second case evaluating the referenced One-Cell-Array lets the cell containing the formula empty otherwise (though it will testify containing text of length 0).

{=INDIRECT("$A$1")} versus {=INDIRECT("$A$1:$A$1")}

[Lupp]

Finally I got it!

Many, many thanks to mariosv for being at my side with his thoughts and his suggestions.

“Final” examples attached, some additional explanations inside the document.

ask35716PollAnyTypeFinalExamples001.ods

[Lupp]

Thank you, karolus. I thought of this effect, too - and you are right: I had filled the formula in… But I hadn’t changed my standard setting with ‘AutoCalculate’ ON. In addition:

1. These cells did (do) not show the value from the cell ‘Copy’ was taken from - bot 0, as I said.

2. The effact did (does) only occur if the referenced cell was bblank while pasting (filling) the formula cell.

3. The situation is still reproducible.

Did you try it?

[Lupp]

Please try reproducing (action in the file attached to this answer,) ‘AutoCalculate’ ON:

1. Let A50 blank. Enter {=A50} into C50 as array-formula. (Get 0.)

2. Copy C50.

3. Let A55 blank. Paste the formula into C55. (Get 0.)

4. Enter 78 (or any number) into A55. (Get 0???)

5. Save file, close LibO, reopen file with Lib=. Get C55 showing 78 (or the other number)???

6. Help me understand.

Version: 4.2.4.2
Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8

[mariosv]

Hi Lupp, solved with Version: 4.2.6.0.0+ TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-06-18_14:40:07, what I think is usable with a similar safety as 4.2.4 if not better. (Index of /libreoffice/old)