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

No_1	2	1			1.5
2	2	0			2
3		2			2
4	*	3			3

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Marlowwe]

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

A	B	C	D	E	F	G	h	i	j
PRICE	%	PRICE	%	Diff	PRICE	%	Diff	...	AVERAGE NO.
1		2			4
N/A		0			2
1		**			3

<tbody>
</tbody>

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 :

A	B	C	D	E	F
No_1	2	1			=avg_ifs

<tbody>
</tbody>

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:............