+ Reply to Thread
Results 1 to 11 of 11

How to calculate the weighted average without including #N/A

  1. #1
    Registered User
    Join Date
    04-29-2014
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    11

    How to calculate the weighted average without including #N/A

    Dears,
    I have cloulmns for prices & others for volume (lets now focus on two columns E&O (E10:15 & O10:O15) , wherein E for prices & O for volume, I need to calculate the weighted average prices of the last 6 months but the issue each column has #N/A, my question is how to calculate the weighted average without including #N/A?
    please help me.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,685

    Re: How to calculate the weighted average without including #N/A

    What results are you expecting and where?

    Excel 2016 (Windows) 32 bit
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    8
    FOB
    Qty
    9
    Month
    CN
    EURO ZONE
    SA
    BR
    EG
    IN
    MY
    TH
    TR
    ZA
    CN
    EURO ZONE
    SA
    BR
    EG
    IN
    MY
    TH
    TR
    ZA
    10
    Nov-18
    #N/A
    1015
    1099
    #N/A
    1410
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    113
    2053
    #N/A
    200
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    11
    Dec-18
    #N/A
    953
    1075
    1053
    1410
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    55
    1382
    149
    200
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    12
    Jan-19
    #N/A
    1016
    1047
    1003
    1410
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    66
    2654
    99
    200
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    13
    Feb-19
    #N/A
    1420
    1056
    #N/A
    1410
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    92
    1183
    #N/A
    200
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    14
    Mar-19
    1000
    1000
    1000
    1000
    1000
    1000
    1000
    1000
    1000
    1000
    #N/A
    100
    1183
    #N/A
    200
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    15
    Apr-19
    1200
    1200
    1200
    1200
    1200
    1200
    1200
    1200
    1200
    1200
    #N/A
    100
    #REF!
    #REF!
    200
    #REF!
    #REF!
    #REF!
    #REF!
    #REF!
    16
    6 Months WA
    #N/A
    1112
    #REF!
    #N/A
    1307
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    Sheet: LL (3)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    04-29-2014
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to calculate the weighted average without including #N/A

    Thanks for reply, lets for example look at column H, it is the prices in Brazil and the volume is shown in column R. My file is large and i did Vlook to get the above data and the results show that i have no sales in some of the months and instead it gives me #NA. This is why I can not get the weighted average.
    I need the result in cell H16 please

    I hope my answer is clear.

  4. #4
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    4,816

    Re: How to calculate the weighted average without including #N/A

    because the formulas are in areas I have no access to, I cannot get the formula to work exactly but, if your issue is getting rid of the #N/A have you tried adding IFERROR(your if/or/vlookup formula),"") to your formula, or if you want those to appear as zero then IFERROR(your formula),0)?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam C

  5. #5
    Registered User
    Join Date
    04-29-2014
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to calculate the weighted average without including #N/A

    Book2.xlsx

    my file has over 50 thousands rows and it is interactive by using Vlookup, so my goal is not to get rid of the #N/A but i need formula that can give me weighted average even if some cells have #N/A, i included simplified file
    Last edited by AliGW; 03-18-2019 at 01:50 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,685

    Re: How to calculate the weighted average without including #N/A

    What results are you expecting to see in the new sample file and why? How do you want the weighted average calculating? What are the weightings? You are not actually answering the key questions.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,685

    Re: How to calculate the weighted average without including #N/A

    Maybe try this:

    =SUM(IF(ISNUMBER($E$6:$E$11),$E$6:$E$11*$F$6:$F$11))/SUM(IF(ISNUMBER($E$6:$E$11),$F$6:$F$11))

    .. .confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  8. #8
    Registered User
    Join Date
    04-29-2014
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to calculate the weighted average without including #N/A

    i need to get my weighted average prices for the last six months ( each month i sell quantity with different price) by the end of the cycle ( 6 months) i need to see the average prices of my sales.
    hope now it is clear

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,685

    Re: How to calculate the weighted average without including #N/A

    No, you still haven't answered the questions. Did you try my suggestion in post #7? It's a guess because you have consistently failed to answer my questions.

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  10. #10
    Registered User
    Join Date
    04-29-2014
    Location
    saudi
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to calculate the weighted average without including #N/A

    Thanks AliGW for your support and help.
    your suggestion in post 7 works perfectly.
    sorry for bothering you.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,685

    Re: How to calculate the weighted average without including #N/A

    No bother, except it would have been quicker if you had actually answered the questions!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1