Bill Golden

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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

average.xlsx

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

ASKER

Both answers were equally informative.

be counted