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

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.

2. ## Re: Formulas giving two different answers? (Excel 2003 / 2007)

Some values in column D contain trailing spaces. If you run this code
<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
</code>
the formulas will return the same value.

3. ## Re: Formulas giving two different answers? (Excel 2003 / 2007)

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.

4. ## Re: Formulas giving two different answers? (Excel 2003 / 2007)

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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> again!

5. ## Re: Formulas giving two different answers? (Excel 2003 / 2007)

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. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

6. ## Re: Formulas giving two different answers? (Excel 2003 / 2007)

<img src=/S/ouch.gif border=0 alt=ouch width=15 height=15> Errr... am I wrong about my belief on the 2 formulas?

<img src=/S/grin.gif border=0 alt=grin width=15 height=15>

cheers, francis

7. ## Re: Formulas giving two different answers? (Excel 2003 / 2007)

You can use the TRIM function of course.

8. ## Re: Formulas giving two different answers? (Excel 2003 / 2007)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•