#Num! appears at the end of correct entries
 

Hi I am using a formula in Coulumn B which is
=IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

This is an array formula that must be inserted with:
CNTRL-SHFT-ENTER
rather than the
ENTER key

This is to bring to the top of column all entries that have a positive
reponse to an IF formula in column A which puts "" if false. The above
formula works well but in the cells below the numbers it comes up with #NUM!
in all the cells below
How do I fix it so those cells are blank in column B like in A

Regards David

#Num! appears at the end of correct entries
 

CountA will count cells that include the empty string (ie ""). Sounds like
you have formulae in column A that evaluate to "" that are being counted by
the counta function. Perhaps:

=IF(ROWS($1:1)<=SUM(--($A$1:$A$500<"")),INDEX($A$1:$A$500,SMALL(IF($A$1 :$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

will give the results you're after (I'm shooting from the hip - It's a bit
late and I didn't test this).


"Davidm" wrote:

Hi I am using a formula in Coulumn B which is
=IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

This is an array formula that must be inserted with:
CNTRL-SHFT-ENTER
rather than the
ENTER key

This is to bring to the top of column all entries that have a positive
reponse to an IF formula in column A which puts "" if false. The above
formula works well but in the cells below the numbers it comes up with #NUM!
in all the cells below
How do I fix it so those cells are blank in column B like in A

Regards David

#Num! appears at the end of correct entries
 

in the cells below the numbers it comes up with #NUM!

So, the formula is returning NUMBERS?

Change COUNTA to COUNT.

It's also slightly more efficient if you move this portion:

-MIN(ROW($A$1:$A$500))+1

=IF(ROWS($1:1)<=COUNT($A$1:$A$500),INDEX($A$1:$A$5 00,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)),ROWS ($1:1))-MIN(ROW($A$1:$A$500))+1),"")


--
Biff
Microsoft Excel MVP


"Davidm" wrote in message
...
Hi I am using a formula in Coulumn B which is
=IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

This is an array formula that must be inserted with:
CNTRL-SHFT-ENTER
rather than the
ENTER key

This is to bring to the top of column all entries that have a positive
reponse to an IF formula in column A which puts "" if false. The above
formula works well but in the cells below the numbers it comes up with
#NUM!
in all the cells below
How do I fix it so those cells are blank in column B like in A

Regards David

#Num! appears at the end of correct entries
 

I have changed the counta to only count in the formula and it now seems to work
thanks for your help I will still have a look at what you have written
Thanks

"JMB" wrote:

CountA will count cells that include the empty string (ie ""). Sounds like
you have formulae in column A that evaluate to "" that are being counted by
the counta function. Perhaps:

=IF(ROWS($1:1)<=SUM(--($A$1:$A$500<"")),INDEX($A$1:$A$500,SMALL(IF($A$1 :$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

will give the results you're after (I'm shooting from the hip - It's a bit
late and I didn't test this).


"Davidm" wrote:

Hi I am using a formula in Coulumn B which is
=IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")

This is an array formula that must be inserted with:
CNTRL-SHFT-ENTER
rather than the
ENTER key

This is to bring to the top of column all entries that have a positive
reponse to an IF formula in column A which puts "" if false. The above
formula works well but in the cells below the numbers it comes up with #NUM!
in all the cells below
How do I fix it so those cells are blank in column B like in A

Regards David