Variable formula needed for Macro

[jomili]

I've attached an example of spreadsheet I'm working on, the purpose of which is to create a forecast for staffing. Part of the process is to pull in existing history to get the average staffing change by month. I'm automating all the steps, as we have multiple workers needed to repeat this forecasting process on a monthly basis.

I'm stuck on what formula to put in Column H (the Average), and where to place it. Depending on what the user picks for their history, there could be anywhere from 3 to 12 different columns. The way the formulas would work would be like this (using row 3 as example):

3 months - =((D3-C3)+(E3-D3))/2 Formula in column G
4 months - =((D3-C3)+(E3-D3)+(F3-E3))/3 Formula in column H
5 months - =((D3-C3)+(E3-D3)+(F3-E3)+(G3-F3))/4 Formula in column I
6 months - =((D3-C3)+(E3-D3)+(F3-E3)+(G3-F3)+(H3-G3))/5 Formula in column J
Etc...

As well, the target destination for the formula would be moving depending on months fixed. My questions are these:

1) Is there a variable formula that I can use in place of apending the formula indicated above to get the average monthly change?
2) How can I make certain my Average column is always going to be the first column to the right of the Total column, no matter how many months are selected?

Thanks in advance for any help you can provide.

[Richard Buttrey]

Hi,

I'm assuming that the data starting in A1 is pasted in every month and that you will always want the average calculated for however many months are in the data.

In which case it's probably neater to make the first column the average and use the formula I have shown in the attached.

HTH

[jomili]

Richard,

I like the formulaS, but I'm not sure I understand them. I see in cell B1 you've named the range as Months, and I understand what CountA does, but I don't understand the (5:5)-5, but can see the result is 3. But after that, I can't figure out how your formula in D6 works, other than seeing that it uses the result of B1 as a devisor. I can see the result of

Please Login or Register  to view this content.

is 2, but I don't see WHY it's 2. Would you care to explain further?

Thanks,
John

[Richard Buttrey]

Hi John,

OK.
The COUNTA(5:5) counts the number of non blank cells on row 5. Currently there are 8, 4 of which are the month dates and the other 4 being the 4 column labels. Hence we deduct the number of labels and then 1 since you are using 3 movements straddling 4 month ends. i.e. -5

Your original formulae contained self cancelling cell references, i.e. D3 & -D3, E3 & -E3, the only significant values in your original formula are the first and last, i.e. -C3 & F3

Since B1 evaluates to 3, an offset from cell D6 of 0 for row and 3 for the column refers to cell G6 which equals 2. Hence the A6 formula is effectively -D6+G6 which equates to zero.

As your data expands, B3 will increment and hence the Offset from D6 will always result in the last month column, D6 remains static of course.


HTH