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!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your 2 sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[bricegoing]

Dropbox

www.dropbox.com

I would like cell B4, C4, D4, and so on, to start with 100 pts. and for each code I put into B3,C3,D3, etc... to reference the table on the sheet named deductions and subtract the correct number of points for that code. (Example for code PP 20 points would be removed from their starting total of 100. If they have PP, T, it would take 20 points for the PP and 10 points for the T code leaving them at 70. Hopefully this helps some.

 

[Fluff]

Is this what you mean

Employability Grades Auto Grade.xlsx
	A	B	C	D	E
1		11/09/2020	18/09/2020	25/09/2020	02/10/2020
2	Student	Employability	Employability	Employability	Employability
3	Gavin Bell			PP	T
4		100	100	80	70
Carpentry II A Day

Cell Formulas
Range	Formula
B4	B4	=100-IFNA(VLOOKUP(B3,Deductions!$A$3:$B$15,2,0),0)
C4:E4	C4	=B4-IFNA(VLOOKUP(C3,Deductions!$A$3:$B$15,2,0),0)

 

[bricegoing]

Is this what you mean

Employability Grades Auto Grade.xlsx
	A	B	C	D	E
1		11/09/2020	18/09/2020	25/09/2020	02/10/2020
2	Student	Employability	Employability	Employability	Employability
3	Gavin Bell			PP	T
4		100	100	80	70
Carpentry II A Day

Cell Formulas
Range	Formula
B4	B4	=100-IFNA(VLOOKUP(B3,Deductions!$A$3:$B$15,2,0),0)
C4:E4	C4	=B4-IFNA(VLOOKUP(C3,Deductions!$A$3:$B$15,2,0),0)

That works if there is only one code, but if I input multiple codes it goes back to 100. Is it possible for it to deduct for multiple codes?

 

[Fluff]

Realistically you would need a macro for that.
Doing that with a formula would not be simple

 

[bricegoing]

Realistically you would need a macro for that.
Doing that with a formula would not be simple

OK Thank you for the help.

 

[Fluff]

Would a macro be ok?

 

[bricegoing]

Would a macro be ok?

Yes, any way that it would work is A okay with me lol.

 

[Fluff]

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	80	100
Carpentry II A Day

Cell Formulas
Range	Formula
B4:F4	B4	=100-SUM(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(B3,",",REPT(" ",15)),SEQUENCE(5,,1,15),15)),Deductions!$A$3:$B$15,2,0),0))