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!!!