Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

Thread: SUMPRODUCT - ISNUMBER combination

  1. #1
    Join Date
    10th May 2005
    Posts
    65

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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 at 04: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.

  3. #3
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: SUMPRODUCT - ISNUMBER combination

    Why not just use

    =AVERAGE(B2:B6)
    HTH

    Bob

  4. #4
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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 at 04: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.

  5. #5
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    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

  6. #6
    Join Date
    10th May 2005
    Posts
    65

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    10th May 2005
    Posts
    65

    Re: SUMPRODUCT - ISNUMBER combination

    Hi,

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

    Thank you

    Chandra
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th January 2006
    Posts
    524

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    10th May 2005
    Posts
    65

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,295

    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!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. IsNumber is not working
    By juju in forum EXCEL HELP
    Replies: 3
    Last Post: May 29th, 2006, 00:48
  2. OR with ISNUMBER
    By rhc in forum EXCEL HELP
    Replies: 2
    Last Post: April 21st, 2006, 01:29
  3. If/Isnumber/istext syntax
    By Loiner in forum EXCEL HELP
    Replies: 2
    Last Post: April 3rd, 2005, 16:39
  4. Isnumber & Time Formula
    By EDR in forum EXCEL HELP
    Replies: 4
    Last Post: January 16th, 2005, 04:11
  5. VBA for ISNUMBER function
    By Diego_Garcia in forum EXCEL HELP
    Replies: 4
    Last Post: November 28th, 2004, 10:30

Bookmarks

Posting Permissions

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