Jump to content

Excel question


BOF

Recommended Posts

And no I don't want alternative suggestions to using Excel ...

So, I have the chart below.

f0c2873dee535160d14c8a5238d62bb4.png

The games are along the bottom 1-46.  Super.

The days of the week are up the side, 1-7.

BUT ...

Excel day numbers are normally 1=Sunday to 7=Saturday.

I don't want that.  In order to have a more sensible "days we play games on", I want MY footballing week to start on 1=Friday to 7=Thursday.

Now the numbers in the above graph DO represent my numbering.

BUT ...

Here's the question.  How do I put the text Friday thru Thursday on the Y-axis above in the manner that I want it (Friday at the bottom).  I can't do it via a normal "Number->Date->"ddd"" method otherwise it will default to the Excel day numbering and I'll end up with Sunday on the bottom and Saturday at the top.  I can't figure out how to tie my new numbering system to my custom days.  I also have a 2-column vlookup-type table on the sheet with the day number beside my day name, but I can't get the chart to use it.

Google has been less than helpful so far.

Anyone know how?

Link to comment
Share on other sites

frig it, like this fellow has done.

http://peltiertech.com/text-labels-on-vertical-axis-in-excel-column-chart/

Quote

When analyzing survey results, for example, there may be a numerical scale that has associated text labels. This may be a scale of 1 to 5 where 1 means “Completely Dissatisfied” and 5 means “Completely Satisfied”, with other labels in between. The data can be plotted by value, but it’s not obvious how to place the text labels on the chart in place of the numerical labels on the vertical axis....

[goes on to tart about making it as he wants]

 

  • Like 1
Link to comment
Share on other sites

Thanks Blandy. It looks like that might do it. It's some effort to do something so simple though so I don't feel quite so silly not knowing :D And Excel is so good at everything else!

Link to comment
Share on other sites

Could you use bigger numbers, so if 1=Sunday, you use the number range 6 (for Friday) up to 12 (for the following Thursday) set a manual min/max (6 and 12) for the Y axis, and have the Y axis cross the X at value 6?

 

Edit, or you could use another column of values based off your 1-7 values (which'll equal each row's value +5), format it against the the "dddd" date format and use as the data series for the graph, if you'd prefer to input values against your 1-7 range.  This is assuming you've 2 columns, 1 for the games, and the other for the day of the week, with each row corresponding to a pair of game/day values. 

Edit 2:  Hopefully the attached image will better explain it.  (For reference, column E shows the "workings out" (i.e. adding 5 to your input), whilst F shows those values formatted as "dddd")

 

excel01.png

Edited by CardiffGreens
Further terrible explainationing...
  • Like 1
Link to comment
Share on other sites

20 hours ago, CardiffGreens said:

Could you use bigger numbers, so if 1=Sunday, you use the number range 6 (for Friday) up to 12 (for the following Thursday) set a manual min/max (6 and 12) for the Y axis, and have the Y axis cross the X at value 6?

 

Edit, or you could use another column of values based off your 1-7 values (which'll equal each row's value +5), format it against the the "dddd" date format and use as the data series for the graph, if you'd prefer to input values against your 1-7 range.  This is assuming you've 2 columns, 1 for the games, and the other for the day of the week, with each row corresponding to a pair of game/day values. 

Edit 2:  Hopefully the attached image will better explain it.  (For reference, column E shows the "workings out" (i.e. adding 5 to your input), whilst F shows those values formatted as "dddd")

 

excel01.png

Ooh I'll have a look at this Monday. cheers CG.

Link to comment
Share on other sites

35 minutes ago, blandy said:

Slightly off topic, but this article might make you feel wiser. From yesterday's i.

Are they just stealing content from us? Another "newspaper" to never buy.

Link to comment
Share on other sites

2 hours ago, limpid said:

Are they just stealing content from us? Another "newspaper" to never buy.

No. They're no more stealing than when someone on here quotes from a newspaper article or website. They do the same as us - provide a source and author and small extract.

Link to comment
Share on other sites

3 minutes ago, blandy said:

No. They're no more stealing than when someone on here quotes from a newspaper article or website. They do the same as us - provide a source and author and small extract.

They could provide the URL though - even if it's just for the site. Like other papers have in the past and like we require of our posters.

Link to comment
Share on other sites

They name the site, which is reasonable for printed media, IMO - Whether they write "The Daily Telegraph" or "Villatalk" people can see and unless they're, er, untroubled by the ravages of brainpower, they can see where the source of the quote/extract was. Click through not working on tree based media. It seems fair to me. But only one view, I suppose.

Link to comment
Share on other sites

It's better than nothing at all, but they didn't put "VillaTalk", they put "Villa Talk" which has quite different search results. At the moment we are top result on an anonymous search for either, but that's not always true like when a pop star has been talking about their new villa.

Link to comment
Share on other sites

1 hour ago, limpid said:

It's better than nothing at all, but they didn't put "VillaTalk", they put "Villa Talk" which has quite different search results. At the moment we are top result on an anonymous search for either, but that's not always true like when a pop star has been talking about their new villa.

Yeah, agreed they should ideally have said "VillaTalk". Still, this isn't helping BOF with his spreadsheet!

Link to comment
Share on other sites

  • 2 weeks later...

OK, OK, I've got another one, although I suspect this one is beyond what excel tries or even wants to do.

Here's my graph.  It's very basic.  It shows attendance per game.

2c2dee64cdc399ddbe581b499fdde100.png

The question is, can I dynamically change the colour of the bars representing games that were televised, leaving the non-televised games as blue.  This would just make it visually easier to spot a general trend for attendances of games that were on the tellybox.  As I say, I suspect I can not.  I can't even see how to manually change an individual bar.

Link to comment
Share on other sites

16 minutes ago, BOF said:

I can't even see how to manually change an individual bar.

When you click on your bars, they'll all be selected.  Single click again on the one you want to change and it'll only select that one - the right click menu will then have "Format data point" as the bottom option - allowing you to change that bar's colour in the fill menu (and if you leave the "format data point" option open, you can select another individual bar to change that one as well - without having to close the option menu and reopen it).

Link to comment
Share on other sites

×
×
  • Create New...
Â