Blank cells

  • Thread starter Thread starter Antonio
  • Start date Start date
A

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
 
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.)
 
Tks Dave

When I enter the formula the outcome is "False"

Can u pls help in correcting this??

Tks in advance
 
Did you try to retype the formula? Safer to copy and paste, then you'll get
it right.
 
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
 
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 said:
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
Click to expand...
 
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
Click to expand...
 
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
 
=INDEX(D1:D250,LARGE(IF(D1:D250>0,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?
 
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??????
 
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:D250>0,ROW(D1:D250)),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??????
Click to expand...
 
Hi Dave

formula Used on column E.
=IF(COUNTIF(D1:D250;">0")<20;INDEX(D1:D250;LARGE(IF(D1:D250>0;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
 
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(D1:D250;">0")<20;INDEX(D1:D250;LARGE(IF(D1:D250>0;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
Click to expand...
 
You also had another typo in your formula (Indirect).

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


Cause when I use it, the formula returns #name?
Click to expand...
 
Dave

when I try any of the formulas, that kindly someone posts, the result is not
the one I need...
 
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 said:
Cause when I use it, the formula returns #name?
Click to expand...
 
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:D250>0;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.


Dave

when I try any of the formulas, that kindly someone posts, the result is not
the one I need...
Click to expand...
 
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
 
2nd Formula

=INDEX(D1:D250;LARGE(IF(D1:D250>0;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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2002: Return blank when VLOOKUP on blank cells 2
Nested hlookup/vlookup (possibly just an if statement though?!?) 1
WCG Stats Sunday 16 October 2022 2
SUMIf 4
WCG Stats Sunday 13 November 2022 2
WCG Stats Monday 01 November 2021 1
WCG Stats Tuesday 21 July 2020 1
WCG Stats Saturday 28 November 2020 1

Back
Top