Formulas giving two different answers? (Excel 2003 / 2007)

Topic

New Reply

Hi,

In the attached W/B I have a sum function calculating the result of an IF formula to give me an answer of 178. Then I have an array function {SUM(1/COUNTIF(… , …))} that gives me 177? This scenarion is similar in 2003 and 2007.

I need to desperately find out where and why there is a difference as this is a sample of data from the main W/B that contains over 14000 records, where the difference is about 86 between the two calcs. The results is used in statistical reports and we first need to find out what is the correct value (178 or 177) and also what is causing the difference so we can rectify the original file. The original file is using the array functions to provide results. This quey came about when it was double checked using the IF/SUM procedure.

TIA for your valued help!

Reply | Quote

Replies

Some values in column D contain trailing spaces. If you run this code

Sub Repair()
Dim i as Integer
Application.Calculation = xlCalculationManual
For i = 2 To 1000
Range("D" & i) = "'" & Trim(Range("D" & i))
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

the formulas will return the same value.

Reply | Quote

Errr… am I wrong about my belief on the 2 formulas?

cheers, francis

Reply | Quote

Yes, both formulas are used to count the number of unique entries in a column.

Reply | Quote

Hi Rudi

I am unable to open the attachment as I don’t have 07 version
I believe the 2 formula are different in their workings,
in general term with an example below

Col A
10
20
30
40
50
={SUM(IF(A1:A51>10,A1:A5,””))} >> result = 140
Sum with if will add all number except with the given criteria/s, such as to add all in a range that is > 10,

while using
=SUM(1/COUNTIF(A17:A21,”>10″),A17:A21) >> result = 150.25
will add all number in the range plus the value return by 1/countif
as 1 divide 4 minus the cell that is > 10 will give you 0.25

Without knowing the criteria/s of your formula I can only generally stated these, afraid that not much help from me.
Hope this may point you to the right direction.

Reply | Quote

Franciz,

TX so much for your reply. I really appreciate your interest in my query. Since you did not see the actual problem, the sum if scenario you refer to is not in the right context. In the W/B I was simply summing up the result of a seperate IF function similar to =IF(A2=A1,0,1). I summed up all the 1’s to get a unique count of records.

Cheers again.

Reply | Quote

You are a saviour… THX Hans!

PS: Is it better to run the code, or can I use the TRIM function to get rid of the trailing spaces too? Just asking as the person who’s W/B it is is not to familiar with macros.

TX and again!

Reply | Quote

You can use the TRIM function of course.

Reply | Quote