Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?

Michael D. Setzer II wrote:
> Was interested if it could be modified for multi-column searches.
> Did a same test using H2:I5 and used this formula.
>
> = SMALL((IF(ISNUMBER($H$2:$I$5), "ALL
> GOOD",COLUMN($H$2:$I$5)*1000+ROW($H$2:$I$5))), 1)
>
> That returns the number 8004 and 2nd one returns 9005.
> Then used this formula to convert to a cell address.
> =CHAR(INT(L2/1000)+64)&MOD(L2,1000)
>
> That only works for first 26 columns, and upto 999 rows, but could
> be modified.

Hi Michael,
I've got another way of finding the address of the first non-numeric
value in a multi-column search.

= ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))),
MIN(IF(ISNUMBER(OFFSET($G$4:$H$7, MIN(IF(ISNUMBER($G$4:$H$7), "All
good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), "All good",
COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All good",
ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)))))

Please note that it should be entered as an array formula by pressing
CTRL+SHIFT+ENTER instead of just plain ENTER (curly braces will appear
around the formula if it is done correctly).  The advantage of this
formula is that it should work for any contiguous range, not limited
to 999 rows or 26 columns.

There are also disadvantages to this formula.  The first obvious
disadvantage is debugging/understanding the formula (if anyone wants
an explanation of how it works, feel free to ask).  The second
disadvantage is that I can't think of a logical way to extend it to
find the 2nd or 3rd non-numeric value.  Your (Michael's) formula can
easily be modified to search for the 2nd, 3rd, 4th etc. non-numeric
cell.  The formula also references the range in question quite a few
times, which makes it a pain to change the reference to another range
(in this case I would suggest a "find & replace" for $G$4:$H$7)

The above formula returns "Err:502" if all the values are non-numeric.
 To get a more user friendly result, use the following formula:

= IF(MIN(ISNUMBER(G4:H7)), "All values are numeric",
ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))),
MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All
good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), "All good",
COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All good",
ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))))))

Once again, remember to enter it as an array formula (by pressing
CTRL+SHIFT+ENTER instead of just ENTER).

Regards
Stephan

-- 
Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted