Calling Excel programmers

[Steve Maskery]

I'm just reading about PivotTables. I think that will do it.
Thank you very much indeed.
S

 

[SimonB]

Hmm, no I don't think it's a running total I need.

In English:

For each individual Date Value in Col F (Sale Date), Sum the values in Col A (Sales) where the Col A's corresponding Date Value (Sale Date) equals the Date Value (Sale Date) under consideration.

So I end up with 2 columns, Date and Total Sales on that date. I could also do the same thing with months. I can then plot Sales against Dates

Clear as murky mud viewed through dark glasses at midnight?
S

Gotcha, I had thought for some reason that each row was a date and total sales for that date. So you mean a 'group by' in SQL terms?

 

[wizer]

This takes me back. I used to know quite a bit about excel and I actually used to enjoy it! :shock: It's all seeped out of my damp squib of a brain now. I barely remember HTML, at times...

 

[Steve Maskery]

I just like to say ta for all the help. I've got what I wanted, although now I'm not quite sure what to do with it!

One thing I've found is much frustration with the Date formatting. I've been trying to group sales by date (so if I have 6 sales on one day, I get the total for that day) and also by month (ditto). But it wasn't happening for the month, just the day!. It's because changing the format to MMM/yy does not alter the underlying data, which remains dd/mmm/yyyy. I've had extract the individual bits and string them back together again. What a faff. I bet there is an easy-peasy way to do it if only I was heducated.

Anyway, I now have a graph, which is what I wanted.

Thank you all.
S

 

[xy mosian]

Steve, It seems as if you may be better off using some dedicated accounting software. Unfortunately all the ones I have looked at are far too sprawling, and take an age to set up. One application that gets good reviews is GnuCash from, http://www.gnucash.org/. Now I haven't tried it, but it could be just what is needed. Your requirement for sales grouping must be exactly what these things are designed for, account details by date etc.. Any one got experience of something small and light on its feet?

xy

 

[SimonB]

I just like to say ta for all the help. I've got what I wanted, although now I'm not quite sure what to do with it!

One thing I've found is much frustration with the Date formatting. I've been trying to group sales by date (so if I have 6 sales on one day, I get the total for that day) and also by month (ditto). But it wasn't happening for the month, just the day!. It's because changing the format to MMM/yy does not alter the underlying data, which remains dd/mmm/yyyy. I've had extract the individual bits and string them back together again. What a faff. I bet there is an easy-peasy way to do it if only I was heducated.

Anyway, I now have a graph, which is what I wanted.

Thank you all.
S

Steve, I think this might help - http://www.mrexcel.com/articles/pivot-t ... -month.php

I just tried it and it works, it allows the date fields in a pivot table to be grouped in various ways, including summing day totals into their equivalent month.

I use Office 2007 and I think this may be what the example is using, not sure what you use and if it's an earlier version, whether it has this feature.

 

[matt]

Out of interest, what's the objective of the analysis? Sales by day is quite fine level of granularity - potentially useful if you're trying to see if day of the week or time of the month influences purchases but, unless your production leadtime is less than a week it's not much use for replenishment purposes (at least not with volume). I'm wondering whether dropping analysis by day will enable better data handling further up the line and facilitate easier manipulation of the figures when it comes to presenting meaningful data.

 

[Steve Maskery]

Matt
I wasn't after Sales by Day, just Sales by Month. I simply discovered that when I did the PT it did Sales by Day (because that was the real value of the data), and didn't when I changed the date display to MMM/YY.

But that link of Simon's looks to be useful, I'll definitely have a look later.

Cheers
Steve

 

[SPinonit]

Steve

You should be able to copy and paste the function below into any cell on the first row, ideally in the column in which you want your dates. Ive used your exact text string to check this and it does work on my version of Excel (2003). Try it and see, if it does what you need it to, I will try to write a macro to automate the entire process at the click of a button. I like playing with Excel, so I quite enjoy challenges like this.

Function:

=DATE(MID(A1,9,4),VLOOKUP(LEFT(A1,3),{"Jan","1";"Feb","2";"Mar","3";"Apr","4";"May","5";"Jun","6";"Jul","7";"Aug","8";"Sep","9";"Oct","10";"Nov","11";"Dec","12"},2,0),MID(A1,5,2))

I hope this helps, let me know if it does and you would like me to try and crack the macro.

Steve

 

[wizer]

We've got a guy at work who is so enthusiastic about excel I think he has a poster on his bedroom wall (in fact his name is Steve :shock But he's great for stuff like this. Everyone should\needs to know an Excel guru, Fact.