Results 1 to 8 of 8

20090204, 09:29 #1
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
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.
TIA for your valued help!Regards,
Rudi

20090204, 10:22 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20090204, 10:34 #3
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Hope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090204, 10:34 #4
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
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!Regards,
Rudi

20090204, 10:38 #5
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
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>Regards,
Rudi

20090204, 10:39 #6
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090204, 10:48 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formulas giving two different answers? (Excel 2003 / 2007)
You can use the TRIM function of course.

20090204, 11:15 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formulas giving two different answers? (Excel 2003 / 2007)
Yes, both formulas are used to count the number of unique entries in a column.