Average exclude text, 0, and blank cels - Function or macro

[Marlowwe]

Hi,

I would like to ask you for help with macro or custom function for this.

Calculate average for n cell and exclude 0, blank cells, -, text. Every time is different amount of calculated cells.

How to write macro or custom function.


My try is here but it is only for 2 calculating cell and Macro doesn´t work:

Sub Averageee()


Dim myValue As Range
Dim myValuee As Range


Set myValue = Application.InputBox("Cell No.1", Type:=8)
Set myValuee = Application.InputBox("Cell No.2", Type:=8)


ActiveCell.Formula = "=(SUM(SUMIF(" & myValue & ",<>0),SUMIF(" & myValuee & ",<>0)))/SUM(COUNTIF(" & myValue & ",>0),COUNTIF(" & myValuee & ",>0))"
ActiveCell.Select


End Sub

Thank you for your help.

 

[FormR]

Hi, why not just use AVERAGEIF() for example.

Excel 2013/2016
	B	C	D	E	F
8	2	1			1.5
9	2	0			2
10		2			2
11	*	3			3
Sheet1

Cell Formulas
Range	Formula
F8	=AVERAGEIF(B8:E8,"<>0")

 

[Vinoth_Shob]

Use the below code for average if function

Function avg_ifs()
Dim k As Long
For k = 2 To 4
Sheet2.Range("F" & k).Value = WorksheetFunction.AverageIfs(Sheet2.Range("B" & k & ":" & "E" & k), Sheet2.Range("B" & k & ":" & "E" & k), ">0")
Next
End Function

Data in sheet 2
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 64"]No_1[/TD]
[TD="class: xl70, width: 64"]2[/TD]
[TD="class: xl70, width: 64"]1[/TD]
[TD="class: xl70, width: 64"] [/TD]
[TD="class: xl70, width: 64"] [/TD]
[TD="class: xl71, width: 64"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]2[/TD]
[TD="class: xl63, width: 64"]2[/TD]
[TD="class: xl63, width: 64"]0[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl65, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]3[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"]2[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl65, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]4[/TD]
[TD="class: xl72, width: 64"]*[/TD]
[TD="class: xl67, width: 64"]3[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl68, width: 64"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marlowwe]

Hi, Oh ok, Thank you. That is possible If I have a numbers in next columns. If I have for example:





[TABLE="class: outer_border, width: 800, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]h[/TD]
[TD="align: center"]i[/TD]
[TD="align: center"]j[/TD]
[/TR]
[TR]
[TD="align: center"]PRICE[/TD]
[TD="align: center"]%[/TD]
[TD="align: center"]PRICE[/TD]
[TD="align: center"]%[/TD]
[TD="align: center"]Diff[/TD]
[TD="align: center"]PRICE[/TD]
[TD="align: center"]%[/TD]
[TD="align: center"]Diff[/TD]
[TD="align: center"]...[/TD]
[TD]AVERAGE NO.[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]**[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Excel 2013/2016[TR="class: outer_border, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]

Sheet1​

Hi, why not just use AVERAGEIF() for example......

 

[Marlowwe]

Hi, thank you. But I dont know How to use this .

If it is like this :

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]No_1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]=avg_ifs[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The function dont work.

Use the below code for average if function......

 

[Jonmo1]

Try averageifS

=AVERAGEIFS(A2:I2,A$1:I$1,"Price",A2:I2,"<>0")

 

[Marlowwe]

I could be like this??????????

Sub AVERAGE_insertToActiveCell()
Dim myType As String
Dim Rng As Range
Dim Rngg As Range

Set Rng = Application.InputBox("Area for number calculation:", Type:=8)
Set Rngg = Application.InputBox("Area for name of column:", Type:=8)

myType = Application.InputBox("Name of column:", Type:=8)

'=AVERAGEIFS(Y15:AH15;$Y$9:$AH$9;"Price";Y15:AH15;"<>0")
ActiveCell.Formula = "=AVERAGEIFS(" & Rng.Address & "," & Rngg.Address & "," & myType & "," & Rng.Address & ",<>0)"
ActiveCell.Select


End Sub

But something is wrong and show error.

Try averageifS

=AVERAGEIFS(A2:I2,A$1:I$1,"Price",A2:I2,"<>0")

 

[Jonmo1]

Works for me..

Book1
	A	B	C	D	E	F	G	H	I	J
1	PRICE	%	PRICE	%	Diff	PRICE	%	Diff	...	AVERAGE NO.
2	1		2			4				2.333333333
3	N/A		0			2				2
4	1		**			3				2
Sheet1

Cell Formulas
Range	Formula
J2	=AVERAGEIFS(A2:I2,A$1:I$1,"Price",A2:I2,"<>0")
J3	=AVERAGEIFS(A3:I3,A$1:I$1,"Price",A3:I3,"<>0")
J4	=AVERAGEIFS(A4:I4,A$1:I$1,"Price",A4:I4,"<>0")

 

[FormR]

But something is wrong and show error.

It's not clear what ranges you are selecting with those inputboxes, but at a guess try amending this line:

ActiveCell.Formula = "=AVERAGEIFS(" & Rng.Address & "," & Rngg.Address & "," & myType & "," & Rng.Address & ",<>0)"

To:

ActiveCell.Formula = "=AVERAGEIFS(" & Rng.Address & "," & Rngg.Address & ",""" & myType & """," & Rng.Address & ",""<>0"")"

 

[Marlowwe]

Hey, Thanks a lot.

Here is my fina macro:

Sub AVERAGE_insertToActiveCell()
Dim myType As String
Dim Rng As Range
Dim Rngg As Range


Set Rng = Application.InputBox("Select area of numbers (in a row) for calculation:", Type:=8)
Set Rngg = Application.InputBox("Select Header area:" & vbCrLf & _
"The area must be the same as Selection of numebers from 1st step" & vbCrLf & _
"Select only one row, not merged cells", Type:=8)


myType = Application.InputBox("Select cell what column should be calculated:", Type:=8)


'ActiveCell.Formula = "=subtotal(" & myType & "," & Rng.Address & ")"
'=AVERAGEIFS(Y15:AH15;$Y$9:$AH$9;"Price";Y15:AH15;"<>0")
ActiveCell.Formula = "=AVERAGEIFS(" & Rng.Address & "," & Rngg.Address & ",""" & myType & """," & Rng.Address & ",""<>0"")"
ActiveCell.Select


End Sub

It's not clear what ranges you are selecting with those inputboxes, but at a guess try amending this line:............