Check value of cells against condition?

[jgruberman]

Hello,

I am looking to check a table of sports team names, times and ranking and if their ranking is 3 or lower and their game time is 9pm or later, to flag them somehow, perhaps with another cell with a yes/no or just any sort of identifier that the team fits the specified criteria. Here is an excerpt:

2/4	9:45 PM	Warriors D1	Bulldogs D1	D1	Valley Ranch StarCenter	Green	1	5
2/4	3:30 PM	GRITR D2	Fighting Squids D2	D2	Valley Ranch StarCenter	Hitchcock	5	4

 

[SanjayGMusafir]

I tried the formula posted, but it seems I did something wrong? I think the Away Flag should be "Yes" since it's 10pm and 3.
View attachment 105947

I believe @Joe4 has asked a valid question #9. Your screenshot is that from Google Sheets...

 

[etaf]

yes it should be yes so check the time is not text and 3 is not text

as asked a few times now - is this a google sheet question ????

 

[jgruberman]

Yes, this is for Google Sheets. Previously I posted in the wrong forum and an admin moved the question to this one. I double checked the forum description and it says this is for Google Sheets. I apologize, I don't understand why it matters... did I not post it in the correct forum again? I was almost certain that I did....

 

[etaf]

I don't understand why it matters.

because google uses different functions from excel - and its my mistake i did not see the forum you where in
so i will not be able to help any further and unsubscribe from more answers
as i say - sorry about that

 

[jgruberman]

By the way, are you trying to do this in Excel or Google Sheets?
I ask because I see that your previous question was for Google Sheets, and you posted this to the "General Discussion & Other Applications" forum.

Yes this is for Sheets, that's why I posted it in the forum that says "This is also the place to ask about other spreadsheet programs (e.g. Google Sheets)". My first question I asked in the wrong forum but a mod moved it to the correct one I believe.

 

[jgruberman]

because google uses different functions from excel - and its my mistake i did not see the forum you where in
so i will not be able to help any further and unsubscribe from more answers
as i say - sorry about that

So the formula will not work?

 

[etaf]

I dont know , as i have never used google sheets - I suspect as its a basic IF () and timevalue logic tests
I have just used google sheets and tried
it works OK

not sure how to save the file in google sheets so i can keep the share for you to see, or anyone searching in future

you can see true - usingthis formula
=and(B2>TIMEVALUE("21:00"),I2<=3)

2/4	21:45	Warriors D1	Bulldogs D1	D1	Valley Ranch StarCenter	Green	1	5	FALSE
2/4	22:00	GRITR D2	Fighting Squids D2	D2	Valley Ranch StarCenter	Hitchcock	5	3	TRUE

 

[Fluff]

Yes this is for Sheets, that's why I posted it in the forum that says "This is also the place to ask about other spreadsheet programs (e.g. Google Sheets)".

It would also help if you clearly stated what your were using in the original question.

 

[jgruberman]

It would also help if you clearly stated what your were using in the original question.

I apologize, I assumed that if I wasn't posting in the Excel specific forum that it implied I was using Sheets. That was my mistake.

 

[jgruberman]

I dont know , as i have never used google sheets - I suspect as its a basic IF () and timevalue logic tests
I have just used google sheets and tried
it works OK

not sure how to save the file in google sheets so i can keep the share for you to see, or anyone searching in future

you can see true - usingthis formula
=and(B2>TIMEVALUE("21:00"),I2<=3)

2/4	21:45	Warriors D1	Bulldogs D1	D1	Valley Ranch StarCenter	Green	1	5	FALSE
2/4	22:00	GRITR D2	Fighting Squids D2	D2	Valley Ranch StarCenter	Hitchcock	5	3	TRUE

It still is not working, I have a feeling that it is because I2 is not a static value, but a formula itself: "=SUMPRODUCT((preftable!$B$1:$F$1)*(preftable!$B$2:$F$36=H19)*(preftable!$A$2:$A$36=F19))". I am not sure how to read the output value in the cell, but I am guessing this is why