Racketlon Excel ranking. Problem with the average and if function ??

**throary** · 11-22-2012, 12:45 PM

Hello!

I am doing a ranking for my racketlon club for the moment.

I have some problems that I hope you can help me with. The ranking uses the average function over 3 years to sum up the ranking points. The first year the points counts to 100 %, the second year it counts to 50 % and the third year 30 % counts to the ranking. In cell D4 is the date for the last tournament. On each tournament its a date, so for example "Distriktsmästerskapen 2012" from row AR to AX has its date in cell AW4. Cell AX5 tells how many days ago the tournament was compared to the last tournament date that is in D4. AX8 steers how much points in % the tournament weights, in this case 100 % because its under 1 year ago. In row G-I do I have the average year points. I have manually put in the formula in G-I cause I dont know how to tell excel that everything within 365 days shall be in the year 1 column, and the tournaments for year 2 column shall be between 366-730 days and so on. Is it the IF function ?

Then one question more. Each player must play at least 2 tournaments a year, and if they just play one tournament per year, they will get their points multiplicated with 0,4. So for example Player 1 that only have played one tournament in year 1 has 2000 points in the G21 cell. What I have to do now is to manually change his score to 2000x0,4... And to do that for 130+ players are a big time waist! Is there any way in excel to get a function to do this automatic ?

Also the last question for now, if a player has played none tournament in a year, I get a #DIVISION/0! error code. I can solve this if I write a 0 in for example cell X21, cause thats a tournament in year 2. But again, it must be some way in excel to do that this error dont appear ?

I will attach a example file.examplerank.xlsb

I know that it may be hard for you guys to put you into this, it might be a bit complex...

Hope that you understand what I meant with this

Thanks in advance / Daniel

**rtl** · 11-23-2012, 09:01 AM

Hi Daniel,

I'm sure there's an answer to all three of your questions, but let me answer the easy ones now and I'll work on the other one and get back to you later.

The #DIVISION/0! problems can be solved by testing for error conditions before the division is performed. In G21, you'd use:
=IF(ISERROR(AVERAGE(K21:S21)),0,AVERAGE(K21:S21))
which results in 0 if there's a divide-by-zero condition, and your required average if there's not.
You'd edit H21 and I21 similarly, and then select and drag-copy those three cells downwards to extend the formulae to all the cells below.

May I mention that in the example you've provided, I think your averaged cell ranges may be incorrect. Looking at your tournament titles in L20:AQ20, I think your 2012 tournaments are in L:R, your 2011 tournaments are in S:Z and your 2010 tournaments are in AA:AI. When you average them in G21:I21, I would imagine that G21 should therefore be:
=IF(ISERROR(AVERAGE(L21:R21)),0,AVERAGE(L21:R21))
and H21 and I21 would average values from the above-mentioned columns.
Sorry if I've assumed anything incorrectly there - ignore my comment if so!

Moving on to your requirement to apply a 40% weighting if the player has only played one event, you can check the number of cells with numeric content in your range before you average it, using the COUNT function, so that would change G21 to:
=IF(ISERROR(AVERAGE(L21:R21)),0,IF(COUNT(L21:R21)=1,AVERAGE(L21:R21)*0.4,AVERAGE(L21:R21)))
and H21 and I21 would change similarly, and then all three would be extended downwards as before.

The automatic date detection requirement is a little more complicated so I'll respond to that separately.

Great to see so much effort being put into racketlon administration - keep it up! If you're playing in, or involved with, the World Championships in Stockholm next weekend, then good luck.

Richard

**throary** · 11-23-2012, 05:32 PM

Hi Richard and thanks for the fantastic effort you have put in here!

This is great help. About the average cell range you mentioned, I count the start date from cell D4, since that was the last tournament we had. Then I am counting 365 days back to get all the tournaments during year 1. But I understand how you thought there.

Yes I will play in Stockholm, in the B class. It will be fun I hope

I have also been to England and played 2 times in the English Open, I think 2007 and 08 but not sure

Are you also playing ?

Once again, thanks for the input!

/Daniel

**throary** · 11-23-2012, 06:15 PM

Hello again.

Tested the function you wrote and it appears that ,0,AVERAGE in the formel is causing problems ?? Anyone know why ?

/Daniel

**daddylonglegs** · 11-23-2012, 08:10 PM

For your region you probably need semi-colons instead of commas for separators in formulas, try this version

=IF(ISERROR(AVERAGE(K21:S21));0;AVERAGE(K21:S21))

or if you have Excel 2007 or later you can shorten that by using IFERROR like this

=IFERROR(AVERAGE(K21:S21);0)

**throary** · 11-23-2012, 08:52 PM

Thanks daddylonglegs and sorry for my nobiness

I got the error code #NAME? cause I guess my excel 2007 only supports swedish function names. So I found a page http://www.piuha.fi/excel-function-n...a-english.html there I have all the english functions converted to swedish.

Thanks to Richard also, my formel is as follows =OM(ÄRFEL(MEDEL(K21:S21));0;OM(ANTAL(K21:S21)=1;MEDEL(K21:S21)*0,4;MEDEL(K21:S21)))

What would I do without this forum ?

/Daniel

**rtl** · 11-25-2012, 08:43 PM

Hi Daniel,

I was hoping to point you at a solution for your yearly averages, which replaced each AVERAGE function with a sum/#samples fraction as follows (for G21):

Sum part = SUMPRODUCT((AR$5:GJ$5>0)*(AR$5:GJ$5<=365)*(AR21:JG21))
#Samples part = SUMPRODUCT((AR$5:GJ$5>0)*(AR$5:GJ$5<=365))

However, although SUMPRODUCT is supposed to treat non-numeric values as zero, when you stop comma-separating the ranges and start to use the multiplier asterisk, this treatment ceases and the end result is #VALUE! because of the non-numeric text in your Category column e.g. Elite. I'm guessing this is because SUMPRODUCT only considers it has one array to deal with, and the multiplier function is happening before SUMPRODUCT is applied to the result.

I'm reluctant to suggest you stop using your Category column and just enter the numeric Category Code directly, as it removes the user-friendliness, though that would make the formulae work.

Perhaps daddylonglegs can come up with an alternative usage? I hope you can follow the problem from my description.

Regards

Richard

**daddylonglegs** · 11-26-2012, 06:36 PM

Originally Posted by rtl

I'm guessing this is because SUMPRODUCT only considers it has one array to deal with, and the multiplier function is happening before SUMPRODUCT is applied to the result.

Exactly right, Richard

You can use a comma before the sum range then text will be ignored, i.e.

=SUMPRODUCT((AR$5:GJ$5>0)*(AR$5:GJ$5<=365),AR21:GJ21)

**rtl** · 11-27-2012, 01:39 AM

Thanks DLL - I'd tried with all commas and with all asterisks, but not your combination, which of course works (when I'd got my GJs corrected to JGs!). My #Samples formula needed a bit of work too, but they ended up as:

Sum part = SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365),AR21:JG21)
#Samples part = SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365)*(AR21:JG21>0))

The replacement for the AVERAGE function is therefore Sum/#Samples. I've re-implemented the error checking by simply checking #Samples for 0, 1 and >1 to return 0, Average*0.4, and Average respectively. It's getting longer and complicated, but G21 is now:

=IF(SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365)*(AR21:JG21>0))=0,0,IF(SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365)*(AR21:JG21>0))=1,SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365),AR21:JG21)/SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365)*(AR21:JG21>0))*0.4,SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365),AR21:JG21)/SUMPRODUCT((AR$5:JG$5>0)*(AR$5:JG$5<=365)*(AR21:JG21>0))))

and I've left the date check values as multiples of 365, so H21 becomes:

=IF(SUMPRODUCT((AR$5:JG$5>365)*(AR$5:JG$5<=365*2)*(AR21:JG21>0))=0,0,IF(SUMPRODUCT((AR$5:JG$5>365)*(AR$5:JG$5<=365*2)*(AR21:JG21>0))=1,SUMPRODUCT((AR$5:JG$5>365)*(AR$5:JG$5<=365*2),AR21:JG21)/SUMPRODUCT((AR$5:JG$5>365)*(AR$5:JG$5<=365*2)*(AR21:JG21>0))*0.4,SUMPRODUCT((AR$5:JG$5>365)*(AR$5:JG$5<=365*2),AR21:JG21)/SUMPRODUCT((AR$5:JG$5>365)*(AR$5:JG$5<=365*2)*(AR21:JG21>0))))

and I21 becomes:

=IF(SUMPRODUCT((AR$5:JG$5>365*2)*(AR$5:JG$5<=365*3)*(AR21:JG21>0))=0,0,IF(SUMPRODUCT((AR$5:JG$5>365*2)*(AR$5:JG$5<=365*3)*(AR21:JG21>0))=1,SUMPRODUCT((AR$5:JG$5>365*2)*(AR$5:JG$5<=365*3),AR21:JG21)/SUMPRODUCT((AR$5:JG$5>365*2)*(AR$5:JG$5<=365*3)*(AR21:JG21>0))*0.4,SUMPRODUCT((AR$5:JG$5>365*2)*(AR$5:JG$5<=365*3),AR21:JG21)/SUMPRODUCT((AR$5:JG$5>365*2)*(AR$5:JG$5<=365*3)*(AR21:JG21>0))))

All best demonstrated by Daniel's example file, with those cells edited and copied to all cells below in columns G,H,I, as attached here examplerank.xlsb

Regards - Richard

**throary** · 12-02-2012, 11:35 AM

Ty guys! I mark this as solved, great help! Btw, I was placed 9:th of 64 in the B class at WC this weekend.

/Daniel

Racketlon Excel ranking. Problem with the average and if function ??

LinkBack

Thread Tools

Rate This Thread

Display

Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Re: Racketlon Excel ranking. Problem with the average and if function ??

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions