Excel help... i suck

[Borgsta]

Alrighty.

I have a sum function going to calculate all the scores for a Dreamteam thing I am doing. However I would like to have it automatically minus the bottom 7 scores.

Is there any way to do that?

 

[Vonsche]

You'll need to minus the cells you want taken off at the end of your formula.

eg: =sum(a1:a10)-a11-a12-a13-a14-a15-a16-a17

 

[roguless54]

You'll need to minus the cells you want taken off at the end of your formula.

eg: =sum(a1:a10)-a11-a12-a13-a14-a15-a16-a17

I think that would do the trick, you should also be able to shorten that again to be easier read on excel to

=sum(a1:a10) - (a11:a17)

EDIT: just tested and thats a negative, only deducts the first cell's value, hmmm

 

[jim boy]

I think that would do the trick, you should also be able to shorten that again to be easier read on excel to

=sum(a1:a10) - (a11:a17)

EDIT: just tested and thats a negative, only deducts the first cell's value, hmmm

shouldn't that be =sum(a1:a10) - sum(a11:a17)

 

[Borgsta]

Yeah I had done that already, but due to it being Dreamteam the lowest cell is going to change every week. I suck at Excel but not that much!

Ideally I'd love Excel to recognise the 7 lowest scores and disregard them

 

[roguless54]

shouldn't that be =sum(a1:a10) - sum(a11:a17)

Yes spot on there Jim Boy, thanx for that works a treat, knew i was close.

 

[spitlizard]

Yes spot on there Jim Boy, thanx for that works a treat, knew i was close.

That is such a shitty part of excel...you can be soooo close but it still comes up with an error

 

[de_LICA_ish18]

Not sure if this will help as I'm not sure how you have things set up and I've never used Dream Team, so if it comes back with duplicate whole numbers ranking may not work

Possibly set up a ranking

example: Col B is the name of data and Col C is your score

Col A____________Col B_Col C
=rank(c1,$c$1:c$5) Carl 23 (rank = 2 in col a)
=rank(c2,$c$1:c$5) Coll 29 (rank = 1 in col a)
=rank(c3,$c$1:c$5) Ess 8 (rank = 3 in col a)
=rank(c4,$c$1:c$5) Eag 7 (rank = 4 in col a)
=rank(c5,$c$1:c$5) Freo 2 (rank = 5 in col a)

rank will change each time Col C score changes

Then you can set up a sumif for each ranking ie:

for ranking 1

=sumif(a1:a5,1,c1:c5)

Do this for each ranking excluding the lowest 7 and then sum final total. This way, each week it will exclude the lowest 7 when data is updated.

Again depends on what your set up is if this will work automatically for you or not. Also if you have repeat numbers it will stuff up the ranking. Not sure if dream team works to whole numbers or decimal points??

Not sure if it will make sense to you, but there you go anyway. I can't think of any other way to do it.

 

[DonsRule]

Alrighty.

I have a sum function going to calculate all the scores for a Dreamteam thing I am doing. However I would like to have it automatically minus the bottom 7 scores.

Is there any way to do that?

=sum(a1:a10)-small(a1:a10,1)-small(a1:a10,2)-small(a1:a10,3)-small(a1:a1,4)-small(a1:a10,5)-small(a1:a1,6)-small(a1:a10,7)

the number enter the comma in the 'small' well remove the related number, so put i 4, and it'll find the 4th lowest humber in the list.

 

[Borgsta]

The last 2 seem what I am after. I'll try it out at work on Monday.

Thanks a lot.

 

[weevil]

Pretty sure the most concise way would be to use PERCENTILE as the criteria for a SUMIF. Should be 1 short relatively simple formula.

Can’t be arsed doing it myself.

 

[Borgsta]

=sum(a1:a10)-small(a1:a10,1)-small(a1:a10,2)-small(a1:a10,3)-small(a1:a1,4)-small(a1:a10,5)-small(a1:a1,6)-small(a1:a10,7)

the number enter the comma in the 'small' well remove the related number, so put i 4, and it'll find the 4th lowest humber in the list.

did that and it worked perfectly.

Thanks.