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
