# HELP - This is driving me crazy!!!!!

Posted by JAF on January 03, 2001 5:02 AM

I have a very weird problem with Excel that I hope someone can shed some light on, as it's driving me crazy.

In Column AB, I have 8205 records which were produced by dividing one number by another and displaying the result to 4 decimal places using the formula =ROUND(U3,K3,4). All the formulas have been replaced by values and formatted to show a percentage - eg the result of the ROUND formula of 0.3817 has been formatted to show 38.17%.

If I type in the formula =COUNTIF(\$AB\$3:\$AB\$8207,"&LT;=0.5"), Excel correctly returns the correct answer.

If however, I type in the formula =COUNTIF(\$AB\$3:\$AB\$8207,"&LT;=AE2") (where cell AE2 contains a value of 0.5), Excel returns a zero value.

Any suggestions as to why Excel is being so weird and any solutions as to how to fix it????

NB: The problem first came to light when I was building an Array Formula. When I had completed the first part of the formula =SUM(('Unitary Data'!\$D\$3:\$D\$8207=E3)*('Unitary Data'!\$U\$3:\$U\$8207)) - it worked fine, but when I added a section to only include accounts with a value in column AB greater than the value in cell A6, it returned a #DIV/0! error which I don't understand as I'm not trying to divide in the formula - =SUM(('Unitary Data'!\$D\$3:\$D\$8207=E3)*('Unitary Data'!\$AB\$3:\$AB\$8207&LT;=A6)*('Unitary Data'!\$U\$3:\$U\$8207))

Hope someone can solve this!!!
JAF

Posted by Aladin Akyurek on January 03, 2001 5:14 AM

TRY:

=COUNTIF(\$AB\$3:\$AB\$8207,"&LT;="&AE2)

Even better:Give a sensible name to the cell AE2 via the Name Box and use in the above formula the name instead of AE2 after &.

Posted by JAF on January 03, 2001 6:09 AM

Re: HELP - This is driving me crazy! - SOLVED

Problem solved.

I had a single record in column AB with a #DIV/0 error which was throwing out the Array Formula!

Doh!

JAF : I have a very weird problem with Excel that I hope someone can shed some light on, as it's driving me crazy. : JAF

Posted by Aladin Akyurek on January 03, 2001 9:00 AM

Re: HELP - This is driving me crazy! - SOLVED

Problem solved.

I see you've run into the countif-problem while trying to track down your input problem with countif. ;-)

You still risk having trouble with your array formula when the input to it not error-free. You should try to control the input somehow. In the mean time, you might use the following formulas:

=IF(ISNUMBER(SUM('Unitary Data'!\$D\$3:\$D\$8207,'Unitary Data'!\$AB\$3:\$AB\$8207,'Unitary Data'!\$U\$3:\$U\$8207)),1,0)

Name the cell where you put this formula, e.g., InputOK. Then edit your array formula as

=IF(InputOK,SUM(('Unitary Data'!\$D\$3:\$D\$8207=E3)*('Unitary Data'!\$AB\$3:\$AB\$8207&LT;=A6)*('Unitary Data'!\$U\$3:\$U\$8207)),"Input Error")

This isn't much, but it may prevent one from getting "crazy".

Cheers.