[GlennCMC70]

i use Excel to track season points. i know the every simple stuff like Sum and stuff.
for the final season points tally, i have to go in and manually subtract 20% of the lowest scores for each person.
i want a formula that will automatically drop those scores based on cell or text color for that cell.
i've googled till my eyes are falling out. i've found some stuff, but i just don't know enough about excel to understand it all. i've tried a few examples given w/ no luck.

anyone here able to help?

[StanIROCZ]

I'm not sure exactly what you are trying to do, but here are couple suggestions that might help you out.

small - returns the k-th smallest value in a data set
min - returns the smallest value in a data set

using those tools, you can try some trickery.

=SUM(A1:F1)-MIN(A1:F1)*0.2 this would subtract 20% of the smallest score

=SUM(A1:F1)-SMALL(A1:F1,1)*0.2 this gives you the same answer as above since the '1' means the smallest value in the list

=SUM(A1:F1)-SMALL(A1:F1,1)*0.2-SMALL(A1:F1,2)*0.2 this is an add on to above, it subtracts 20% from the smallest and 2nd smallest event


this is how I interpret what you are trying to do, but could be wrong. you should be able to make a slight change to get what you want.

This post has been edited by StanIROCZ: Aug 5 2009, 06:25 PM