Help Me With My Crazy Idea

[WH2000]

Hello,
I am trying to create a points tracking system for my auto club.
So far what i have is i place you finishing position into the column with the date and it fills in the points value in the point column.

We operate a points bonus points system based upon the number of individuals currently participating in each category as follows:
you gain 1 point for each person you beat up to 4, so if you have 3 people in you category, 1st place would get 2 bonus point, and second place would get 1.

What i am looking for is to be able to enter the position and have it automatically assign the bonus points by the random number of people in each category..

I do not see a way to attach the file to this, so i will more than gladly email it as it can probably be streamlined to be made more efficient.

 

[Jeffrey Mahoney]

This how I imagined what you said

Book4
	A	B	C	D	E	F	G	H	I	J
1		Date:	5/19/2024		6/9/2024		7/15/2024		8/4/2024
2		Participants:	4		6		5		4
3	Total Bonus	Name	Place	Bonus	Place	Bonus	Place	Bonus	Place	Bonus
4	2	Bob	2	2		0		0	4	0
5	5	John	1	3	5	1	5	0	3	1
6	9	Frank	3	1	4	2	1	4	2	2
7	8	Herald	4	0	1	4	4	1	1	3
8	6	Jack		0	2	4	3	2		0
9	6	George		0	3	3	2	3		0
10	0	Suzanne			6	0		0		0
Sheet1

Cell Formulas
Range	Formula
D4:D9,J4:J10,H4:H10,F4:F10	D4	=IF(C4>0,MIN(C$2-C4,4),0)
A4:A10	A4	=SUMIF($C$3:$M$3,"Bonus",C4:M4)

 

[WH2000]

Works great THANK YOU THANK YOU THANK YOU

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not sure how big your competitor list might be but with your version of Excel you can get all results in a column with just a formula in the top cell without having to copy the formula down the column. In my example below, the formula is only entered into D4 and then copied to F4, H4 and J4
Not sure if it is better for you or not but I have also made it so that if the person did not compete on a particular day or for some reason no place was awarded the Bonus column remains blank like the Place column, rather than filling with a 0.
You can also get all the totals in column A with a single formula in the top cell only.

24 10 11.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Date:	19/05/2024		9/06/2024		15/07/2024		4/08/2024
2		Participants:	4		6		5		4
3	Total Bonus	Name	Place	Bonus	Place	Bonus	Place	Bonus	Place	Bonus
4	2	Bob	2	2					4	0
5	5	John	1	3	5	1	5	0	3	1
6	9	Frank	3	1	4	2	1	4	2	2
7	8	Herald	4	0	1	4	4	1	1	3
8	6	Jack			2	4	3	2
9	6	George			3	3	2	3
10	0	Suzanne			6	0
Bonus Points

Cell Formulas
Range	Formula
A4:A10	A4	=BYROW(C4:J10,LAMBDA(r,SUMIF(C3:J3,"Bonus",r)))
D4:D10,J4:J10,H4:H10,F4:F10	D4	=BYROW(C4:C10,LAMBDA(r,IF(r>0,MIN(C2-r,4),"")))
Dynamic array formulas.

 

[WH2000]

Thank you I will give it a try
Gives me more options