If then statements

[Ric]

Help please!

Trying to write differnet fomulas 1 for Text and 1 for numbers not to
appear if thet aren't in another cell from another worksheet in same
workbook.
Ex 1 is on the first line and this is the current formula
='Piazzo Ware C5"

If there isn't any text, then I the rest of the column should remain
empty and I don't wan tto see the formulas or #VALUE

Ex 2
=e6/e4
this is the current result and I would prefer just an empty cell
instead of "#VALUE!" all over my sheets

Thanks

 

[slarbie]

for Example 1:
the formula you provide doesn't look it would ever work, so assuming "Piazzo
Ware" is your other sheet's name and C5 is the cell you want to reference,
you could do this

=IF(ISERROR('Piazzo Ware'!C5),"",'Piazzo Ware'!C5)

Example 2:
=IF(E4=0,"",E6/E4)

OR

=IF(OR(E4=0,E4=""),"",E6/E4)

 

[Patrick Molloy]

=IF('Piazzo Ware'!C5="","",'Piazzo Ware'!C5)

=IF(ISERROR(E6/E4),"",E6/E4)

 

[Ric]

for Example 1:
the formula you provide doesn't look it would ever work, so assuming "Piazzo
Ware" is your other sheet's name and C5 is the cell you want to reference,
you could do this

=IF(ISERROR('Piazzo Ware'!C5),"",'Piazzo Ware'!C5)

Example 2:
=IF(E4=0,"",E6/E4)  

OR

=IF(OR(E4=0,E4=""),"",E6/E4)









- Show quoted text -

Thanks this formula works IF(ISERROR('Piazzo Ware'! C30),"", 'Piazzo
Ware'!C30) however I have an additional question;
how do I make this look in C74 and if there isn't a value there go to
C63, no value, go to C52, no valur look into C41, agin if there isn't
a value then go to C30?

Thanks

 

[JP Ronse]

Hi All,

I was trying to help Ric but got myself blocked on non adjacent cells in
array functions.

Having

#DIV/0!
#N/A
5
#N/A

(empty cell)

(in C27:C31) then

={CHOOSE(LARGE(ISNUMBER(C27:C31)*({1;2;3;4;5});1);C27;C28;C29;C30;C31)}

returns 5, which is correct because there is only one value in the range.

Is there a way to point to non adjacent cells, e.g; C27, C29, C3, ...

Wkr,

JP

for Example 1:
the formula you provide doesn't look it would ever work, so assuming
"Piazzo
Ware" is your other sheet's name and C5 is the cell you want to reference,
you could do this

=IF(ISERROR('Piazzo Ware'!C5),"",'Piazzo Ware'!C5)

Example 2:
=IF(E4=0,"",E6/E4)

OR

=IF(OR(E4=0,E4=""),"",E6/E4)









- Show quoted text -

Thanks this formula works IF(ISERROR('Piazzo Ware'! C30),"", 'Piazzo
Ware'!C30) however I have an additional question;
how do I make this look in C74 and if there isn't a value there go to
C63, no value, go to C52, no valur look into C41, agin if there isn't
a value then go to C30?

Thanks

 

[JP Ronse]

Hi All,

Maybe this question became hidden as a replied question. Allow me to post it
again. Perhaps I was not clear enough ...

Having in C2731 following data:

#DIV/0! 30
#N/A 41
5 52
#N/A 63
<empty cell> 74

The function below returns 52.

={CHOOSE(LARGE(ISNUMBER(C27:C31)*({1;2;3;4;5});1);D27;D28;D29;D30;D31)}

(array formula, ctrl-shift-enter)

The ISNUMBER part finds '5' as numeric (true) => multiplying with the array
{1;2;3;4;5}) & large [1] returns 3.

CHOOSEI finds 52 (D29).

The problem is that the data is not in adjacent cells, e.g. : C27/D27,
C29/D29, C31/D31, C33/D33, C35/D35.

I tried already ... ISNUMBER({C27; C29; C31; C33; C35}), but this does not
work. Something like

({isnumber(C27);isnumber(C29)}) doesn't work either.

Is there a way to work with non-adjacent cells?

Wkr,

JP