Blank cells

[Antonio]

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]

I selected E1:E20 and used this formula:
=IF(COUNT(D)<20,"not enough numbers",OFFSET(D1,COUNT(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]

Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance

 

[David Biddulph]

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

 

[Antonio]

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]

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

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

 

[Dave Peterson]

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

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

 

[Antonio]

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]

=INDEX(D1250,LARGE(IF(D1250>0,ROW(D1250)),21-ROW(INDIRECT("1:20"))))

Still an array formula.
Change D1250 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]

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]

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(D1250,">0")<20,"not enough umbers",
INDEX(D1250,LARGE(IF(D1250>0,ROW(D1250)),21-ROW(INDIRECT("1:20")))))

(still array entered)

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??????

 

[Antonio]

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1250;">0")<20;INDEX(D1250;LARGE(IF(D1250>0;ROW(D1250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

 

[Dave Peterson]

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

Why?

Hi Dave

formula Used on column E.
=IF(COUNTIF(D1250;">0")<20;INDEX(D1250;LARGE(IF(D1250>0;ROW(D1250));21-ROW(inderect("1:20")))))

entered as an array +

Now receive "false message"

Going crazy (n driving u crazy"

Sorry n tks

 

[Antonio]

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

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

Why?

 

[Dave Peterson]

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

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

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

 

[Antonio]

Dave

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

 

[David Biddulph]

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

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

 

[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(D1250;">0")<20;"not enough numbers";
INDEX(D1250;LARGE(IF(D1250>0;ROW(D1250));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.

Dave

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

 

[Antonio]

Hi

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

1st formula
=IF(COUNT(D)<20;"not enough numbers";OFFSET(F1;COUNT(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

 

[Antonio]

2nd Formula

=INDEX(D1250;LARGE(IF(D1250>0;ROW(D1250));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!