To see less ads Register or Login ----- Daily Fantasy Sports games 18+

D'oh

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

D'oh

Post by blahblah »

I have a simple problem for a wise monkey.

I want to turn a horizontal table into a vertical one - is it a simple Pivot? If so how etc?

1, I am using OpenOffice
2, The Masters start tomorrow, and I am naively running a Comp
3, Yes, putting the "Real" players across the top, and FISO Entrants down the side would have been the sensible thing to do.
4, Yes there must be a simpler way than having cells with x's in to show the selected players, and an "if" condition to get the players score from.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: D'oh

Post by Surprised »

So you currently have FISO names across the top and players down the side?

User avatar
Knulpuk
Dumbledore
Posts: 8986
Joined: 05 Dec 2005, 13:22
Location: MTA (Mid Table Anonymity)
FS Record: Ultra ultra consistent (see above)

Re: D'oh

Post by Knulpuk »

Copy selection eg your row ; paste special values, then tick the: "Transpose" box it will then post a row as a column or vice versa - just switch them round.

That works in Excel...

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

Cunningly deduced, Holmes.

Each FISO has two columns, which are merged. One column has an x denoting if the player (row) has been selected, and a second column with an if x in first column then equals score. The cunning idea was to then just get hold of an alphabetic list and put in the scores in after the Rounds have finished.

I also have summed the scores per FISO bod..........

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

8-) 8-) 8-)

megadyl 10
Angus_1972 11
polarbear147 13
Horton Mumblemouse 14
SHAFTY1 14
scopsm 17
zoolander 17
thelip 18
MU No1Fan 20
WilBert 20
Dad Dancer 21
purge54 21
Groomyd 22
topspur 22
yorksdragon 22
Trickster 23
blah 24
Lister 26
the dude abides 28
delgenus 29
Jonathon 31
The Elkmaster 41
graysman 42
Digsby 44
padmole 45
juckster 49
fox in the box 59
kcjonze 60
Edmonson 72
mowatson 73

With test scores. I had to do another paste from the transposed sheet, as it pulled in all manner of numbers.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: D'oh

Post by Surprised »

you can add the scores into the table with a combine If and Vlookup statement

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

This is good, but it doesn't recalculate, when I change the scores.

Not a massive problem, as I can redo it after each round, but I was thinking - and boy is that dangerous.

Maybe we should have a few Spreadsheets for people running Comps to download from FISO?

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

Surprised wrote:you can add the scores into the table with a combine If and Vlookup statement
Can you do me an example? Using English is fine, rather than a pure syntactically correct type of thing.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: D'oh

Post by Surprised »

blahblah wrote:
Surprised wrote:you can add the scores into the table with a combine If and Vlookup statement
Can you do me an example? Using English is fine, rather than a pure syntactically correct type of thing.
Ok.
I'll assume a few things for the sake of the formula but you can easily adapt.
ScreenHunter_01 Apr. 04 17.52.jpg
=IF(D3="x",(VLOOKUP($D$1,$A$14:$F$15,6,FALSE)),"-")

The range a14 to f15 will be the scores tht you will get from wherever you will get the scores from and you can even automate this to save you typing scores in.
d1 is where the golfers name is so will be changed for each golfer.
You do not have the required permissions to view the files attached to this post.

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

My rows and columns are the other way round.

If I had typed it like that, then the FISO scores would be vertical, and so no problem, lol.

Or should I have transposed the whole table?

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: D'oh

Post by Surprised »

I thought you had transposed them so FISO names were going down?

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

Transposed the table, after a few random ticking and unticking of boxes - I had only done the FISO names and Scores before.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: D'oh

Post by Surprised »

transpose FISO names and golfers into a new sheet but not the data. You may need to merge the cells where the golfers names are again
Then try the formula

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

I transposed the lot, and tweaked the formulae, so all is sort of in order, now. Strangely the sums() were OK but the "if"'s were all over the place, but then I have a "$" in there.

Many thanks all.

User avatar
Rob
Grumpy Old Man
Posts: 3638
Joined: 24 Jul 2009, 10:54
Location: Poster formerly known as SOBD
FS Record: WC2010 FISODAS Champion!!

Re: D'oh

Post by Rob »

I would find it easier (in excel) to put the golfer scores in one column and then the FISO player's picks in another column by typing a 1 if they have selected the golfer. Then each Fisoer's score is a sumproduct formula of their selection column and the player score column.

For example:

Image

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

So SUMPRODUCT multiplies the 1 by the real players score?

Ingenious, if a little late :lol: :lol:

But I would still need to twist the FISO scores to post them here....

I have it kind of sorted, with real players going across, and FISO downwards - my magical "x" puts the players scores into the FISO person's row, and sums at the end.....

Then a simple = to put the score next to the FISO'ite and C&P to here......

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: D'oh

Post by barry »

SOBD wrote:I would find it easier (in excel) to put the golfer scores in one column and then the FISO player's picks in another column by typing a 1 if they have selected the golfer. Then each Fisoer's score is a sumproduct formula of their selection column and the player score column.

For example:

Image

you could do it like that, then a separate section have (in two columns) Fisoer Score and just have

= D2 =E2 =F2 etc in one column.

and = D17 =E17 =F17 in the other.

gives you the nice simple "working out" section and a nice simple "presentation" section

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

I cunningly have my SUM() next to FISO'ite, and Data Sort puts the lot in ascending order...

42 bustrds have signed up :evil: :lol:

User avatar
Mystery
FISO Knight
Posts: 13816
Joined: 13 Oct 2005, 18:33
Location: Just about here
FS Record: 116th in TFF 05/06
Contact:

D'oh

Post by Mystery »

Personally I'd have done this in two lists. One with golfer names and scores and the other with the FISOns and their selections.

The selection list can have data validation on it locking you into the list of golfers and then you just VLOOKUP the score from the golfer list and total it or whatever.

User avatar
blahblah
FISO Viscount
Posts: 108495
Joined: 13 Oct 2005, 18:46
Location: .. he thinks that he knows something which he doesn't, whereas I am quite concious of my ignorance.

Re: D'oh

Post by blahblah »

That sounds like the high-tec version :lol:

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”