[Solved] Array with empty cell

[britto]

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

[FJCC]

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.

[mikele]

Hello,
if the cell ist formatted a date, then the value 0 means 30.12.1899 or displayed as 30.12

[britto]

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
..

[mikele]

Hello,

Code: Select all

 =IF($C889=0;"";MAX(SE(C$3:C$850=$C889;$N$3:$N850)))

Another option: format the cell with "dd.yy;;;" instead of "dd.yy"

[britto]

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))}

[mikele]

Hello,
SE() is portuguese for IF()
The formula

Code: Select all

=IF($C889=0;"";MAX(IF(C$3:C$850=$C889;$N$3:$N850)))

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.

[britto]

Thanks with Ctrl+Shift+Enter works !!