Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row (some
times more).

I am looking for a way (formula) to, in column E, have as result last 20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row (some
times more).

I am looking for a way (formula) to, in column E, have as result last 20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson

Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row (some
times more).

I am looking for a way (formula) to, in column E, have as result last 20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
António

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson

If you're still getting the FALSE result, copy your formula back from the
formula bar to the group here.

Did Excel add the curly brackets when you did Control Shift Enter? [But if
you didn't do that, I'd expect a result of #VALUE!, not FALSE, so I still
suspect an erroneously typed formula.]

I'm confused that you say the formula didn't work and that you got the
result FALSE, as the values you give below seem to show the correct values
in E1:E20 as one would expect from that formula, with the last 20 non-blank
values from column D transferred to column E as you requested.
--
David Biddulph

"Antonio" wrote in message
...
hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
António

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll
get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough
numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the
following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result
last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
António

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
António

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
António

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
António

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
António

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks


"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀšÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀšÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ*€„¢ÃƒÆ’€šÃ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You also had another typo in your formula (Indirect).

=IF(COUNTIF(D1:D250;"0")<20;"not enough umbers",
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave

when I try any of the formulas, that kindly someone posts, the result is not
the one I need...



"Dave Peterson" wrote:

You also had another typo in your formula (Indirect).

=IF(COUNTIF(D1:D250;"0")<20;"not enough umbers",
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ*€„¢ÃƒÆ’€ €„¢ÃƒÆ€šÃ₠¬Å¡Ãƒ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

If it does that, it probably means you've typed something wrongly again.
[Yes, I can spot at least one typing error in what you've got below. Please
learn to copy and paste.]

By omitting that term you've actually reversed the functioning of the IF
statement, so you're wasting your own time and the time of those who (up to
now) have been trying to help you. It was exceptionally brave of you to
assume that Dave who gave you advice had got it wrong and that you knew
better.
--
David Biddulph

"Antonio" wrote in message
...
Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from
yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",

INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but
you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of
copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only
the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David
suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as
follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃfÆ'Ã??TÃf?sÃ,³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and
paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in
message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough
numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter
instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around
your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by
using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until
the 400th row
(some
times more).

I am looking for a way (formula) to, in column E,
have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

This post doesn't really help diagnose any problems. You're going to have to be
more specific.

In my earlier post, I tried to change all the commas to semicolons, but missed
one:

=IF(COUNTIF(D1:D250;"0")<20;"not enough numbers";
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))

Remember to select E1:E20
with E1 the activecell
paste into the formula bar
and hit ctrl-shift-enter.

If this doesn't work, copy it once more and try it again. Then if that fails,
post back with your formula and the steps you took.



Antonio wrote:

Dave

when I try any of the formulas, that kindly someone posts, the result is not
the one I need...

"Dave Peterson" wrote:

You also had another typo in your formula (Indirect).

