IF and VLOOKUP formulas to get started

[Pottie8]

Hi All

I hope to receive as much guidance and help as possible on this platform

https://drive.google.com/drive/folders/0B9pqW92R9U4xLWkxek1zTGFZLUk?usp=sharing

I have attached the data and destination images and would just like so help with formulas to:

1. In destination enter user name just the first time even if it is in data more than once.
2. continue on the second line with the first instance of any different name etc.
3. populate column values based on data sheet.

If any further info is needed, please let me know.


Regards,
Pottie8

 

[oldbrewer]

I would sort the data by student name ascending, then by exam name ascending, then by fail before pass.

I would then use a macro to pull out scores to find number of attempts, ave and best score etc. Easy, but quite time consuming. I am away for 4 days if nobody else has sorted it for you by then I will try to do it for you.

 

[Pottie8]

I would sort the data by student name ascending, then by exam name ascending, then by fail before pass.

I would then use a macro to pull out scores to find number of attempts, ave and best score etc. Easy, but quite time consuming. I am away for 4 days if nobody else has sorted it for you by then I will try to do it for you.

Hi There,

That sound great! Thank you in advance!


Kind Regards,
Henco

 

[micsza]

Hi Pottie8,

please check out my try to your request:

Firstly, for the sake of simplicity I have named the columns in the data sheet as follows: dates, students, lessons, results.
Secondly, 'name' is the reference to the student name you type in, so basically A4, A5, etc and 'lesson' is the reference to the lesson (so likely B2 or you can hardcode it). Obviously your final references need appropriate update to match your sheet names etc.

# For the number of attempts:
=COUNTIFS(students,name,lessons,lesson)

# For the best score:
=MAXIFS(results,students,name,lessons,lesson)

# For the average:
=AVERAGEIFS(results,students,name,lessons,lesson)

# And for the initial score:
=INDIRECT(ADDRESS(ROW(dates)+MATCH(MINIFS(dates,students,name,lessons,lesson),dates,0)-1,COLUMN(results)))

I hope you find it at least somehow helpful.

 

[Pottie8]

Hi Pottie8,

please check out my try to your request:

Firstly, for the sake of simplicity I have named the columns in the data sheet as follows: dates, students, lessons, results.
Secondly, 'name' is the reference to the student name you type in, so basically A4, A5, etc and 'lesson' is the reference to the lesson (so likely B2 or you can hardcode it). Obviously your final references need appropriate update to match your sheet names etc.

# For the number of attempts:
=COUNTIFS(students,name,lessons,lesson)

# For the best score:
=MAXIFS(results,students,name,lessons,lesson)

# For the average:
=AVERAGEIFS(results,students,name,lessons,lesson)

# And for the initial score:
=INDIRECT(ADDRESS(ROW(dates)+MATCH(MINIFS(dates,students,name,lessons,lesson),dates,0)-1,COLUMN(results)))

I hope you find it at least somehow helpful.

Hi micsza

That seems to do the trick!

Just playing around with the specifics, but thatnk you ever so much!!!!!

Please be on standby furhter if possible?

THANK YOU!!!


Pottie8

 

[Pottie8]

Hi There

I seem to just be struggling with initial score issue. The rest works 100%! Thank you.


WORKSHEET:

