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