Logical Test

  • Thread starter Thread starter Melody
  • Start date Start date
M

Melody

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,
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.
 
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.
 
there are other cells on other work sheets that will
be referencing this sheet1!B40.
Click to expand...

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),"")
 
Back
Top