multiple criteria in one cell

Glasgow Girl · Mar 10, 2016

Hi Guys, this is my first time posting so hopefully I’ll do it right. I’m trying to get to grips with excel so I’m not sure if it is even possible to have multiple criteria for a single cell but would appreciate any help you can provide.

I have to produce schedules for 200 exam invigilators based on information from a spreadsheet with 6000 lines of data, its taking forever

However, a lot of exams have students who are given additional time (15mins per hour) at the beginning of an exam, so I would have to take that into account when producing schedules to make sure invigilators start 20mins before the early student, e.g.

60min exam starts at 14:00 with extra time 15mins, plus invigilator time 20mins;
Student starts 13:45 and invigilator starts 13:25

2hr exam starts 14:00, with extra time 30mins, plus invigilator time 20mins;
Student starts 13:30 and invigilator starts 13:10

I was hoping to find a formula that would do the following
I have used row 5 as an example but I need it for the whole of column R:

When cell V5 = yes and cell U5 is equal to 01:00 add 35mins to cell R5
When cell V5 = yes and cell U5 is equal to 01:30 add 42mins to cell R5
When cell V5 = yes and cell U5 is equal to 02:00 add 50mins to cell R5
When cell V5 = yes and cell U5 is equal to 03:00 add 65mins to cell R5

	E	F	R	S	T	U	V
	Exam Start	Exam Finish	Invigilation START	Invigilation END	Invigilation TOTAL	Exam Length	Early Starts
5	09:30	10:30	09:10	10:55	01:45	01:00	YES
6	14:00	15:30	13:40	15:55	02:15	01:30	YES
7	09:30	11:30	09:10	11:55	02:45	02:00	YES
8	14:00	17:00	13:40	17:25	03:45	03:00	YES

Thank you in advance for any help you can give me

RoryA · Mar 10, 2016

Welcome to the forum.

I'm a little confused by your description and your examples. Do you already have the data in column R or is the formula supposed to go into column R and calculate the invigilator start time based on columns E, U and V?

AlanY · Mar 10, 2016

try this.
the new start time is in Column W

Excel 2012

E

F

R

S

T

U

V

W

1

Exam Start

Exam Finish

Invigilation START

Invigilation END

Invigilation TOTAL

Exam Length

Early Starts

2

3

4

5

09:30

10:30

09:10

10:55

01:45

01:00

YES

09:45

6

14:00

15:30

13:40

15:55

02:15

01:30

YES

14:22

7

09:30

11:30

09:10

11:55

02:45

02:00

YES

10:00

8

14:00

17:00

13:40

17:25

03:45

03:00

YES

14:45

9

14:00

15:30

13:40

15:55

02:15

01:30

No

13:40

10

09:30

11:30

09:10

11:55

02:45

01:00

YES

09:45

Sheet3

Cell Formulas
Range	Formula
W5	=IF(V5="YES",R5+LOOKUP(U5,0+{"1:00","1:30","2:00","3:00"},0+{"0:35","0:42","0:50","0:65"}),R5)

or you can put it back to Column R by swapping them over like this

Excel 2012

E

F

R

S

T

U

V

W

1

Exam Start

Exam Finish

Invigilation START

Invigilation END

Invigilation TOTAL

Exam Length

Early Starts

2

3

4

5

09:30

10:30

09:45

10:55

01:45

01:00

YES

09:10

6

14:00

15:30

14:22

15:55

02:15

01:30

YES

13:40

7

09:30

11:30

10:00

11:55

02:45

02:00

YES

09:10

8

14:00

17:00

14:45

17:25

03:45

03:00

YES

13:40

9

14:00

15:30

13:40

15:55

02:15

01:30

No

13:40

10

09:30

11:30

09:45

11:55

02:45

01:00

YES

09:10

Sheet3

Cell Formulas
Range	Formula
R5	=IF(V5="YES",W5+LOOKUP(U5,0+{"1:00","1:30","2:00","3:00"},0+{"0:35","0:42","0:50","0:65"}),W5)

Glasgow Girl · Mar 10, 2016

So sorry, I didn't explain this too well, I confuse myself sometimes!

There would be no data in column R, I need to calculate the time based on; whether there is an early start student (V), the length of the exam (U) and the original start time (E)

The start time of the exam is E5. However, some students are given extra time (15mins per hour) so start before everyone else. When I allocate an invigilator they have to be there 20mins before the early start student. The variable is the length of the exam, so a 2hr exam starts at 14:00 for the majority of students, but one or two students will start 13:30 and the invigilator has to start at 13:10.

Thanks

AlanY · Mar 10, 2016

ok, check if this work for you

Excel 2012

E

F

R

S

T

U

V

1

Exam Start

Exam Finish

Invigilation START

Invigilation END

Invigilation TOTAL

Exam Length

Early Starts

2

3

4

5

09:30

10:30

10:05

10:55

01:45

01:00

YES

6

14:00

15:30

14:42

15:55

02:15

01:30

YES

7

09:30

11:30

10:20

11:55

02:45

02:00

YES

8

14:00

17:00

15:05

17:25

03:45

03:00

YES

9

14:00

15:30

14:00

15:55

02:15

01:30

No

10

09:30

11:30

10:05

11:55

02:45

01:00

YES

Sheet3

Cell Formulas
Range	Formula
R5	=IF(V5="YES",E5+LOOKUP(U5,0+{"1:00","1:30","2:00","3:00"},0+{"0:35","0:42","0:50","0:65"}),E5)

RoryA · Mar 10, 2016

Try this in R5:

=E5-TIME(0,20,0)-IF(V5="YES",TIME(0,ROUNDDOWN(U5*24*15,0),0))
and copy down.

Glasgow Girl · Mar 10, 2016

I'm afraid that didn't work. The invigilator time is later than the start time instead of earlier. I need the invigilator to start first, then the student with extra time, then the main body of students (their start time is in column E).

I really appreciate you all taking the time to help with this, sorry I wasn't too clear with what I needed

Glasgow Girl · Mar 10, 2016

Oh you genius. Thank you so much, that seems to have done it

RoryA · Mar 10, 2016

Glad to help.

AlanY · Mar 10, 2016

formula should read

=IF(V5="YES",E5-LOOKUP(U5,0+{"1:00","1:30","2:00","3:00"},0+{"0:35","0:42","0:50","0:65"}),E5)

multiple criteria in one cell

Glasgow Girl

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

AlanY

Well-known Member

Glasgow Girl

New Member

AlanY

Well-known Member

RoryA

MrExcel MVP, Moderator

Glasgow Girl

New Member

Glasgow Girl

New Member

RoryA

MrExcel MVP, Moderator

AlanY

Well-known Member

Similar threads

Forum statistics

Share this page

multiple criteria in one cell

New Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

New Member

Well-known Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock