If cell B40 contains a formula to AVERAGE the adjacent cells (B1:B38),
however, this range of cells are empty, then my result in cell B40 equals
#DIV/0!

Now, on the next worksheet, I would like cell C50 to reference B40, however,
if B40 does not give me a result (if it says #DIV/0!) then I would like for
cell C50 to give me a "WORD".

However, if any values have been entered into cells (B1:B38) and therefore
I do get a result in cell B40, then I would like it to also result in cell
C50 on the next work sheet.

My problem is if I use the Function IF in cell C50, I cannot say:
Logical_test: IF B40 < #DIV/0!
Value_if_true: =AVERAGE (B1:B38)
Value_if_false: "WORD"
How can I have cell C50 perform a formula only when cell B40 gives me a
result?--
Melody

=if(iserror(b40),"Word",average(b1:b38))
or
=if(iserror(b40),"Word",b40)

or drop B40 and just use:
=if(count(b1:b38)=0,"Word",average(b1:b38))




Melody wrote:

If cell B40 contains a formula to AVERAGE the adjacent cells (B1:B38),
however, this range of cells are empty, then my result in cell B40 equals
#DIV/0!

Now, on the next worksheet, I would like cell C50 to reference B40, however,
if B40 does not give me a result (if it says #DIV/0!) then I would like for
cell C50 to give me a "WORD".

However, if any values have been entered into cells (B1:B38) and therefore
I do get a result in cell B40, then I would like it to also result in cell
C50 on the next work sheet.

My problem is if I use the Function IF in cell C50, I cannot say:
Logical_test: IF B40 < #DIV/0!
Value_if_true: =AVERAGE (B1:B38)
Value_if_false: "WORD"
How can I have cell C50 perform a formula only when cell B40 gives me a
result?--
Melody

--

Dave Peterson

Try this:

=IF(ISNUMBER(Sheet1!B40),Sheet1!B40,"WORD")

--
Biff
Microsoft Excel MVP


"Melody" wrote in message
...
If cell B40 contains a formula to AVERAGE the adjacent cells (B1:B38),
however, this range of cells are empty, then my result in cell B40 equals
#DIV/0!

Now, on the next worksheet, I would like cell C50 to reference B40,
however,
if B40 does not give me a result (if it says #DIV/0!) then I would like
for
cell C50 to give me a "WORD".

However, if any values have been entered into cells (B1:B38) and
therefore
I do get a result in cell B40, then I would like it to also result in cell
C50 on the next work sheet.

My problem is if I use the Function IF in cell C50, I cannot say:
Logical_test: IF B40 < #DIV/0!
Value_if_true: =AVERAGE (B1:B38)
Value_if_false: "WORD"
How can I have cell C50 perform a formula only when cell B40 gives me a
result?--
Melody

Melody,
Usually error of this kind happens when the divisor is 0, in ur case since
no entry.
(If u mean that u want to keep track of those cells that dont have value -
that did not have an entry of any number or are 0, then dave and valko gave
u the answer.

"Melody" wrote:

If cell B40 contains a formula to AVERAGE the adjacent cells (B1:B38),
however, this range of cells are empty, then my result in cell B40 equals
#DIV/0!

Now, on the next worksheet, I would like cell C50 to reference B40, however,
if B40 does not give me a result (if it says #DIV/0!) then I would like for
cell C50 to give me a "WORD".

However, if any values have been entered into cells (B1:B38) and therefore
I do get a result in cell B40, then I would like it to also result in cell
C50 on the next work sheet.

My problem is if I use the Function IF in cell C50, I cannot say:
Logical_test: IF B40 < #DIV/0!
Value_if_true: =AVERAGE (B1:B38)
Value_if_false: "WORD"
How can I have cell C50 perform a formula only when cell B40 gives me a
result?--
Melody

Works perfect! Never would have know about "iferror" OR "ifnumber" unless you
told me. How can I get a list of these and define their uses.

Another question to perfect this work book: Based on my previous question,
If cell B40 is displaying an error: #div/0! and I wish for it to not print.
How do I put in the formula into B40: =average(B2:B38) PLUS have that cell
not print anything if there is an error. This is all remembering that there
are other cells on other work sheets that will be referencing this sheet1!B40.
--
Melody


"T. Valko" wrote:

Try this:

=IF(ISNUMBER(Sheet1!B40),Sheet1!B40,"WORD")

--
Biff
Microsoft Excel MVP


"Melody" wrote in message
...
If cell B40 contains a formula to AVERAGE the adjacent cells (B1:B38),
however, this range of cells are empty, then my result in cell B40 equals
#DIV/0!

Now, on the next worksheet, I would like cell C50 to reference B40,
however,
if B40 does not give me a result (if it says #DIV/0!) then I would like
for
cell C50 to give me a "WORD".

However, if any values have been entered into cells (B1:B38) and
therefore
I do get a result in cell B40, then I would like it to also result in cell
C50 on the next work sheet.

My problem is if I use the Function IF in cell C50, I cannot say:
Logical_test: IF B40 < #DIV/0!
Value_if_true: =AVERAGE (B1:B38)
Value_if_false: "WORD"
How can I have cell C50 perform a formula only when cell B40 gives me a
result?--
Melody

there are other cells on other work sheets that will
be referencing this sheet1!B40.

It depends on how this cell is being used as a reference. Try one of these:

=IF(COUNT(B2:B38),AVERAGE(B2:B38),0)
=IF(COUNT(B2:B38),AVERAGE(B2:B38),"")


--
Biff
Microsoft Excel MVP


"Melody" wrote in message
...
Works perfect! Never would have know about "iferror" OR "ifnumber" unless
you
told me. How can I get a list of these and define their uses.

Another question to perfect this work book: Based on my previous question,
If cell B40 is displaying an error: #div/0! and I wish for it to not
print.
How do I put in the formula into B40: =average(B2:B38) PLUS have that cell
not print anything if there is an error. This is all remembering that
there
are other cells on other work sheets that will be referencing this
sheet1!B40.
--
Melody


"T. Valko" wrote:

Try this:

=IF(ISNUMBER(Sheet1!B40),Sheet1!B40,"WORD")

--
Biff
Microsoft Excel MVP


"Melody" wrote in message
...
If cell B40 contains a formula to AVERAGE the adjacent cells (B1:B38),
however, this range of cells are empty, then my result in cell B40
equals
#DIV/0!

Now, on the next worksheet, I would like cell C50 to reference B40,
however,
if B40 does not give me a result (if it says #DIV/0!) then I would like
for
cell C50 to give me a "WORD".

However, if any values have been entered into cells (B1:B38) and
therefore
I do get a result in cell B40, then I would like it to also result in
cell
C50 on the next work sheet.

My problem is if I use the Function IF in cell C50, I cannot say:
Logical_test: IF B40 < #DIV/0!
Value_if_true: =AVERAGE (B1:B38)
Value_if_false: "WORD"
How can I have cell C50 perform a formula only when cell B40 gives me a
result?--
Melody