Announcement

Collapse
No announcement yet.

SUMPRODUCT - ISNUMBER combination

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUMPRODUCT - ISNUMBER combination

    Hi,

    I am trying to do a weighted average of two items. But I want to omit items that have a non-numeric entry in the fields. I am able to do a sum of the list using the conditions (A8 in the sample). but, the weighted average is not working (cell B8).

    Extra step is the checklist for me.

    I appreciate any help I can get or any other startegy that would work better.

    Thank you

    Chandra
    Attached Files

  • #2
    Re: SUMPRODUCT - ISNUMBER combination

    If your actual data contains no zeros (that should be included in teh average) then change B2 to
    =SUM(B2:B6)/COUNTIF(B2:B6,">0")

    No need for the extra steps nor for the array formula.
    Last edited by thomach; February 9th, 2006, 05:18. Reason: modify
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Re: SUMPRODUCT - ISNUMBER combination

      Why not just use

      =AVERAGE(B2:B6)
      HTH

      Bob

      Comment


      • #4
        Re: SUMPRODUCT - ISNUMBER combination

        I wasn't thinking fully. Better is simply
        =SUM(B2:B6)/COUNT(B2:B6)

        COUNT will ignore text and empty cells. It counts only numbers. Similarly, SUM doesn't care about (ignores) text entries.

        EDIT. Bob has it right. I was thinking AVERAGE didn't like text strings, but didn't check myself. Obviously I was wrong.
        Last edited by thomach; February 9th, 2006, 05:26. Reason: Bow to Bob
        Best Regards,
        Tom
        ---------------------------
        Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

        Comment


        • #5
          Re: SUMPRODUCT - ISNUMBER combination

          BTW, you don't need to array enter a SUMPRODUCT formula. For instance, in A8 you can simply use

          =SUMPRODUCT(--(ISNUMBER(B2:B6)),A2:A6)
          HTH

          Bob

          Comment


          • #6
            Re: SUMPRODUCT - ISNUMBER combination

            Hi,

            Thank you for everybody who posted in response to my question.

            Yes, definitely the suggestions were much simpler than the one I had used.

            But my main issue is getting a weighted average of the first two columns - with non-numberic characters in the second column (b2:b6).

            The sumproduct seems to give error because of non-numeric values. Maybe it was not clear with my using 1 for all the values in a2:a6. Now, I have changed the numbers.

            Hope this makes it a bit more clearer than before.

            Thank you for your help.

            Chandra

            Comment


            • #7
              Re: SUMPRODUCT - ISNUMBER combination

              Hi,

              My apologies. Forgot to post the file. B8 is what I have issues with.

              Thank you

              Chandra
              Attached Files

              Comment


              • #8
                Re: SUMPRODUCT - ISNUMBER combination

                Hi,

                I know another way of solving the problem. Review the attachment and let me know if you get your desired result.

                Biz
                Attached Files

                Comment


                • #9
                  Re: SUMPRODUCT - ISNUMBER combination

                  Biz,

                  Thank you for the details.

                  The sum of Column B is good but the sum of column A is including row 6 which should be eliminated from the SUM of Column A (because the row in column B has a non-numeric charcater).

                  My desired result is in D8 and the column D does the same thing with an extra step added. But I would like to get this done in 1 step - using columns A and B only.

                  Hope this makes sense.

                  Thank you,

                  Biz

                  Comment


                  • #10
                    Re: SUMPRODUCT - ISNUMBER combination

                    Try...

                    =SUM(IF(ISNUMBER(B2:B6),(A2:A6)*(B2:B6)))/SUM(IF(ISNUMBER(B2:B6),A2:A6))

                    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

                    Hope this helps!

                    Comment


                    • #11
                      Re: SUMPRODUCT - ISNUMBER combination

                      You didn't read my previous reply. All that you need is

                      Code:
                      =SUMPRODUCT(--(ISNUMBER(B2:B6)),A2:A6,B2:B6)/SUMPRODUCT(--(ISNUMBER(B2:B6)),A2:A6)
                      HTH

                      Bob

                      Comment


                      • #12
                        Re: SUMPRODUCT - ISNUMBER combination

                        Originally posted by Bob Phillips
                        You didn't read my previous reply. All that you need is

                        Code:
                        =SUMPRODUCT(--(ISNUMBER(B2:B6)),A2:A6,B2:B6)/SUMPRODUCT(--(ISNUMBER(B2:B6)),A2:A6)
                        Hi Bob,

                        I tested your formula it works. My know on Sumproducts is not that strong but it looks like awesome function then I need to do more work on.

                        Biz

                        Comment


                        • #13
                          Re: SUMPRODUCT - ISNUMBER combination

                          Domenic,

                          That did it. It worked as I wanted it to. I tried some variations and seems OK. I will try some more later in the evening.

                          Thank you all for helping me.

                          Chandra

                          Comment


                          • #14
                            Re: SUMPRODUCT - ISNUMBER combination

                            Originally posted by Bob Phillips
                            You didn't read my previous reply...
                            Sorry Bob! I too didn't read the previous posts carefully. Otherwise, I wouldn't have offered my formula, but rather point to your formula.

                            Comment


                            • #15
                              Re: SUMPRODUCT - ISNUMBER combination

                              Bob,

                              The replies came too fast and I missed it and saw only Domenic's response. My apologies and it works very well too. So, there are few options to work with now.

                              Thank you once again everybody.

                              Chandra

                              Comment

                              Working...
                              X