![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Date sent: Wed, 8 Jun 2011 10:37:07 +0200
tested the
backward-compatibility of LibreOffice 3.4?
Send reply to: users@libreoffice.org
Stephan wrote:
> 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))))))I just have a small correction to make. For consistency, the first
range reference in the formula should also be absolute (i.e. G4:H7
should be $G$4:$H$7). So the formula should actually be:= IF(MIN(ISNUMBER($G$4:$H$7)), "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).
The Address function was something I didn't recall, use to teach a
spreadsheet class, but haven't in a long time. Did come up with
some other testing.
Filled in A1:AF20 with a bunch of numbers, and then I randomly
put in some "a" characters in cells.
In Cell AG1 placed this to get the number of non-numeric
=COUNTA(A1:AF20)-COUNT(A1:AF20)
In Cells AH1 thru AH15 put the numbers 1 to 15
In Cell AI1 thru AI15 put this formula
{= SMALL((IF(ISNUMBER($A$1:$AF$20), "ALL
GOOD",COLUMN($A$1:$AF$20)*1000+ROW($A$1:$AF$20))),
AH1)}
Then in AJ1 thru AJ15 put this formula
=ADDRESS(MOD(AI1,1000),INT(AI1/1000))
This is the result.
13 non-numeric fields and their addresses.
13
1
1007
$A$7
2
4011
$D$11
3
8010
$H$10
4
11012
$K$12
5
13007
$M$7
6
17011
$Q$11
7
19015
$S$15
8
24017
$X$17
9
26009
$Z$9
10
29009
$AC$9
11
31015
$AE$15
12
32011
$AF$11
13
32015
$AF$15
14
#VALUE!
#VALUE!
15
#VALUE!
#VALUE!
Could add an if to the AJ formula to only display if less than or
equal to $AG$1.