Compare values in 3 different cells

[TQO23]

Hi.

Been going round and round in circles trying resolve this.

I have 3 cells and would like to detect a combination of values and return a different text string dependent on results. Each cell can be anything from 0 to about 10 and I am testing whether a cell is above 2.5 or not.

So if all 3 cells are above 2.5 I would say, for example, "go for it". If any one of the 3 cells is less than 2.5 then I might say "mmmmmmmmm". Then the other test is looking for any two of the other cells to be below 2.5 when I'd say "leave alone" or some other phrase.

I've got IF(AND etc working for the 3 cells above 2.5 but don't really know where to start for the other two tests becuase the formula would be horrendously long.

Any help appreciated.

Regards

Nigel
TQO23

 

[VoG]

Try like this

=IF(MIN(A1:A3)>2.5,"go for it",IF(AND(LARGE(A1:A3,1)>2.5,LARGE(A1:A3,2)>2.5),"mmm","leave alone))

 

[BiocideJ]

Or this...
=LOOKUP(COUNTIF(A1:A3,"<2.5"),{0,1,2},{"go for it","mmmmmm","leave alone"})

Although you didn't really specify what to do if all 3 values are <2.5 (I just have it say "leave alone" however, you can modify as follows if you need another special condition:
=LOOKUP(COUNTIF(A1:A3,"<2.5"),{0,1,2,3},{"go for it","mmmmmm","leave alone","REALLY leave alone"})

 

[TQO23]

Try like this

=IF(MIN(A1:A3)>2.5,"go for it",IF(AND(LARGE(A1:A3,1)>2.5,LARGE(A1:A3,2)>2.5),"mmm","leave alone))

Hi VoG and thanks for prompt response. Appreciated. Ok this looks promising but isn't quite working.

If i have a value over 2.5 in all 3 cells it says "go for it" which is correct. If however if I have ANY ONE of the cells with a value of 2 it says "leave alone" when it should say mmmmm. If I have two or more cells with a value below 2.5 it still says "leave alone.

Also how would I enter 3 different cells instead of A1:A3 as they are in cells B12, E12 and H12.

Many thanks again.

Nigel
TQO23

 

[VoG]

Try BiocideJ's formulas.

 

[TQO23]

Or this...
=LOOKUP(COUNTIF(A1:A3,"<2.5"),{0,1,2},{"go for it","mmmmmm","leave alone"})

Although you didn't really specify what to do if all 3 values are <2.5 (I just have it say "leave alone" however, you can modify as follows if you need another special condition:
=LOOKUP(COUNTIF(A1:A3,"<2.5"),{0,1,2,3},{"go for it","mmmmmm","leave alone","REALLY leave alone"})

Hi BiocideJ and thank you also for responding. This works for me as it doesn't matter if all 3 are below 2.5, 2 out of 3 is a no no decision for me.

So if I have 3 cells that are not in the same row, such as B12,E12,H12, what would the formula look like please?

I love MrExcel board members (well that's a bit deep but you know what I mean!)

regards

Nigel
TQO23

 

[BiocideJ]

Try like this

=IF(MIN(A1:A3)>2.5,"go for it",IF(AND(LARGE(A1:A3,1)>2.5,LARGE(A1:A3,2)>2.5),"mmm","leave alone))

For mine, it would have to change to
=LOOKUP(COUNTIF(B2,"<2.5")+COUNTIF(E2,"<2.5")+COUNTIF(H2,"<2.5"),{0,1,2},{"go for it","mmmmmm","leave alone"})

For his, you could enter simply replace every instance of A1:A3 with (B2,E2,H2)
i.e. LARGE((B2,E2,H2),2) {note the double parantheses}


EDIT: I hate that you can't do COUNTIF((B2,E2,H2),"<2.5") because that would be much simpler, but Excel doesn't like it with that formula unfortunately.

And we love you too. In a totally non-creepy way. :wink:

 

[TQO23]

Guys thank you so much for your prompt response and expertise.

BiocideJ's version is working perfectly.

Thank you, thank you.


Nigel
TQO23

 

[BiocideJ]

Guys thank you so much for your prompt response and expertise.

BiocideJ's version is working perfectly.

Thank you, thank you.


Nigel
TQO23

No problem. Thanks for replying.

 

[pkrishna]

The formula given by VoG seems to work well. I tried with the following data.

<TABLE style="WIDTH: 123pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=163 border=0><COLGROUP><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" span=3 width=33><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 25pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=33 height=17>2</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 25pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=33>3</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 25pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=33>4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>mmm</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">go for it</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">leave it</TD></TR></TBODY></TABLE>

If yuou want to use non-contiguous cells, such as B12, E12, H12, just enclose them inside parantheses - such as =LARGE((B12,E12,H12),1)