Help With formula

[VinceF]

Your help is needed again and as always very much appreciated.

Golf game: either Two+ or Two++ Object with both games is to get as much under par as possible.
Two+ is with 3 players on a team and it must use the 2 lowest scores on the hole but if the third players score will help the team it uses all 3 scores.
Two++ is with 4 players on the team and it must use the low 2 scores on the hole but if the 3rd players score helps the team it will use it and same with the 4th players score.

In the example, the hole is a par 5 and 3 of the 4 players birdied the hole, the 4th players score doesn't help the team to get further under par therefore his score wouldn't count, the result should read -3


=IF(Main!$D$4="TWO+",IF(SMALL(K7:K10,3)>=K6,SMALL(K7:K10,1)+SMALL(K7:K10,2)-K6*2),
IF(Main!$D$4="TWO+",IF(SMALL(K7:K10,3)<K6,SMALL(K7:K10,1)+SMALL(K7:K10,2)+SMALL(K7:K10,3)-K6*3),
IF(Main!$D$4="TWO++",IF(SMALL(K7:K10,4)>=K6,SMALL(K7:K10,1)+SMALL(K7:K10,2)+SMALL(K7:K10,3)-K6*3),
IF(Main!$D$4="TWO++",IF(SMALL(K7:K10,4)<K6,SMALL(K7:K10,1)+SMALL(K7:K10,2)+SMALL(K7:K10,3)+SMALL(K7:K10,4)-K6*4)))))

Thanks in advance,
VinceF
Excel 2016
Win10

Master Skins-Stableford Templet FINAL 3-29-22.xlsm
	J	K
5	HOLE	1
6	PAR	5
7	Kelly	4
8	Woody	4
9	Ty	4
10	Sid	5
11		###
Two Plus

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J11	Cell Value	<0	text	NO
K11	Cell Value	<0	text	NO
D7:D46,J12:K12,J19:K19,J47:K47,J54:K54,J61:K61,J26:K26,J33:K33,J68:K68,J7:J11	Cell Value	duplicates	text	NO

 

[Snakehips]

@VinceF Is it reasonable to suggest that Main D4 will only ever contain either "Two+" or " Two++"

 

[Snakehips]

Does this help?

Note that I have hard scores entered, not the formulas that you will have.

Book1
	I	J	K	L
4
5		HOLE	1
6		PAR	5
7		Kelly	4
8		Woody	4
9		Ty	4
10		Sid	5
11			-3
12
Sheet2

Cell Formulas
Range	Formula
K11	K11	=IFERROR((-2*K6)+(SMALL(Sheet2!K7:K10,1)+SMALL(Sheet2!K7:K10,2))-IFERROR(IF(SMALL(K7:K10,3)<K6,K6-SMALL(K7:K10,3),0),0)-IFERROR(IF(AND(Main!$D$4="Two++",SMALL(K7:K10,4)<K6),K6-SMALL(K7:K10,4),0),0),"")

 

[VinceF]

@VinceF Is it reasonable to suggest that Main D4 will only ever contain either "Two+" or " Two++"

Thank you for your reply and Yes that is correct, it would only be one or the other in Main!D4.
However, I believe that I got it working with this modification...specifically the last line of the formula.
If you know of a cleaner or easier one it'd be appreciated.



=IF(AND(Main!$D$4="TWO+",SMALL(K$7:K$10,3)<K$6),SMALL(K$7:K$10,1)+SMALL(K$7:K$10,2)+SMALL(K$7:K$10,3)-K$6*3,
IF(AND(Main!$D$4="TWO+",SMALL(K$7:K$10,3)>=K$6),SMALL(K$7:K$10,1)+SMALL(K$7:K$10,2)-K$6*2,
IF(AND(Main!$D$4="Two++",SMALL(K$7:K$10,4)<K$6),SMALL(K$7:K$10,1)+SMALL(K$7:K$10,2)+SMALL(K$7:K$10,3)+SMALL(K$7:K$10,4)-K$6*4,
IF(AND(Main!$D$4="two++",SMALL(K$7:K$10,3)<K$6),SMALL(K$7:K$10,1)+SMALL(K$7:K$10,2)+SMALL(K$7:K$10,3)-K$6*3,
IF(Main!$D$4="two++",IF(SMALL(K$7:K$10,3)>=K$6,SMALL(K$7:K$10,1)+SMALL(K$7:K$10,2)-K$6*2))))))