Minimum Point loss

[Travisallen2007]

Yall have been amazing at the help!! Thank you so much! I was wondering if you could help with one final question:

I am removing points from a scorecard but only want it to remove a maximum of 10 points (Even though they can potentially lose more)

EX: If they have an A they lose 5 if they have a T they lose 2 up to a 10 point max.

I have set up a Sumproduct formula to count the amount of times they have an A or an T but dont know how to stop it if it goes over 10.

Thanks again for all the help!

 

[Aladin Akyurek]

Something like...

MIN(calculation,10)

 

[Travisallen2007]

The current count formula i have is:

=SUMPRODUCT((LEN(B3:AF3)-LEN(SUBSTITUTE(B3:AF3,$AH$2,"")))/LEN($AH$2))

 

[Travisallen2007]

ok cool - i can make that work!

 

[jtakw]

Hi,

Referring to the sample in your other thread, something like this:

Book1
	A	D	G	H	I	J	K	L	M	N	O	P	Q
2	Person		Total Scores		Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7		Final Score
3	A		77		A	A		T					67
4	B		161			A			T				154
5	C		84		A		A			A			74
6	D		154								T		152
7	E		91										91
8	F		147		T		T						143
9	G		98			T				A			91
10	H		140				T	T					136
11	I		105								A		100
12	J		133										133
13	K		112			T	T	A	A	A			102
14	L		126		A								121
15	M		119							T			117
Sheet17

Cell Formulas
Range	Formula
Q3	=G3-MIN(SUM(COUNTIF(I3:O3,"A")*5,COUNTIF(I3:O3,"T")*2),10)

 

[Aladin Akyurek]

ok cool - i can make that work!

You are welcome.

 

[Travisallen2007]

That is great! That takes place of my side formulas all together!! Man your good!

 

[jtakw]

That is great! That takes place of my side formulas all together!! Man your good!

Not sure if you're talking to me, but You're welcome and glad it's working for you.

 

[Travisallen2007]

Not sure if you're talking to me, but You're welcome and glad it's working for you.

Yes you! :D Some really great suggestions I really appreciate it! Really helped me put some good quality touches on this spreadsheet.