Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a long If and AND statement. If a1 is between 17-21 AND a2 is greater than 42 and a3 is greater 53 and a4 is less than the time of 15:54 then they pass, if not they fail
Solved by E. S. in 54 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 24/02/2018 - 05:13
Welcome to excel got it pro.
Excelchat Expert 24/02/2018 - 05:13
User 24/02/2018 - 05:14
What a coincidence. I'm here to be helped today,
User 24/02/2018 - 05:14
I'm really just curious if that can be done in one formula.
Excelchat Expert 24/02/2018 - 05:14
Is it possible for you to show some sample data?
Excelchat Expert 24/02/2018 - 05:15
Haha.
Excelchat Expert 24/02/2018 - 05:15
Yup, it can be done.
Excelchat Expert 24/02/2018 - 05:15
I just need to see the data orientation.
User 24/02/2018 - 05:16
essentially that
Excelchat Expert 24/02/2018 - 05:16
Thanks, Please gimme a couple of minutes.
User 24/02/2018 - 05:16
I'll change the age to make it work
Excelchat Expert 24/02/2018 - 05:17
Let me ask you another thing, what is the program you are using? excel or google sheets? if excel, is it office 365 or another version?
User 24/02/2018 - 05:17
it is excel
User 24/02/2018 - 05:17
version....
User 24/02/2018 - 05:17
2013
Excelchat Expert 24/02/2018 - 05:18
ah okay. then we need the if/and combination. Office 365 offer a more simple alternative. A few minutes please.
User 24/02/2018 - 05:20
I can buy 365 and download it in about 5 minutes if that makes your life easier
Excelchat Expert 24/02/2018 - 05:21
No no, not a problem.
Excelchat Expert 24/02/2018 - 05:22
D is mm:ss, right?
User 24/02/2018 - 05:22
yes
Excelchat Expert 24/02/2018 - 05:24
Okay, we need to change it a bit, excel recognizes the current time as 13 hour 53 minutes rather than 13 min 53 sec.
User 24/02/2018 - 05:24
OH shit. That's maybe why i couldn't get it haha
Excelchat Expert 24/02/2018 - 05:26
I trying to give it a work.
Excelchat Expert 24/02/2018 - 05:29
If you convert the cell value as plain text, would it be a problem?
User 24/02/2018 - 05:29
no noot at all
User 24/02/2018 - 05:30
as long as it registers 13:54 as slower than 13:53... to maybe
Excelchat Expert 24/02/2018 - 05:30
okay, please select YES when you are asked whether you need more time or not.
User 24/02/2018 - 05:33
That seems like a terrible flaw in the program haha.
Excelchat Expert 24/02/2018 - 05:36
Okay, maybe excel etiquette can help here. :)
Excelchat Expert 24/02/2018 - 05:38
excel etiquette( best practice) says use cell reference rather than hard coded numbers in formula.
Excelchat Expert 24/02/2018 - 05:38
=IF(AND(A2>=17,A2<=21,B2>42,C2>52,D2>F1),"Pass","Fail")
Excelchat Expert 24/02/2018 - 05:38
I have used this and it is working now.
User 24/02/2018 - 05:40
That's a lot simpler than I was trying to do.
Excelchat Expert 24/02/2018 - 05:40
sometimes the solution is simple. :)
User 24/02/2018 - 05:41
So let me complicate it further. If i wanted different variables for age range 22-26,27-31, etc. Can that all be added to one formula? Or is that getting too convoluted
User 24/02/2018 - 05:41
essentially an OR
Excelchat Expert 24/02/2018 - 05:41
Of course it can be added into one formula.
User 24/02/2018 - 05:43
Those variables... But I could essentially link them to the benchmark and place is on a different sheet.
Excelchat Expert 24/02/2018 - 05:44
Yup you can.
Excelchat Expert 24/02/2018 - 05:44
If you need help on how to, just ask me. I'm here for another 30 minutes if you need me.
User 24/02/2018 - 05:45
If you could do the next one that would be OR i could probably go from there and figure out the rest
Excelchat Expert 24/02/2018 - 05:45
Incorporate in one formula, right?
User 24/02/2018 - 05:46
Yes
User 24/02/2018 - 05:46
I have about 600 of these to do. I basically just got handed a tracking list of a bunch of army dudes. That's the low range of the PT table.
Excelchat Expert 24/02/2018 - 05:46
Okay.
Excelchat Expert 24/02/2018 - 05:47
Is it possible for you to share the original file? I might be able to simplify the formula.
User 24/02/2018 - 05:48
you're basically looking at exactly the file. Just has a Rank and Name column
Excelchat Expert 24/02/2018 - 05:48
okay. understood. please again select Yes when you are asked.
User 24/02/2018 - 05:50
So like the next few lines...
User 24/02/2018 - 05:50
the second line is the same except the age is different
User 24/02/2018 - 05:51
and all their ages are calculated wiht a formula from their birthday
Excelchat Expert 24/02/2018 - 05:51
understood.
User 24/02/2018 - 05:52
As it stands I have to manually look at everyone's numbers, which obviously i can sort by age but it's tedious.
User 24/02/2018 - 05:53
Lol and also i can tell you by run times 4 of them failed
User 24/02/2018 - 05:54
line 4 should be a Fail
User 24/02/2018 - 05:54
line 3 a pass
Excelchat Expert 24/02/2018 - 05:56
Please take a closer look, and let me know why.
User 24/02/2018 - 05:57
So line 3.. a 23 year old, the minimum to pass is 40 push ups, 50 sit ups, and run faster than 16:36
User 24/02/2018 - 05:57
17:58 is slower
Excelchat Expert 24/02/2018 - 05:57
ah okay.
User 24/02/2018 - 05:57
and i'll fix the age on teh first one so i do'nt fck this up
Excelchat Expert 24/02/2018 - 05:58
now?
User 24/02/2018 - 05:59
Thats their actuall pass/fail
Excelchat Expert 24/02/2018 - 06:00
15:53 is slower than 13:54
Excelchat Expert 24/02/2018 - 06:01
so, it should be a fail.
Excelchat Expert 24/02/2018 - 06:01
The table was incorrect.
Excelchat Expert 24/02/2018 - 06:02
Now it is correct.
Excelchat Expert 24/02/2018 - 06:02
=IF(AND(A2>=\$G\$3,A2<=\$H\$3,B2>\$I\$3,C2>\$J\$3,D2<\$K\$3),"Pass",IF(AND(A2>=\$G\$4,A2<=\$H\$4,B2>\$I\$4,C2>\$J\$4,D2<\$K\$4),"Pass","Fail"))
Excelchat Expert 24/02/2018 - 06:02
here I have added only first two group.
User 24/02/2018 - 06:03
thanks
User 24/02/2018 - 06:03
You're a scholar and a gentleman
User 24/02/2018 - 06:03
dude made it by a second hahaa i feel like someone pencil whipped that grade
Excelchat Expert 24/02/2018 - 06:04
IF(AND(A2>=\$G\$3,A2<=\$H\$3,B2>\$I\$3,C2>\$J\$3,D2<\$K\$3),"Pass",IF(AND(A2>=\$G\$4,A2<=\$H\$4,B2>\$I\$4,C2>\$J\$4,D2<\$K\$4),"Pass",IF(AND(A2>=\$G\$5,A2<=\$H\$5,B2>\$I\$5,C2>\$J\$5,D2<\$K\$5),"Pass",IF(AND(A2>=\$G\$6,A2<=\$H\$6,B2>\$I\$6,C2>\$J\$6,D2<\$K\$6),"Pass","Fail"))))
Excelchat Expert 24/02/2018 - 06:04
here you go, added two more.
Excelchat Expert 24/02/2018 - 06:04
Do you want me to finish the rest too?'
User 24/02/2018 - 06:05
Nah working it out should be a nice learning opportunity for me
User 24/02/2018 - 06:05
Didn't you say you got off soon?
Excelchat Expert 24/02/2018 - 06:05
Did I?
Excelchat Expert 24/02/2018 - 06:05
I don't remember, :)
User 24/02/2018 - 06:05
There is a beer somewhere that needs to be drank.
User 24/02/2018 - 06:06
I appreciate your help. Next time I'll have something more difficult.
Excelchat Expert 24/02/2018 - 06:06
haha.
Excelchat Expert 24/02/2018 - 06:06
We will be waiting for the challenge.
Excelchat Expert 24/02/2018 - 06:06
Anything else you need for now?
User 24/02/2018 - 06:07
Nope enjoy your day/night/morning/evening. Whatever you got going on whereever you are
Excelchat Expert 24/02/2018 - 06:07
Its the middle of the day here. You too.
User 24/02/2018 - 06:07
1am here this is how i spend my friya nights
Excelchat Expert 24/02/2018 - 06:07
Please rate my effort before you leave.
User 24/02/2018 - 06:07
you have a good day
User 24/02/2018 - 06:07
deal
Excelchat Expert 24/02/2018 - 06:08
To leave feedback click on the End session on the top right.
User 24/02/2018 - 06:08
cheers

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.