A calc n00b trying to do something out of my reach.

Discuss the spreadsheet application

A calc n00b trying to do something out of my reach.

Postby Evan » Tue Jun 17, 2008 11:14 pm

Hi guys. I've been putting off a project that needs this for some time, because I couldn't figure out how it would work, while being as automatic as possible. So I brought it here.

Basically, what I'm trying to do is create a spreadsheet where numbers are input, while elsewhere, those numbers are then grouped together into different lists that are averaged to be as close as possible (given the original data).

It would start looking something like this:

Original data:
A 79
B 32
C 10
D 84

Then be automatically broken up into groups sort of like this.

Group 1:
C 10
D 84
Avg: 47

Group 2:
A 79
B 32
Avg: 55.5
Evan
 
Posts: 3
Joined: Tue Jun 17, 2008 10:49 pm

Re: A calc n00b trying to do something out of my reach.

Postby Villeroy » Wed Jun 18, 2008 9:30 am

See attachment.
And download/file.php?id=361 for alternatives.
Attachments
subtotal_groups_names_values.ods
lookup group membership and create subtotals
(17.22 KiB) Downloaded 164 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: A calc n00b trying to do something out of my reach.

Postby Evan » Thu Jun 19, 2008 8:57 pm

Thank you for the quick reply.

I took a look at this, played with it for a while, and I don't think it was what I was looking for unfortunately.

I'll tell you what I'm trying to do. I'm looking to take a list of players and a number (their overall ability) and for the spreadsheet formulas to break them into as equal of teams as possible.

More like this on a large scale:
Original data:
John 76
Mike 61
Dave 73
Joe 58

And end up with this:
Team A
John 76
Joe 58
Avg: 67

Team B
Mike 61
Dave 73
Avg: 67
OOo 3.0.X on MS Windows Vista
Evan
 
Posts: 3
Joined: Tue Jun 17, 2008 10:49 pm

Re: A calc n00b trying to do something out of my reach.

Postby keme » Fri Jun 20, 2008 12:33 pm

One viable approach (if teams always have an even number of members):
  • Make a sorted list (use the RANK() function to establish a sequence number if you don't want to alter the list sequence, but then you need to catch the situation of several people with equal ranking).
  • Pick pairs that are at the same distance from the middle of the list. (So if 8 people are listed, the first pair would be number 4+5, the next number 3+6, etc.)
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3395
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: A calc n00b trying to do something out of my reach.

Postby keme » Fri Jun 20, 2008 2:46 pm

A suggestion is attached.
 Edit: Removed. See later posting... 

There are a few things you need to consider before you modify the attached spreadsheet:
  • If you want to have more or fewer participants, insert/delete rows inside the list. Don't just blank them out or add new rows at the bottom.
  • Don't move the Teams list away from column A.
  • If there are duplicate names, the calculation of average team strength is not reliable.

A useful exercise would be to locate the reasons for the above caveats...
Last edited by keme on Mon Jun 23, 2008 10:54 am, edited 1 time in total.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3395
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: A calc n00b trying to do something out of my reach.

Postby Evan » Sun Jun 22, 2008 11:27 pm

Thank you keme, excellent. I'm going to mark this as resolved but, I'd like to ask you, how do you modify it. Add, subtract players and alter the size and number of teams?
OOo 3.0.X on MS Windows Vista
Evan
 
Posts: 3
Joined: Tue Jun 17, 2008 10:49 pm

Re: A calc n00b [ ... ] Warning - exercise spoiler

Postby keme » Mon Jun 23, 2008 10:53 am

First, the original submission has a bug in the range used for "rank arbitration", making it fail sometimes. Corrected here (I hope...)

I made a few shortcuts to facilitate copying of formulas.
When selecting teams, the "rank displacement" depends on the team column, so in column A you will have the 1st above and below the middle of the ranking. In column B the 2nd, etc. That means if you want to extend the teams list, you just copy the 3 bottom rows of column H as far as you need to the right. If you want a teams list elsewhere, you probably need to alter the formulas. You can safely overwrite the team deviation box when copying, as it doesn't serve any purpose other than displaying the standard deviation in team strength.

If you insert rows below a range, you start outside the range. That means the range is not expanded in formulas. If you insert new rows inside the range (between row 2 and 17 in the original file), all formulas using that range will adjust to the new range. Right click in the row numbers to insert new rows.

As for larger teams, if they have even numbers you could just combine the pairs to make teams of 4,6,8..., or you could insert the pairs as source of a new (identical) selector sheet to have better weighted setups for teams of 4 (and with one step further you have teams of 8, etc.)

In any case, with more than two players there are more options than the pairing that we examine here, so you're not certain to get a "best match" anymore ("fairly good match" at best). For that you need something more advanced than the list approach.
Attachments
bestmatch.ods
(11.31 KiB) Downloaded 110 times
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3395
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Calc

Who is online

Users browsing this forum: No registered users and 32 guests