IF? - Lookup a number

[mypig7]

I have 4 cells. A2,A3,A4,A5
3 of the cells contain the word "Fault" and 1 contains a number e.g. 55 (this number can vary though)
I want to enter a forumla (say in A1) so that if one of the cells in the range A2:A5 contains a number that number appears in A1.

 

[NBVC]

Is it only one cell that will contain a number?

If yes, try: =LOOKUP(9.9999999999E+307,A2:A5)

 

[bakerman]

This works, in cell B2 Paste the following formula
=SMALL($A$2:$A$5,0+1)
Then in cell A1 enter the formula A1 = C3

 

[mypig7]

thanks v much

Thanks.

If I get #NUM! appearing because no number appears is there any way I can get the letters FAULT to appear instead of #NUm!

Thanks

 

[NBVC]

=If(isnumber(formula),formula,"FAULT)

replace formula with the actual formula you used

 

[Scott Huish]

Perhaps:

=IF(COUNT(A2:A5)=1,SUM(A2:A5),"FAULT")