PDA

View Full Version : [SOLVED] AVERAGEIF Specific cells ignoring #DIV/0!



rhysm144
11-11-2016, 03:27 PM
I am trying to average cells F3 and P3 (will use same formula for large ranges also) not as a range but as specific cells because I don't want the data in cells G3,H3 etc to be included in the average.

I also want this average to exclude any #DIV/0! results.

Thank you

mana
11-11-2016, 07:07 PM
please try this

=AGGREGATE(1,6,F3:P3) 

Aussiebear
11-12-2016, 02:19 PM
Just to add further information to Mana's post. Excel's AGGREGATE function only seems to apply to 2010 and above, and has the following syntax:

Aggregate(Function_Num, Options, Ref1) in its simplest form, with the Function_Num being from 1 to 19, and Options being from 1 to 7, and Ref1 is the Range to activate on.
Therefore the formula to use is :

=Aggregate(1,6,F3) where 1 is Average, and 6 is Ignore error values.

Paul_Hossler
11-12-2016, 03:18 PM
Not too sure that AGGREGATE would work for something like cells in row 3


From Help:



Type of Range:


The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.





As far as the original question:



I am trying to average cells F3 and P3 (will use same formula for large ranges also) not as a range but as specific cells because I don't want the data in cells G3,H3 etc to be included in the average.

I also want this average to exclude any #DIV/0! results.


taken as written, the average of F3 and P3 would be (F3+P3)/2

So I'm guessing that the intent was the cells in F3:P3

Do not understand how to handle something as vague as "I don't want the data in cells G3,H3 etc to be included in the average."

After the TBD cells above are "not included" then delete the #DIV/0!, or are "cells G3,H3 etc" the Div0 errors?

Aussiebear
11-12-2016, 04:19 PM
Perhaps you're right..

Paul_Hossler
11-13-2016, 08:44 AM
In any event, I'd just do a simple user defined function that takes the complete range, and one or more excluded ranges


17576





Option Explicit
Function SpecialAverage(AllData As Range, ParamArray ExcludeRanges()) As Double
Dim Num As Long, i As Long
Dim Tot As Double
Dim R As Range

'get the whole thing
For Each R In AllData.Cells
If IsNumeric(R.Value) Then
Num = Num + 1
Tot = Tot + R.Value
End If
Next

'back out the excluded
For i = LBound(ExcludeRanges) To UBound(ExcludeRanges)
If TypeOf ExcludeRanges(i) Is Range Then
For Each R In ExcludeRanges(i).Cells
If IsNumeric(R.Value) Then
Num = Num - 1
Tot = Tot - R.Value
End If
Next
End If
Next I

SpecialAverage = Tot / Num
End Function





17577

Aussiebear
11-13-2016, 02:09 PM
After re reading the initial post, and since I can't make up my mind just which words have the emphasis in the Op's post and they haven't bothered to follow up with any clarification, I'm going with this version.

=If(IsError(Average(F3,P3),'''',Average(F3,P3))

rhysm144
11-13-2016, 02:59 PM
Sorry for the delayed response. Thanks for the consideration I will try and paraphrase.

I want to average just cells F3 and P3. My issue is F3 contains #DIV/0!. As such I'm looking for a formula that would ignore the result in F3 and effectively just average P3. Although this scenario seems kind of silly because Im just getting a value I previously had I will be using the same principle for multiple more cells so I wanted to see if I could make it work.

Most of the solutions I could devise only allowed a function to ignore the error in a range ie F3:P3 but I dont want that because I dont want to include the in between cells in the average.

I simply want to average cells F3 and P3, if there is an error in one of the cells, omit it from the average.

Cheers

rhysm144
11-13-2016, 03:04 PM
After re reading the initial post, and since I can't make up my mind just which words have the emphasis in the Op's post and they haven't bothered to follow up with any clarification, I'm going with this version.

=If(IsError(Average(F3,P3),'''',Average(F3,P3))

Unfortunately this returned a 'too many arguments were entered for this function' error

Paul_Hossler
11-13-2016, 07:37 PM
A worksheet formula




=IF(AND(ISERROR($F$3),ISERROR($P$3)),0,IF(AND(ISERROR($F$3),NOT(ISERROR($P$ 3))),$P$3,IF(AND(NOT(ISERROR($F$3)),ISERROR($P$3)),$F$3,($F$3+$P$3)/2)))

Aussiebear
11-14-2016, 03:21 AM
Here's my slash and burn method:

=IfError((F3,P3)/2,0)

But before you go forth to slay dragons...... be aware it simply hides the problem of the divide by zero with a zero. You really should be correcting the earlier formula which provided the initial divide by zero.

@Paul, I'll bet you had a grin on your face when you posted that one of yours. :wot

Paul_Hossler
11-14-2016, 07:15 AM
Long deeply nested WS sheet formulas give me a headache as well as eyestrain

In VBA, this is what the formula does




Option Explicit

Function SpecialTwoNumberAverage(A As Variant, B As Variant) As Variant
If Not IsNumeric(A) And Not IsNumeric(B) Then
SpecialTwoNumberAverage = 0#
ElseIf IsNumeric(A) And Not IsNumeric(B) Then
SpecialTwoNumberAverage = A
ElseIf Not IsNumeric(A) And IsNumeric(B) Then
SpecialTwoNumberAverage = B
Else
SpecialTwoNumberAverage = (A + B) / 2#
End If
End Function

rhysm144
11-14-2016, 11:00 AM
Perfect, cheers guys those got the job done.