Hi
I rarely use functions but
I needed to search max value for a record matching an new enter, in this case older date matching same name just inserted
I use this array for example =MAX(SE(C$3:C$850=$C889;$N$3:$N850))
when C889 is empty return always same date 30.12
I check value of empty cell is 0 but nowhere cell = 0 in C3:C850
anyway I use IF <> "" in another function to avoid result when cell is empty in another function
could please tell me how to write it for array as my tries fails
[Solved] Array with empty cell
[Solved] Array with empty cell
Last edited by Hagar Delest on Fri Jan 25, 2019 10:26 pm, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OpenOffice 3.4.1 on Windows XP
Re: array with empty cell
I cannot reproduce your result. If the cell with the search target is empty, my formula returns 0. Can you post a small example file? To upload a file, click Post Reply and look for the tab that says Upload Attachment just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: array with empty cell
Hello,
if the cell ist formatted a date, then the value 0 means 30.12.1899 or displayed as 30.12
if the cell ist formatted a date, then the value 0 means 30.12.1899 or displayed as 30.12
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Re: Array with empty cell
Thanks
I could understand ...no matching result give 30.12
How to make function search only when cell is not empty
How to insert in the formula IF <> 0
..
I could understand ...no matching result give 30.12
How to make function search only when cell is not empty
How to insert in the formula IF <> 0
..
OpenOffice 3.4.1 on Windows XP
Re: Array with empty cell
Hello,
Another option: format the cell with "dd.yy;;;" instead of "dd.yy"
Code: Select all
=IF($C889=0;"";MAX(SE(C$3:C$850=$C889;$N$3:$N850)))
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Re: Array with empty cell
if i write IF condition before array, after SHIFT+ENTER (array KEYs) doesn't set array with {}
that's my question this is result =IF($C889=0;"";MAX(IF(C$3:C$801=$C890*C890>0;$N$3:$N801)))
starting formula {=MAX(SE(C$3:C$850=$C889;$N$3:$N850))}
that's my question this is result =IF($C889=0;"";MAX(IF(C$3:C$801=$C890*C890>0;$N$3:$N801)))
starting formula {=MAX(SE(C$3:C$850=$C889;$N$3:$N850))}
OpenOffice 3.4.1 on Windows XP
Re: Array with empty cell
Hello,
SE() is portuguese for IF()
The formula works fine (as an array function), but I use LibreOffice. Maybe there are differences in this case.
Try it again: close this formula with Ctrl+Shift+Enter.
SE() is portuguese for IF()
The formula
Code: Select all
=IF($C889=0;"";MAX(IF(C$3:C$850=$C889;$N$3:$N850)))
Try it again: close this formula with Ctrl+Shift+Enter.
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10