Formula that deducts points for each code I input.

[bricegoing]

I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor Participation, T for Tardy, etc...) have them lose that number of points. I have each code and the number of points it deducts on another sheet in excel but don't know how to make it look up the code and then deducts the points. Thanks for any help!

 

[bricegoing]

What happens if you put this into an empty cell

=SEQUENCE(2)

#NAME?

 

[Fluff]

Ok, that means you don't yet have the latest update.
Try

Employability Grades Auto Grade.xlsx
	A	B	C	D	E	F
1		11/09/2020	18/09/2020	25/09/2020	02/10/2020	09/10/2020
2	Student	Employability	Employability	Employability	Employability	Employability
3	Gavin Bell			PP,T,S	SL,L
4		100	100	50	30	30
Carpentry II A Day

Cell Formulas
Range	Formula
B4	B4	=100-SUM(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(B3,",",REPT(" ",15)),{1,16,31,46,61},15)),Deductions!$A$3:$B$15,2,0),0))
C4:F4	C4	=B4-SUM(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(C3,",",REPT(" ",15)),{1,16,31,46,61},15)),Deductions!$A$3:$B$15,2,0),0))

 

[bricegoing]

Ok, that means you don't yet have the latest update.
Try

Employability Grades Auto Grade.xlsx
	A	B	C	D	E	F
1		11/09/2020	18/09/2020	25/09/2020	02/10/2020	09/10/2020
2	Student	Employability	Employability	Employability	Employability	Employability
3	Gavin Bell			PP,T,S	SL,L
4		100	100	50	30	30
Carpentry II A Day

Cell Formulas
Range	Formula
B4	B4	=100-SUM(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(B3,",",REPT(" ",15)),{1,16,31,46,61},15)),Deductions!$A$3:$B$15,2,0),0))
C4:F4	C4	=B4-SUM(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(C3,",",REPT(" ",15)),{1,16,31,46,61},15)),Deductions!$A$3:$B$15,2,0),0))

I'm so sorry this is taking so much of your time.. Its working but only for the first code. If I put PP it correctly goes down to 80, but if I put PP,T or any other second code it stays at 80

 

[Fluff]

Oops forgot to mention it will need to be confirmed with Ctrl Shift Enter & not just enter.

 

[bricegoing]

Oops forgot to mention it will need to be confirmed with Ctrl Shift Enter & not just enter.

I did the cntrl shift enter and it put the {} on it but its still only reading the first code. I tried PP,T PP, T and PPT

 

[Fluff]

Maybe older version of Excel can't handle an array like & I don't have an old version to test on.

 

[bricegoing]

Maybe older version of Excel can't handle an array like & I don't have an old version to test on.

Shoot. Thank you for trying though.

 

[Eric W]

In older versions of Excel, lookup functions inside of an array formula are pretty much a no-no. Try this though:

Book1
	A	B	C	D	E	F
1		9/11/2020	9/18/2020	9/25/2020	10/2/2020	10/9/2020
2	Student	Employability	Employability	Employability	Employability	Employability
3	Gavin Bell			PP,T,S	SL,L
4		100	100	50	30	100
Sheet3

Cell Formulas
Range	Formula
B4:F4	B4	=100-SUMPRODUCT(--ISNUMBER(SEARCH(","&Deductions!$A$3:$A$15&",",","&B3&",")),Deductions!$B$3:$B$15)

This assumes that you only have a given code in a cell one time. If you could have PP more than once, I can come up with something with SUMIF instead.

 

[bricegoing]

In older versions of Excel, lookup functions inside of an array formula are pretty much a no-no. Try this though:

Book1
	A	B	C	D	E	F
1		9/11/2020	9/18/2020	9/25/2020	10/2/2020	10/9/2020
2	Student	Employability	Employability	Employability	Employability	Employability
3	Gavin Bell			PP,T,S	SL,L
4		100	100	50	30	100
Sheet3

Cell Formulas
Range	Formula
B4:F4	B4	=100-SUMPRODUCT(--ISNUMBER(SEARCH(","&Deductions!$A$3:$A$15&",",","&B3&",")),Deductions!$B$3:$B$15)

This assumes that you only have a given code in a cell one time. If you could have PP more than once, I can come up with something with SUMIF instead.

This works!! Thank you so much. Excel is not my thing but you and fluff have really impressed me with your knowledge of excel and it honestly makes me want to learn more. Thanks for all the help guys!

 

[Eric W]

Glad we could help! Thanks for the feedback.