Results 1 to 8 of 8
  1. #1
    Gold Lounger
    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!
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    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.

  3. #3
    3 Star Lounger
    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

  4. #4
    Gold Lounger
    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

  5. #5
    Gold Lounger
    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

  6. #6
    3 Star Lounger
    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, francis
    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

  7. #7
    Plutonium Lounger
    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.

  8. #8
    Plutonium Lounger
    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.

Posting Permissions

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