Substituting #N/A

[SamuelT]

Hi all,

I have a worksheet that records the status of various projects. The
formula is dependent on another worksheet, and some of my colleagues
have not filled this in, which then returns an #N/A in my project
status cell. I would like for this not to be presented and instead
simply have a message saying 'None Recorded' (or something along those
lines).

However, because I have a formula already in the cell, I'm not sure how
to do this. The formula is as below:

=IF(R105=E129,D129,IF(R103<E134,D133,IF(R103>E136,D137,D135)))

Can anyone help me to accomplish my goal?

TIA,

SamuelT

 

[Ron Rosenfeld]

=IF(R105=E129,D129,IF(R103<E134,D133,IF(R103>E136,D137,D135)))

One way:

=IF(ISNA(IF(R105=E129,D129,IF(R103<E134,D133,IF(R103>E136,D137,D135)))),"None
Recorded",IF(R105=E129,D129,IF(R103<E134,D133,IF(R103>E136,D137,D135))))


--ron

 

[Bob Phillips]

That formula in itself does not return #N/A, but it seems that one of the
referenced cells holds #N/A

The usual way is to test the formula like

=IF(ISNA(formula),"Not recorded"),formula)

but as I say, I don't think that is your problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[SamuelT]

That works a treat. Many thanks!

 

[SamuelT]

That works a treat. Many thanks!