Set of numbers

Hi all

Need your help, once again, if possible.

1 - On each set of numbers, how can i count the ones that are odd.
2 - Is there any way in a column, to know which is the last cell of a column filled in
and it´s contents?

Tks in advance
Antonio

Hi
for the first question try
=3DSUMPRODUCT(--(MOD(A1:A100,2)=3D1))

for the second one find below a couple of possible=20
formulas (depending on the type of your
data): In your case take A.2.d or A.2.e
-------------------
A. Collection of formulas to return the last value in a=20
COLUMN
depending on the type/structure of data.
1. If you have no blank rows in between use
=3DOFFSET($A$1,COUNTA($A:$A)-1,0)

2. If you have blank rows in between try the following=20
depending of the
type of values in your column:
2.a. If you have ONLY text values in column A try
=3DINDEX(A:A,MATCH(REPT("z",255),A:A))

2.b. If you have ONLY numbers in column A:
=3DINDEX(A:A,MATCH(9.99999999999999E307,A:A))
or
=3DLOOKUP(9.99999999999999E307,A:A)

2.c. If you have BOTH types (text and values), but AT=20
LEAST one text
and one numeric entry
=3DINDEX(A:A,MAX(MATCH(9.99999999999999E307,A:A),M ATCH(REPT
("z",255),A:A)
))

2.d. If you don't know the type of data use the following=20
array
function (entered with CTRL+SHIFT+ENTER)
=3DINDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))
or
=3DLOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)
(thanks to Harlan Grove for this formula)
Note: Does not work with range references like A:A


2.e If you don't want to count formula results like =3D"" as=20
entry adapt
2.d. as follows:
=3DINDEX(A:A,MAX(IF(A:A"",0,ROW(A:A))))

----------

B. Collection of formulas to return the last value in a=20
ROW depending
on
the type/structure of data.
1. If you have no blank columns in between use
=3DOFFSET($A$1,0,COUNTA($1:$1)-1)

2. If you have blank columns in between try the following=20
depending of
the type of values in your row:
2.a. If you have ONLY text values in column A try
=3DINDEX(1:1,1,MATCH(REPT("z",255),1:1))

2.b. If you have ONLY numbers in column A:
=3DINDEX(1:1,1,MATCH(9.99999999999999E307,1:1))
or
=3DLOOKUP(9.99999999999999E307,1:1)

2.c. If you have BOTH types (text and values), but AT=20
LEAST one text
and one numeric entry
=3DINDEX(1:1,1,MAX(MATCH(9.99999999999999E307,1:1) ,MATCH(REPT
("z",255),1:
1)))

2.d. If you don't know the type of data use the following=20
array
function (entered with CTRL+SHIFT+ENTER)
=3DINDEX(1:1,1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))) )
or
=3DLOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
(thanks to Harlan Grove for this formula)

2.e If you don't want to count formula results like =3D"" as=20
entry adapt
2.d. as follows:
=3DINDEX(1:1,1,MAX(IF(1:1"",0,COLUMN(1:1))))

-----Original Message-----
Hi all

Need your help, once again, if possible.=20

1 - On each set of numbers, how can i count the ones that=20
are odd.=20
2 - Is there any way in a column, to know which is the=20
last cell of a column filled in=20
and it=C2=B4s contents?=20

Tks in advance=20
Antonio=20

.

"Frank Kabel" wrote...
....
2.d. If you don't know the type of data use the following
array function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))
....

You didn't test this. A:A on its own works when treated as a *range*, but
ISBLANK(A:A) is necessarily an array *result* and not a range, so this
formula template can't handle entire column references.

Harlan Grove wrote:
"Frank Kabel" wrote...
...
2.d. If you don't know the type of data use the following
array function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))
...

You didn't test this. A:A on its own works when treated as a *range*,
but ISBLANK(A:A) is necessarily an array *result* and not a range, so
this formula template can't handle entire column references.

Hi Harlan
thanks for spotting this. Will change this!
Frank