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]

Just thought of something if you have the dynamic array functions

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,","," "),SEQUENCE(5,,1,5),5)),Deductions!$A$3:$B$15,2,0),0))
C4:F4	C4	=B4-SUM(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(C3,","," "),SEQUENCE(5,,1,5),5)),Deductions!$A$3:$B$15,2,0),0))

That seems to be what i'm looking for but when I put your formula into my spreadsheet it doesn't change when I add codes. going by the screenshot you posted that looks like it works like what i'm asking. Thank you so much for spending your time trying to help me. If I could get it to work on my sheet like it looks like its working on yours that would be it.

 

[Fluff]

I've changed the formula after you saw it, so have another look at post#10

 

[bricegoing]

I've changed the formula after you saw it, so have another look at post#10

Still a no go

 

[Fluff]

What do you get in the cells?

 

[bricegoing]

What do you get in the cells?

They just remain 100

 

[Fluff]

Do you have the SEQUENCE function?

 

[bricegoing]

Do you have the SEQUENCE function?

=100-SUM(IFERROR(VLOOKUP(TIM(MID(SUBSTITUTE(B3,",",REPT(" ",15)),SEQUENCE(5,,1,15),15)),Deductions!$A$3:$B$15,2,0),0))

That's what I put in

 

[Fluff]

Not all versions of 365 have the sequence function yet, do you?

 

[bricegoing]

Not all versions of 365 have the sequence function yet, do you?

that I'm not sure of. Its through my school laptop. How can I tell?

 

[Fluff]

What happens if you put this into an empty cell

=SEQUENCE(2)