=IF(COUNTIF(D1:D250;"0")<20;"not enough umbers",
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ €„¢ÃƒÆ’€ €„¢ÃƒÆ€šÃ₠¬Å¡Ãƒ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi

Will do a recap of all tryes, to see if u can help in obtaining the desired
result

1st formula
=IF(COUNT(D:D)<20;"not enough numbers";OFFSET(F1;COUNT(D:D)-20;0;20;1))

RESULT
52
0
0
0
96
96
0
52
0
0
0
96
96
0
52
0
0
0
96
96


"Dave Peterson" wrote:

You also had another typo in your formula (Indirect).

=IF(COUNTIF(D1:D250;"0")<20;"not enough umbers",
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ*€„¢ÃƒÆ’€ €„¢ÃƒÆ€šÃ₠¬Å¡Ãƒ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

2nd Formula

=INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20"))))

RESULT
52
0
0
0
96
96
0
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!


"Dave Peterson" wrote:

You also had another typo in your formula (Indirect).

=IF(COUNTIF(D1:D250;"0")<20;"not enough umbers",
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ*€„¢ÃƒÆ’€ €„¢ÃƒÆ€šÃ₠¬Å¡Ãƒ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I don't see where you tried the last suggestion:

=IF(COUNTIF(D1:D250;"0")<20;"not enough numbers";
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

2nd Formula

=INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20"))))

RESULT
52
0
0
0
96
96
0
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

"Dave Peterson" wrote:

You also had another typo in your formula (Indirect).

=IF(COUNTIF(D1:D250;"0")<20;"not enough umbers",
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ €„¢ÃƒÆ’€ €„¢ÃƒÆ€šÃ₠¬Å¡Ãƒ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave

Thank you very much for your pacience and help.

The formula does work. The problem was that when I originally tryed it, and
obtained a "false" message, deleted the contents os the first row.

Tks once again
best regards
Antonio

"Dave Peterson" wrote:

This post doesn't really help diagnose any problems. You're going to have to be
more specific.

In my earlier post, I tried to change all the commas to semicolons, but missed
one:

=IF(COUNTIF(D1:D250;"0")<20;"not enough numbers";
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))

Remember to select E1:E20
with E1 the activecell
paste into the formula bar
and hit ctrl-shift-enter.

If this doesn't work, copy it once more and try it again. Then if that fails,
post back with your formula and the steps you took.



Antonio wrote:

Dave

when I try any of the formulas, that kindly someone posts, the result is not
the one I need...

"Dave Peterson" wrote:

You also had another typo in your formula (Indirect).

=IF(COUNTIF(D1:D250;"0")<20;"not enough umbers",
INDEX(D1:D250;LARGE(IF(D1:D2500;ROW(D1:D250));21-ROW(INDIRECT("1:20")))))



Antonio wrote:

Cause when I use it, the formula returns #name?

"Dave Peterson" wrote:

I had a string "not enough numbers" in my formula. You dropped it from yours.

Why?

Antonio wrote:

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;"0")<20;INDEX(D1:D250;LARGE(I F(D1:D2500;ROW(D1:D250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

"Dave Peterson" wrote:

Did you use ctrl-shift-enter to array enter the formula?

And just in case you don't have 20 numbers greater than 0.

=IF(COUNTIF(D1:D250,"0")<20,"not enough umbers",
INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

Antonio wrote:

Hi Dave

Well now it returns

4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!
1 52 52 #NUM!
4 200 0 #NUM!
4 200 0 #NUM!
4 96 0 #NUM!
2 96 96 #NUM!
2 96 96 #NUM!
3 141 0 #NUM!

What i'm I doing wrong??????

"Dave Peterson" wrote:

=INDEX(D1:D250,LARGE(IF(D1:D2500,ROW(D1:D250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1:D250 to a range large enough for all the numbers, but you can't use
the whole column unless you're using xl2007.

And you meant appear in column E, right?
Antonio wrote:

Hi sorry to bother again....

This is the result I obtain, after following your advise of copying the
formula, instead of typing it myself +
I was considering "0" has blank.... what I need is that only the values
bigger then zero to appear on column D

Sorry for the trouble I'm giving and
tks once again

Kind rgds
Antonio

"Dave Peterson" wrote:

And maybe you changed the formula--either do what David suggested (recopy from
the newsgroup posting) or post the version you used.

Antonio wrote:

hi David

Tryed it also, but it seems not to work, as the result as follows:
B C D E
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52 0
4 200 0 0
4 200 0 0
4 96 0 96
2 96 96 96
2 96 96 0
3 141 0 52
1 52 52
4 200 0
4 200 0
4 96 0
2 96 96
2 96 96
3 141 0
1 52 52

Rgds
AntÃÆÀ*€„¢ÃƒÆ’€ €„¢ÃƒÆ€ €„¢ÃÆà €*€„¢ÃƒÆ’¢â€šÂ¬Ã€¦Ã‚¡ÃƒÆ€ ™ÃƒÂ¢Ã¢€šÂ¬Ã…¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚³nio

"David Biddulph" wrote:

Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
--
David Biddulph

"Antonio" wrote in message
...
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

"Dave Peterson" wrote:

I selected E1:E20 and used this formula:
=IF(COUNT(D:D)<20,"not enough numbers",OFFSET(D1,COUNT(D:D)-20,0,20,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)



Antonio wrote:

Hi all

Have numbers on column D, that are placed there by using the following
formula:
=IF(B14<=2,C14,0). This formula is copied down until the 400th row
(some
times more).

I am looking for a way (formula) to, in column E, have as result last
20
rows of non blank data.
I.E
B C D E
4 200 0 96
4 200 0 96
2 96 96 52
2 96 96
3 141 0
1 52 52

Tks in advace

--

Dave Peterson





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson