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

 

[TQO23]

OK this is embarrassing BiocideJ.

Firstly it works perfectly, in my Excel 2007 version BUT what I didn't realise is that Google Docs doesn't seem to like the =LOOKUP function!

I'm using Google Docs as I share it with a couple of other guys.

When I enter the formula I get #NAME? in the cell and when I hover over it I get an error message error: Unknown Function Name LOOKUP

Is there a workaround for Google Docs that you know of please?

thanks

Nigel

 

[BiocideJ]

I have to agree. VoG's appears to give the same results as mine.
Although I am submitting a new version of mine as well that 'may' provide better results although I am not familiar with Google Doc's accepted formulas..

=HLOOKUP(COUNTIF(B2,"<2.5")+COUNTIF(E2,"<2.5")+COUNTIF(H2,"<2.5"),{0,1,2;"go for it","mmmmmm","leave alone"},2,FALSE)

Also, if that isn't working, here is VoG's amended formula...
=IF(MIN((B2,E2,H2))>2.5,"go for it",IF(AND(LARGE((B2,E2,H2),1)>2.5,LARGE((B2,E2,H2),2)>2.5),"mmm","leave alone"))


AND... another possibility just to round things out.
=INDEX({"go for it","mmmmmm","leave alone"},1,(COUNTIF(B2,"<2.5")+COUNTIF(E2,"<2.5")+COUNTIF(H2,"<2.5")+1))

Surely one of the three above should work.

 

[TQO23]

Thank you BiocideJ.

Will test later tonight and report back tomorrow. Thanks for all the suggestions.

Nigel

 

[TQO23]

I have to agree. VoG's appears to give the same results as mine.
Although I am submitting a new version of mine as well that 'may' provide better results although I am not familiar with Google Doc's accepted formulas..

=HLOOKUP(COUNTIF(B2,"<2.5")+COUNTIF(E2,"<2.5")+COUNTIF(H2,"<2.5"),{0,1,2;"go for it","mmmmmm","leave alone"},2,FALSE)

Also, if that isn't working, here is VoG's amended formula...
=IF(MIN((B2,E2,H2))>2.5,"go for it",IF(AND(LARGE((B2,E2,H2),1)>2.5,LARGE((B2,E2,H2),2)>2.5),"mmm","leave alone"))


AND... another possibility just to round things out.
=INDEX({"go for it","mmmmmm","leave alone"},1,(COUNTIF(B2,"<2.5")+COUNTIF(E2,"<2.5")+COUNTIF(H2,"<2.5")+1))

Surely one of the three above should work.

Sorted!! Thanks BiocideJ. It was a bit of trial and error, well lots of errors, but the last formula you gave me, the one starting =INDEX.... was the one that actually worked. Google Docs must do something different somewhere.

Thanks again to All for persevering with my queries and helping me to resolve this.

best regards

Nigel
TQO23

 

[BiocideJ]

Sorted!! Thanks BiocideJ. It was a bit of trial and error, well lots of errors, but the last formula you gave me, the one starting =INDEX.... was the one that actually worked. Google Docs must do something different somewhere.

Thanks again to All for persevering with my queries and helping me to resolve this.

best regards

Nigel
TQO23

Excellent. I'm glad it worked out. I've never used Google Docs before, but I'm surprised so many of the formulas don't work. I'm glad I threw that third option out now since it appears to be the only one that worked.

 

[TQO23]

Excellent. I'm glad it worked out. I've never used Google Docs before, but I'm surprised so many of the formulas don't work. I'm glad I threw that third option out now since it appears to be the only one that worked.

Hi again BiocideJ.

Now I know why I didn't work in a test environment, I never test everything!! This issue has only just come to light because it's the first time we've had a below 2.5 in all 3 cells.

The =INDEX rule you gave me works great with one exception. If all 3 scores are below 2.5 I get an error message #REF! When I hover over this cell which says "error: reference out of range"

Without knowing how the =INDEX function really works I'm at a loss to know where to start. I did try and change some of the values but that screws the other results.

You mentioned previously that I didn't specify what to do if all 3 values are below 2.5 but I didn't latch onto that because of the test scores I was using. Dummy!!

Is there a tweak you can do to the formula to rectify this please?

=INDEX({"Go For It!","Mmmmm Consider 0-0 Rule","Leave Well Alone"},1,(COUNTIF($G14,"<2.5")+COUNTIF($J14,"<2.5")+COUNTIF($M14,"<2.5")+1))

Thanks

Nigel
TQO23

 

[BiocideJ]

=INDEX({"Go For It!","Mmmmm Consider 0-0 Rule","Leave Well Alone"},1,(COUNTIF($G14,"<2.5")+COUNTIF($J14,"<2.5")+COUNTIF($M14,"<2.5")+1))

A brief explanation of how Index works and it will be a relatively easy fix.

Index(a,b,c) has 3 arguments that you pass to it, in this example a,b & c.
A is generally a reference or some array of values (in my case it is an array of 1 row and 3 columns)
B is the row number to return. Since my manually entered array has only 1 row I manually enter a 1.
C is the column number to return. Since my array has 3 columns (Go For It, Mmmmm, Leave Alone), I use the COUNTIFS to total how many values are below 2.5 and return the appropriate column. The +1 is because there is no column 0.

The problem you are experiencing is because when all 3 values are <2.5 you are getting 1+1+1+1 = 4 and there is no 4th column in the supplied array so when it tries to reference it Excel can't find it and returns #REF!

The following will fix it by adding a 4th item to the array.

=INDEX({"Go For It!","Mmmmm Consider 0-0 Rule","Leave Well Alone","REALLY Leave Well Alone"},1,(COUNTIF($G14,"<2.5")+COUNTIF($J14,"<2.5")+COUNTIF($M14,"<2.5")+1))

You can change the text to say whatever you ultimately want.

 

[TQO23]

Thanks for a detailed explanation and response BiocideJ.

As always very much appreciated.

I have a couple of applications that I can use this on so it has been an invaluable learning experience for me.

regards

Nigel
TQO23