Link to home
Start Free TrialLog in
Avatar of Bill Golden
Bill GoldenFlag 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.
SOLUTION
Avatar of Matthew Kelly
Matthew Kelly
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Benefit of using COUNTIF and AVERAGEIF is that A1:A10 can contain 0 and will not
be counted
Avatar of Bill Golden

ASKER

When I enter the formula and numbers in the appropriate range, All I get in the formula cell is #NAME?
Stange... Try with the included example average.xlsx
average.xlsx
#NAME means something is likely mispelled, can you post the exact contents of your cell that is giving that error?
Both answers were equally informative.