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

Bill Golden
Bill Golden used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Put this in A11. Tested in Excel 2007/2010.

="The average of the _" & COUNT(A1:A10) & "_  years is $_" & AVERAGE(A1:A10) & "_."

Open in new window

Microsoft ISV Partner
Commented:
="The average of the "& COUNTIF(A1:A10;"> 0") & " years is $ " & AVERAGEIF(A1:A10;">0")

or US

="The average of the "& COUNTIF(A1:A10,"> 0") & " years is $ " & AVERAGEIF(A1:A10,">0")
TomMicrosoft ISV Partner

Commented:
Benefit of using COUNTIF and AVERAGEIF is that A1:A10 can contain 0 and will not
be counted
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Bill GoldenExecutive Managing Member

Author

Commented:
When I enter the formula and numbers in the appropriate range, All I get in the formula cell is #NAME?
TomMicrosoft ISV Partner

Commented:
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?
Bill GoldenExecutive Managing Member

Author

Commented:
Both answers were equally informative.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial