Home

Excel Table Formula Help Needed

%3CLINGO-SUB%20id%3D%22lingo-sub-333378%22%20slang%3D%22en-US%22%3EExcel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-333378%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20evening.%20I%20have%20an%20Excel%20table%20that%20was%20built%20by%20someone%20else%20that%20is%20nearly%20perfect%2C%20but%20I%20need%20it%20to%20do%20one%20more%20thing%20if%20the%20capability%20exists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20a%20table%20with%20columns%20using%20yes%2Fno%20choices%20and%20dynamic%20range.%20Here%20is%20the%20formula%20he%20used%20to%20calculate%20moving%20numbers%20from%20cell%20input%3A%20%3DCOUNTIFS(Table_Intake%5BOrdered%20on%20Weekend%5D%2C%22yes%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20great%20to%20give%20me%20the%20count%20of%20yeses%20from%20the%20column%2C%20but%20I%20need%20it%20to%20display%20as%20an%20average%20of%20yeses%2C%20not%20a%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20that%3F%20I%20hope%20this%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20can%20get%20it%20to%20provide%20an%20average%20of%20all%20entries%20in%20that%20column%20marked%20%22yes%22%2C%20it%20will%20be%20perfect!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-333378%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334903%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334903%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280825%22%20target%3D%22_blank%22%3E%40SedonaDreaming%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHappy%20to%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334697%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334697%22%20slang%3D%22en-US%22%3EYES!!!!%20Naveen.%20Thank%20you%20so%20much!%20I%20love%20my%20table%20now!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334222%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334222%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Egood%20idea.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20sharing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334183%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334183%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20modify%20a%20bit%20and%20take%20into%20account%20only%20number%20of%20Admit%20dates%20in%20the%20period%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIFS(Table_Intake%5BOrdered%20on%20Weekend%5D%2C%22yes%22%2CTable_Intake%5BAdmit%20Date%5D%2C%22%26gt%3B%3D%22%26amp%3B%24B%241%2CTable_Intake%5BAdmit%20Date%5D%2C%22%26lt%3B%3D%22%26amp%3B%24B%242)%2FCOUNTIFS(Table_Intake%5BAdmit%20Date%5D%2C%22%26gt%3B%3D%22%26amp%3B%24B%241%2CTable_Intake%5BAdmit%20Date%5D%2C%22%26lt%3B%3D%22%26amp%3B%24B%242)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334163%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334163%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280825%22%20target%3D%22_blank%22%3E%40SedonaDreaming%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAverage%20is%20not%20possible%20in%20your%20data.%20but%20you%20can%20calculate%20%25age%20of%20Yes%2FNo.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efile%20attach%20of%20your%20reference.%20maybe%20it's%20helpful%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegard%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-333486%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-333486%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3ETo%20calculate%20an%20average%2C%20you%20need%20a%20numeric%20range%20of%20data.%3C%2FP%3E%3CP%3EExample%3A%20The%20average%20order%20price%20for%20each%20year%20or%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-333470%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Table%20Formula%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-333470%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20explain.%20What%20is%20the%20average%20of%20a%20%22Yes%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
SedonaDreaming
New 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