• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel average excluding #DIV/0! but not in range

V

Vpremakumar

New Member
Hey guys,

Pls help me finding the solutions for below.


Name Number

A 10

B #DIV/0!

C 12

D #DIV/0!

E 20

F 13

G 18


I've to find out the average for names B,D,F,G using excel formula. Thanks
 
Can you replace the formulas in B & D with something to catch the errors and put a 0

eg: =IFERROR(existing formula,0)


Then do an average as normal =Average(B, D, F, G)
 
Hey, I'm looking for formula that it shouldn't consider error value itself to calculate average.With your formula it shows 0 instead of #DIV/0! but it'll consider 0 for average calculation.Is any other formula to calculate average excluding #DIV/0! and data are not in range. Please suggest. Thanks!
 
@xld

What does the ="B" part do?


I think you're okay using the slightly smaller array formula:

=AVERAGE(IF(ISNUMBER(B2:B10),B2:B10))
 
Looking at my formula, let's start at the inside and work out. The first function is an IF function, checking each cell within the range B2:B10 to see if it's a number. If it is, pass it on (True_arguement), otherwise do nothing (will return FALSE). Visually, if we start with this:


1

2

3

4

#DIV/0

John

4

5


The formula will convert to this:

1

2

3

4

FALSE

FALSE

4

5


The outer function is AVERAGE, which takes the converted section and averages it. The function ignores logicals/text, so we just get the average of 1,2,3,4,4,5. This is the desired outcome.


The formula needs to be confirmed as an array formula (use Ctrl+Shift+Enter to confirm, not just Enter) because we want the IF function to look at each cell with B2:B10, not treat the whole range as a single entity.
 
That's okay. Simply the formula to look at the entire range (containing numbers, text, errors) and it will only average the numbers.
 
You must log in or register to reply here.
Back
Top