MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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,"<=0.5"), Excel correctly returns the correct answer.

If however, I type in the formula =COUNTIF($AB$3:$AB$8207,"<=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<=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,"<="&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 &.

Aladin

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<=A6)*('Unitary Data'!$U$3:$U$8207)),"Input Error")

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

Cheers.

Aladin