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
I'm here to help you today.
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.
