Excel Table Formula Help Needed

Copper Contributor

Good evening. I have an Excel table that was built by someone else that is nearly perfect, but I need it to do one more thing if the capability exists.

 

It is a table with columns using yes/no choices and dynamic range. Here is the formula he used to calculate moving numbers from cell input: =COUNTIFS(Table_Intake[Ordered on Weekend],"yes")

 

It works great to give me the count of yeses from the column, but I need it to display as an average of yeses, not a number.

 

How do I do that? I hope this makes sense.

 

If I can get it to provide an average of all entries in that column marked "yes", it will be perfect!

 

Thank you!

7 Replies

Hello

 

Please explain. What is the average of a "Yes"?

 

Hello,

To calculate an average, you need a numeric range of data.

Example: The average order price for each year or day.

 

Hi @SedonaDreaming,

 

Average is not possible in your data. but you can calculate %age of Yes/No.

 

file attach of your reference. maybe it's helpful for you.

 

Regard,

Naveen

If modify a bit and take into account only number of Admit dates in the period

=COUNTIFS(Table_Intake[Ordered on Weekend],"yes",Table_Intake[Admit Date],">="&$B$1,Table_Intake[Admit Date],"<="&$B$2)/COUNTIFS(Table_Intake[Admit Date],">="&$B$1,Table_Intake[Admit Date],"<="&$B$2)

Hi @Sergei Baklan ,

 

good idea.

 

thanks for sharing.

 

Regards,

Naveen

 

 

 

 

 

YES!!!! Naveen. Thank you so much! I love my table now!

Hi @SedonaDreaming

 

Happy to help you.

 

 

Regards,

Naveen