If formulae

[PBA-Andrew Collins]

Hi,

I would like to use an If formula to automatically enter the values "Z" or "S" depending upon the values in two cells.

Now I have 7 columns which I need to think about. Working from left to right:

Column F is where I would like to insert the "IF" formula to deduce whether the letter Z or S are used.
Columns G and H are fixed values.
Column I finds the difference between the adjacent cells in columns G and H.
Columns J and K are fixed values.
Column L finds the difference between the adjacenet cells in columns J and K.

What I would like to do, is enter an "IF" formula (column F) that when the values in both Columns I and L are individually ≤ 1.2, column F displays the letter S, and if both columns I and L are individually >1.2 then column F displays the letter Z.

Thanks for any help in advance.

All the best,

Andrew

[Punnam]

Hi

Try this
=if(or(I1<=1.2,L1<=1.2),"S","Z")
Punnam

[oeldere]

Try this (untested)

F1=if(and(i1<="1.2",L1<="1.2"),"S","Z")

[PBA-Andrew Collins]

Hi All,

Thanks for your help.

Both formulae work for setting it to S.

=if(or(I1<=1.2,L1<=1.2),"S","Z")
=if(and(i1<="1.2",L1<="1.2"),"S","Z")

But what I would like to do is perhaps something along the lines of this:

=IF(((I1<="1.2",L1<="1.2"),"S")OR((I1>"1.2",L1>"1.2"),"Z"))

Not sure if that would work...

Thanks again
Andrew

[estige]

Hi, you didn't specify expected result if neither both values are <=1.2 nor >1.2?
=IF(AND(I1<=1.2,L1<=1.2),"S",IF(AND(I1>1.2,L1>1.2),"Z"))

[Punnam]

Try t
this
=IF(OR(AND(I2<=1.2,L2<=1.2),AND(I2>1.2,L2>1.2)),"S","Z")

Punnam

[PBA-Andrew Collins]

Thanks Estige,

=IF(AND(I1<=1.2,L1<=1.2),"S",IF(AND(I1>1.2,L1>1.2),"Z"))

So that formula works if like you say both cells are the same value, i.e <-1.2 or >1.2.

But as you mentioned what if they are both neither <=1.2 nor >1.2? So if cell I1 is 1.4 and cell L1 is 1.1, then I would like it to say S. Likewise if cell I1 reads 1.1 and cell L1 reads 1.4 then I would like it to say S also.

Thanks again.

Andrew

[boopathiraja]

Try

Please Login or Register  to view this content.

[estige]

So Z if both >1.2, else S? Then a small adjustment to Oeldere's formula:
=if(and(i1>1.2,L1>1.2),"Z","S")

[PBA-Andrew Collins]

Thanks Estige thanks works.

[Punnam]

@ PBA-Andrew Collin

Have u checked post 6

Punnam