Putting a different character in a cell

B

blausen

Okay here is the problem: We are setting up an Excel worksheet to figure
out productivity of employees. I am able to set up a cell to average
another range of cells. Example would be that Cell J3 will average
Cells C3 - I3 (=AVERAGE(C3:I3)). Now I have two questions... Is there a
way to make Excel put a character in Cell J3 where the formula is
instead of a blank cell OR even better I would like to create another
worksheet that will take MULTIPLE cells linked to the first worksheet
with the average formula in it to then do -another- average of the
cells if there is information in the cells. At the moment I am able to
average only the first set of cells in one worksheet but can not
average the linked cells of the average formula in another worksheet.
Is there a way to do either of these situations in Excel? Please help
if possible. Thanks
 
R

RagDyer

With the 'Average' formula that you posted, it's impossible for the cell
containing the formula (J3) to be blank, so I don't think I really
understand your question.

Using your formula, you will have either a number or an error message
returned if all the cells are blank.

You can however, expand on the formula to return a "character" instead of
the error message (#DIV/0!):

=IF(ISERR(AVERAGE(C3:I3)),"Avg. NOT Avail.",AVERAGE(C3:I3))

If I understand the second part of your question, you're copying J3 down
Column J, and you wish to average the returns from those 'Average' formulas
on another sheet, as long as those formulas *do* return a value.

If you would use the average formula suggested above for Column J, then this
formula, on Sheet2, would "average your averages" on Sheet1:

=AVERAGE(Sheet1!J3:J20)

If this doesn't address your problems, post back with a more descriptive
question.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
B

blausen

I wish to express my gratitude for your assistance in this issue. I have
tried the formula that you have suggested and it works wonderfully for
placing a character or characters in the cell until there is data for
the cell to average. I really appreciate your assistance!
 
Top