how do i use IF to substitute for another cell?

[Tim]

Hi,

i have a stock sheet, and the cost price comes from a cell which is a
average. in some months though we do not have all the products delivered, so
the average is either #value or #ref. In another cell though I always have
what we last paid for the product, what would be the formula so that if one
cell does bring up either #VALUE or #REF then i could put in the start cell?

Much thanks if you can help, I am sure it is very simple.

 

[p45cal]

try
=IF(ISERROR(I29),G27,I29)

where
=IF(ISERROR(average cell),start cell,average cell)

 

[Gord Dibben]

Without seeing the formula all I can do is show an example of trapping for
errors.

=IF(ISERROR(A1+B1),C3,A1+B1)

In 2007 version use =IFERROR(A1+B1,C3)

ISERROR wil trap all errors so may not be desirable.

Maybe trap for a cell or cells being blank.

I don't know why you would get a #REF! just from missing data but again, I
can't see your formula.


Gord Dibben MS Excel MVP