[TABLE="width: 641"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Store[/TD]
[TD]dates[/TD]
[TD]Time[/TD]
[TD]Total Time[/TD]
[TD]students[/TD]
[TD]lessons[/TD]
[TD]results[/TD]
[TD]Passed[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]AAA[/TD]
[TD]2017-04-07[/TD]
[TD]12:18:29[/TD]
[TD]00:01:11[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]AAA[/TD]
[TD]2017-04-07[/TD]
[TD]18:08:20[/TD]
[TD]05:48:43[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]AAA[/TD]
[TD]2017-04-13[/TD]
[TD]16:00:49[/TD]
[TD]00:01:26[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]AAA[/TD]
[TD]2017-04-28[/TD]
[TD]12:17:32[/TD]
[TD]00:01:47[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]AAA[/TD]
[TD]2017-04-28[/TD]
[TD]12:18:47[/TD]
[TD]00:01:06[/TD]
[TD]Mary[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]AAA[/TD]
[TD]2017-04-12[/TD]
[TD]10:50:13[/TD]
[TD]00:01:29[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]17:11:11[/TD]
[TD]00:01:30[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]AAA[/TD]
[TD]2017-04-28[/TD]
[TD]15:04:25[/TD]
[TD]00:01:15[/TD]
[TD]James[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:44:18[/TD]
[TD]00:02:56[/TD]
[TD]James[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:49:29[/TD]
[TD]00:05:05[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:50:13[/TD]
[TD]00:00:33[/TD]
[TD]Mary[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:50:51[/TD]
[TD]00:00:34[/TD]
[TD]Mary[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:51:26[/TD]
[TD]00:00:31[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:53:11[/TD]
[TD]00:01:42[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:53:37[/TD]
[TD]00:00:20[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:54:45[/TD]
[TD]00:01:04[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:55:18[/TD]
[TD]00:00:27[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]15:55:41[/TD]
[TD]00:00:20[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]AAA[/TD]
[TD]2017-04-05[/TD]
[TD]16:00:42[/TD]
[TD]00:04:59[/TD]
[TD]Mary[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]AAA[/TD]
[TD]2017-04-12[/TD]
[TD]10:35:30[/TD]
[TD]00:00:58[/TD]
[TD]James[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]AAA[/TD]
[TD]2017-04-12[/TD]
[TD]10:36:00[/TD]
[TD]00:00:27[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]AAA[/TD]
[TD]2017-04-26[/TD]
[TD]12:07:26[/TD]
[TD]00:02:03[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]AAA[/TD]
[TD]2017-04-07[/TD]
[TD]12:27:01[/TD]
[TD]00:01:26[/TD]
[TD]James[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]AAA[/TD]
[TD]2017-04-28[/TD]
[TD]14:16:40[/TD]
[TD]00:01:25[/TD]
[TD]Paul[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]AAA[/TD]
[TD]2017-04-28[/TD]
[TD]14:17:56[/TD]
[TD]00:01:12[/TD]
[TD]Paul[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]AAA[/TD]
[TD]2017-04-28[/TD]
[TD]14:19:17[/TD]
[TD]00:01:00[/TD]
[TD]John[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]AAA[/TD]
[TD]2017-04-29[/TD]
[TD]09:38:21[/TD]
[TD]19:18:54[/TD]
[TD]Paul[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]AAA[/TD]
[TD]2017-04-07[/TD]
[TD]12:31:26[/TD]
[TD]00:02:13[/TD]
[TD]Luke[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]AAA[/TD]
[TD]2017-04-25[/TD]
[TD]15:51:49[/TD]
[TD]03:14:05[/TD]
[TD]Peter[/TD]
[TD]Lesson 1 : Part 1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Results:

[TABLE="width: 402"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Name[/TD]
[TD]Initial Score[/TD]
[TD]Attempts[/TD]
[TD]Average[/TD]
[TD]Best Score[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]John[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Luke[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]James[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Paul[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Melissa[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Fred[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[/TR]
</tbody>[/TABLE]


Results Sheet in column B (initial score - B6 onwards) should reflect the students initial score based on date and then also time (the FIRST time student completed given lesson)

Please could you assist


Pottie8*

 

[Marcelo Branco]

To get the Initial Score try (array formula)

B6
=INDEX(results,MATCH(1,IF(students=A6,IF(dates+Time=MIN(IF(students=A6,dates+Time)),1)),0))
Ctrl+Shift+Enter

If you are using Excel 2016 (office 365) you can substitute MIN(IF(...)) by MINIFS(...)

M.

 

[Pottie8]

Hi there! Thank you - I have got a feeling you know exactly, but it gives me an N/A error. Do you think it could be because of date and/or time columns?

 

[Marcelo Branco]

Check if they are numbers, not text. Use =ISNUMBER(B2) and ISNUMBER(C2) in empty columns and copy down (should return True, if numbers)

I tried with your data sample above and the formula worked perfectly for me.
I've gotten #N/D (#N/A in Engllish) only for Melissa and Fred because they are not present in your data sample of Worksheet.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Name​

[/TD]
[TD]

Initial Score​

[/TD]
[TD]

Attempts​

[/TD]
[TD]

Average​

[/TD]
[TD]

Best Score​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

John​

[/TD]
[TD]

80​

[/TD]
[TD]

5​

[/TD]
[TD]

68​

[/TD]
[TD]

80
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Mary​

[/TD]
[TD]

60​

[/TD]
[TD]

1​

[/TD]
[TD]

60​

[/TD]
[TD]

60​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Luke​

[/TD]
[TD]

80​

[/TD]
[TD]

2​

[/TD]
[TD]

90​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

James​

[/TD]
[TD]

60​

[/TD]
[TD]

14​

[/TD]
[TD]

68​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Paul​

[/TD]
[TD]

40​

[/TD]
[TD]

1​

[/TD]
[TD]

80​

[/TD]
[TD]

80​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Peter​

[/TD]
[TD]

60​

[/TD]
[TD]

4​

[/TD]
[TD]

70​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Melissa​

[/TD]
[TD]

#N/D​

[/TD]
[TD]

5​

[/TD]
[TD]

56​

[/TD]
[TD]

60​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Fred​

[/TD]
[TD]

#N/D​

[/TD]
[TD]

2​

[/TD]
[TD]

70​

[/TD]
[TD]

80​

[/TD]
[/TR]
</tbody>[/TABLE]

Have you confirmed the formula with Ctrl+Shift+Enter?

M.

 

[Pottie8]

Hi There

It is not numbers, it was text, but formatting it as dates (which it is) return same result. Only once retyped it returns TRUE.

Also, even with this change, I still get error #N/A. but let me look into this as you are able to test with positive results, and see what I come up with.

Thank you for all your input!