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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies