Avatar of Bill Golden
Bill Golden
Flag for United States of America asked on

Counting the number of cells with values and inserting the number of cells with values into a text cell.

I have 10 currency value cells.  Let's say for instance, A1 through A10.

I have a text cell that reads:

The average of the ___ years is $____________.

If only 5 of the cells (A1 through A10) have values totalling $500, then my text cell should read:

The average of the _5_  years is $_100_.
   
If 7 of the cells (A1 through A10) have values totalling $770, then my text cell should read:

The average of the _7_  years is $_110_.

Because this spreadsheet will be used in several different versions of Excel, I DO NOT want to use a script or add-in language.  I know it may be convoluted but it is the best I can do given the limitations I have been given.
Microsoft ExcelOffice ProductivitySpreadsheets

Avatar of undefined
Last Comment
Bill Golden

8/22/2022 - Mon
SOLUTION
Matthew Kelly

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Tom

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tom

Benefit of using COUNTIF and AVERAGEIF is that A1:A10 can contain 0 and will not
be counted
Bill Golden

ASKER
When I enter the formula and numbers in the appropriate range, All I get in the formula cell is #NAME?
Tom

Stange... Try with the included example average.xlsx
average.xlsx
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Matthew Kelly

#NAME means something is likely mispelled, can you post the exact contents of your cell that is giving that error?
Bill Golden

ASKER
Both answers were equally informative.