MS Excel / VBA help thread

That table there is made up data.

I plot the number of cases as a line chart, with the time on x, cases on y.

what i've been asked is this:
the graph will show all cases from today's date to 24 months ago.

The user will put in a number on sheet one. This will be from 7 - 18, so they can look back 7 - 18 months.
this value will calculate an average number of cases (and also a +- 3.33) based on
that number
AND
if there is a "y" in column "include".

so far so simple.
however,
If ‘n in the include column’ is 1 or more (i.e. one or more months are excluded, due to an outbreak skewing the numbers), then the number of months excluded will be replaced by previous months in the ‘y in the include column’.

in the example above, if we wanted to look at the last 14 months we'd look from 01 01 15 - 01 11 13.
But there are "n" in Dec and Nov 14 and also Mar- Jan 14 (this is probably extreme, it's a random distro based on "randbetween" y/n to generate the numbers (as the cases were also randbetween 0-100).

I'm being asked to create a full 12 months of y to generate the lines i need. Now.
How can i build a window of 12 months to take an average from, I think i could do it two ways:
generate another table with >24 months in it, and then average only if "y".
generate one table with > 24 months in it, some sort of offset to create a dynamic window

any advice would be great...

Love stuff like this. I think it's formula'able.

If ‘n in the include column’ is 1 or more (i.e. one or more months are excluded, due to an outbreak skewing the numbers), then the number of months excluded will be replaced by previous months in the ‘y in the include column’.

in the example above, if we wanted to look at the last 14 months we'd look from 01 01 15 - 01 11 13.
But there are "n" in Dec and Nov 14 and also Mar- Jan 14 (this is probably extreme, it's a random distro based on "randbetween" y/n to generate the numbers (as the cases were also randbetween 0-100).

So in your example, there are five 'n's in the range to average. What would these be replaced with?

I think it's in here:
"then the number of months excluded will be replaced by previous months in the ‘y in the include column’"

But can you specifically outline what 01-Dec-14, 01-Nov-14, 01-Mar-14, 01-Feb-14 and 01-Jan-14 would be replaced with.

I'm not sure if I'm interpreting the requirements correctly but I'd be tempted to add in another column and use an if statement so that if it is "n" it looks up the value for the previous year (vlookup works on hidden cells so your filter won't make a difference)

@christianSpaceman
I think the problem is that in my (extreme) example there isn't anything to go with.
The person setting me the problem has thought, well, we'll just take the next available 3 months. But not thought about, "what if there are two spikes in the period that have to be reported, what then"?

@aggi
I think this is the same problem. What if the last year's data for the same point also has "ignore" next to it?

The most likely situation is that there will be a spike in each reporting period, but it will only be one spike (of variable length).

This is my problem with it, what do you replace the data with?
Do you use values for the same months but different years?
or
Do you use the next available months?

Personally, I wanted to create a chart with control limits for all the data and a separate chart with just the data excluded.

^ Other than the now personal satisfaction of solving this, there reaches a point where the time spent working on this will far outweigh the benefit to those requesting it surely?

I've knocked something together that has a column to allow you to make the decision on what to replace the values with later - it seems like it's key here. It currently uses 'next available y' - though I'm not sure if that's useful for your application.
More complex might be 'same value as last year scaled by any annual trend'?

Though I'm not sure that it really takes you any further than where you already were feel free to pm me an email address to send to.

The person requesting it wants it.

It sounds like less of an Excel problem (I think that part is easily solved using if statements and lookups or similar) and more an issue with clearly defining what you actually want to do in terms of replacement data.

You basically need to come up with a set of rules in terms of what you want to replace the ignored data with, e.g first option is to use current data, second option is one year before, third is ...

Once you've done that then you can do the Excel work which probably won't be too tricky. Admittedly, I'd be tempted to consider how useful the whole thing is (probably not what you want to hear), once you get to the stage of manually excluding and replacing your data because it doesn't fit the pattern then you've almost decided what you expect the output to be.

Yeah. I get that. I said "oh, yeah, i can do that". Then when faced with it I can, and I can do the bits around it, but the bit about "look, you're going to have a problem when you need to exclude this and this and this" hasn't really been communicated by me properly i think.

Understood, but do they appreciate the costs (your time) going into developing this vs the eventual benefit to them / the organisation?

Probably not.

I seem to remember there was a way to do this without macros.

I have a set of data which I do a pivot table on. I don't want to select the whole column as then the fields default to count rather than sum and I get blanks that need to be filtered out. Plus the headers aren't necessarily in Row 1.

Data is being added at the bottom of the data set. I thought there was a way to get the pivot table to update to add that new data (rather than manually redoing the data source each time) when refreshed but I can't seem to find it.

I know I can do it via a macro, I just have memories that there was an easy way to do it.

If you select the source data and then 'Format as table' and check the headers box then you should be able to reference a column of the table by name.
E.g. if the table was called Table1 and you had firstCol, secondCol, thirdCol you could
=SUM(Table1[secondCol])

And I think the Pivot table then references it by table name and column rather than some absolute position, so when the table grows automatically you should be good.

Offset:

http://excelpivots.com/excel/pivot_table_expanding_data_ranges/

^ i do that a lot.

Cheers all, the Offset was the one I was thinking of. Tables are easier but half of my data comes from another data connection so I'm not sure whether it will allow me to set up another table where only half is imported from an SQL connection..

I will experiment.

couldn't you have three tables? (thus revealing myself to be cackhanded)
one for one half, one for the sql connection, one for the merge?

one for one half, one for the sql connection, one for the merge?

This would be easier to maintain, rather than trying to fix it in one chunk

Possibly, the offset method works fine though (and it's not my spreadsheet so I want to minimise my involvement).

A strange VBA issue. For some reason removing subtotals from a pivot table is far quicker when you click the button on the menu bar rather than using a macro to do it. This led me to discover that you can include menu items in VBA using

CommandBars.FindControl(ID:=14905).Execute

This one removes subtotals for instance and works fine. The ID came from https://www.microsoft.com/en-au/download/details.aspx?id=36798
However, when I try to run other commands they sometimes work and sometimes give this error

It's not vital to anything I'm doing, I'm just curious if anyone knows why this is. It doesn't seem to crop up on forums much.

1 Attachment

sometimes it's quicker to ask in here than go looking.
I've sliced my single table into many tables (i have 6 areas and 7 things, so er, 42?) in access sql.

Usually when I do what I do normally I link excel to one table in access.
So it's no bother to link the table.
Linking 42 is going to make me want to punch my cock.

How do I do this quickly?

And am I doing the right thing here?
Am I going to create something useless?

Create a view in access that links them all and link excel to your view?

How do I do this quickly?

Open an MS connection
Select the MS Access db
Save the query
Edit the query to do the work

^I do something like that to have single pivot table referencing multiple source spreadsheets so people can edit just their data and not have access to anybody elses and I can merge the results.

Connection String

DBQ=I:\File1.xlsb;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Command Text

SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM I:\file2.xlsb.[Sheet1$] UNION ALL SELECT * FROM I:\file3.xlsb.[Sheet1$] UNION ALL SELECT * FROM I:\file4.xlsb.[Sheet1$]

Ok. Will look at that tmrw.
Ta