IF Statement calculating Average if you have numeric & text respon

[mellermj]

I have a worksheet that tracks responses by month for each quarter. What is
the best way to set up an equation that will calculate the average if the
responses can either be a percentage or Yes or No response?

 

[Bernard Liengme]

=AVERAGE(A1:A10) will ignore text
Are you asking how to include it? Do you want Yes=1 and No =0 in the
average?
If so:
=(SUM(G1:G6)+SUMPRODUCT(--(G1:G6="yes")))/COUNTA(G1:G6)
best wishes

 

[mellermj]

Thank you. That works perfectly.

=AVERAGE(A1:A10) will ignore text
Are you asking how to include it? Do you want Yes=1 and No =0 in the
average?
If so:
=(SUM(G1:G6)+SUMPRODUCT(--(G1:G6="yes")))/COUNTA(G1:G6)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email