Jump to content
DOSBODS
  • Welcome to DOSBODS

     

    DOSBODS is free of any advertising.

    Ads are annoying, and - increasingly - advertising companies limit free speech online. DOSBODS Forums are completely free to use. Please create a free account to be able to access all the features of the DOSBODS community. It only takes 20 seconds!

     

Recommended Posts

4 minutes ago, sarahbell said:

I have an excel file that records when people have taken part in something.
I would like to create a chart showing a weekly average of people taking part.
How do I do this?

(without manually doing lots of fucking about)

I've not used excel since, well, before the clone wars.

On the top menu there should be a chart tab.

Google it you bone idle moo.

Link to post
Share on other sites
2 minutes ago, GBDamo said:

I've not used excel since, well, before the clone wars.

On the top menu there should be a chart tab.

Google it you bone idle moo.

No I've tried charts. I need to collate the data for it to put it into 7 day averages. Else I have 2 months worth of nonsense. I don't know how to do that except manually.

I have looked at pivot tables.

I'm on excel 2003 (I am not a big user of excel at all)
 

Link to post
Share on other sites
26 minutes ago, sarahbell said:

I'm on excel 2003 (I am not a big user of excel at all)
 

I use Excel 2003 for my gambling and trading. I trust it to be reliable, and you don't need to pay an Office subscription. Like in the Ebay thread, prices for licenses have actually gone back up in recent times. 

Link to post
Share on other sites
54 minutes ago, GBDamo said:

How do you enter the data.

It would make your life easier to reverse the sheet and insert a new row, row 1, for each new entry. This way it's a simple table whose formula never changes.

 

It's done from a google form. 
 

Link to post
Share on other sites
49 minutes ago, Thombleached said:

PM me and WeTransfer it to me and I'll sort it out 

Have done!

57 minutes ago, GBDamo said:

How do you enter the data.

It would make your life easier to reverse the sheet and insert a new row, row 1, for each new entry. This way it's a simple table whose formula never changes.

 

It does add a new row for each person who takes part.
But then it's just an insane list of dates. 

Link to post
Share on other sites
25 minutes ago, Ash4781b said:

How much data do you have and are you looking for trends? I had something recently with months and had to adjust out the number of weeks? The invoicing was 4 weekly basis.  It made my head spin!

350 points over a couple of months.

 

Link to post
Share on other sites
8 hours ago, Sideysid said:

Use a pivot table, then a report/chart off of that if need be

To get the average value here’s an example =AVERAGEIF(A1:A10,"Pen",B1:B10)
 

CC6FFD1D-C8D5-4E39-B3A4-150D82E6F47F.png

The issue is that Sarah has to average over a week, whereas it sounds like she only enters data for any particular day. 

The way I would do this is with a lookup table. In one sheet, populate column A with a list of sequential dates. In column B have a formula (Vlookup) that looks at Sarah's data, and pulls the data across when the dates match, then populates all the other rows with zero. Then  in column C averages the 7 days data from column B, which you can then plot against the date. It's literally a 2 minute job. 

A bit more advanced would be to use the excel date functionality to identify the Sundays and average the previous 7 days data for each Sunday. Again a 2 minute job when your familiar, probably take an hour or two having to Google each step.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...