[Solved] Array with empty cell

Discuss the spreadsheet application

[Solved] Array with empty cell

Postby britto » Mon Jan 21, 2019 4:44 pm

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
Last edited by Hagar Delest on Fri Jan 25, 2019 10:26 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 3.4.1 on Windows XP
britto
 
Posts: 7
Joined: Fri Mar 08, 2013 1:54 pm

Re: array with empty cell

Postby FJCC » Mon Jan 21, 2019 5:21 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7017
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: array with empty cell

Postby mikele » Mon Jan 21, 2019 5:25 pm

Hello,
if the cell ist formatted a date, then the value 0 means 30.12.1899 or displayed as 30.12
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 33
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Array with empty cell

Postby britto » Wed Jan 23, 2019 2:47 pm

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
..
OpenOffice 3.4.1 on Windows XP
britto
 
Posts: 7
Joined: Fri Mar 08, 2013 1:54 pm

Re: Array with empty cell

Postby mikele » Wed Jan 23, 2019 4:50 pm

Hello,
Code: Select all   Expand viewCollapse view
=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"
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 33
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Array with empty cell

Postby britto » Thu Jan 24, 2019 4:14 pm

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))}
OpenOffice 3.4.1 on Windows XP
britto
 
Posts: 7
Joined: Fri Mar 08, 2013 1:54 pm

Re: Array with empty cell

Postby mikele » Fri Jan 25, 2019 12:32 am

Hello,
SE() is portuguese for IF()
The formula
Code: Select all   Expand viewCollapse view
=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.
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 33
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Array with empty cell

Postby britto » Fri Jan 25, 2019 1:01 pm

Thanks with Ctrl+Shift+Enter works !!
OpenOffice 3.4.1 on Windows XP
britto
 
Posts: 7
Joined: Fri Mar 08, 2013 1:54 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 29 guests