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

1. ## 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?

2. ## 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)

3. ## 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. ## 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)?

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

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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!

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

#### 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