Average last 3 non-blank cells in columns...

[marantsmi]

I have spent hours researching and cannot find my answer. I am a bit new to using functions like index, row, column, large, etc., so I'm having a hard time understanding the formulas I am finding. Please help.

Here's what I have.

Row 4 gives the total "Actual Usage" per month starting at D4 and continuing to infinity, but there are 4-5 blank cells in between every total (each month consists of 4-5 columns to show the weekly break down in row 6).

Row 5 gives the total "Actual Sold" per month with the same situation as row 4, having 4-5 blank cells in between totals.

I want row 7 to reveal the running combined average of the last 3 non-blank cells (monthly totals) for both row 4 and row 5. In other words, the 4 cells in row 6 for the month of December ought to give the combined average of the actual usage and actual sold for September, October and November. Once I enter December's totals at the end of December, I would want January's row 6 to auto-calculate the combined average for Oct-Dec, and so on.

I tried to give a clean example; see attached.

EXAMPLE.xlsx

EXAMPLE.xlsx

EXAMPLE.xlsx

[Sisyphus]

Hi,

You have a number of problems to solve. Let's do them one at a time.

1. You are working with two units, month and week. This is causing problems. Note that weekly totals can't be coerced into monthly totals. You would need daily totals in order to have monthly sums. So, I think you may find it easy to forget about months and work with weeks.

2. If you take a formula like =SUM(A4:F4) that you might have in G4 and copy it to H4 the summed range will change automatically to SUM(B4:G4). It is harder to refer to another sheet that might contain last year's data, but it can be done using Excel's own and essential referencing system. You may like to read up on absolute and relative referencing in Excel help. In short, if you don't use the $ sign the reference will be relative to the current cell, and 10 cells to the left of your current cell will always be 10 weeks from the current week in your worksheet.

3. You calculate the average of 10 weeks' sales by dividing the total sales by the number of weeks. This would be true even if you don't have any sale in some weeks. Your average per week doesn't increase because you did all your sales within a single week. But it seems that you have blank columns which don't represent weeks. Use the COUNTA function to count the number of cells with entries in them. Something like this:

Code: Select all

= SUM(A4:K4)/COUNTA(A4:K4)


The problem is that you can't differentiate between cells that are blank because there was no sale and those that are blank due to the design of your worksheet. Well, you can't change the sales result, but I suspect that your worksheet design may benefit from focusing on weekly numbers. If this can't be done we may have a look at any remaining problems together. If this is what you would like, please post els versions of your worksheets. I am still faithful to Excel 2003.

Have a great day!

[marantsmi]

Hi Sisyphus! Thank you so much for trying to help.

Maybe it would help if I gave you more information. Please see the new example file attached, which is a portion of my actual spreadsheet.

Bottomline: the formulas I'm using now are not ideal, especially as the spreadsheet extends into future months, etc. What I want (if possible) is for the forecast row to calculate the average of the previous three months of usage and sold data, plus a percentage increase (which I can add later, after I figure out the correct formula for the average). You can see my current formulas which divide the monthly total by the number of weeks in the given month, and an increase of 10% over the average.

Is there a formula that will run the length of the spreadsheet (A:ZZ) and calculate the average of the last three months previous to the current month?

You're probably correct: I may need to revamp the whole spreadsheet layout, but if possible, I'd like to keep it the way it is, if there is a possible formula. I only enter monthly totals, and then I break it down by a weekly forecast; I need this to stay intact.

[Sisyphus]

Hi,

Sorry for my typo: I meant to ask you for xls format but my anxious iPad co erred the extension to els. I can't open your example files because they are in xlsx format. So, I only see the picture.

But even so, I don't understand why the thought I have offered can't be worked into a solution. Why do you want to look at columns A to ZZ in order to extract an average for the past 3 months? Three months' data should be in 14 or 15 columns to the left of where you want the result. Why do you need to look toward the right? Is there a point I'm not getting?

Looking to the left you have 14 or 15 columns with weekly results plus 3 columns with monthly totals. This poses two problems, how to determine whether there are 14 or 15 columns, and how to exclude the monthly columns from being included. For the second problem I again suggest the COUNTA function. Can you guarantee that every weekly column will have a total and every monthly column will not? The blank cell needs not be in the same row as the total you want to sum up. In that case (All columns) - (COUNTA Columns) = (Weekly columns). But this calculation wouldn't be necessary if there are always 3 monthly total columns within any range of 17 or 18 cells to the left of where you want the result. So, the bigger problem is the other one, 14 or 15 weeks.

I suppose that a solution can be worked out that examines the cells in question and extends the range precisely. However, I fail again to see your objection to my train of thought. So, please excuse me for rephrasing it now that I have a little more information. Since you calculate your weekly totals from monthly sales, what prevents you from calculating monthly forecasts based on weekly sales? The science you apply (forecasting) is imprecise. There is a lot more imprecision in the 'estimated' (= wishful thinking) increase of 10% than in any conversion from month to week and back you might make with real numbers. In fact, 3 months can be 89 to 92 days. 13 weeks are always 91 days. So, why not work with 13 weeks' totals. And if you want to convert that to a period of fewer or more days you modify it based on a calendar calculation. So, I don't see why you need to face the question of how many weeks there were in the last three months, which, actually, burns down to how many week ended during the past 3 months which makes your result even more volatile.

A word of encouragement: The average you want can be worked out. As I said in my previous mail, it is a division of a sum by a number. Accordingly, I seek the task in defining the sum and the number, each one separately. The picture of your worksheet has no column or row IDs. Am I correctly assuming that you want to apply the formula in the Row marked as forecast? In that row you have both, monthly and weekly totals. Is your problem that you can't get a correct sum for a previous period regardless of whether you want months or weeks? And, similarly, that you can't get a reliable divisor either? Such things can be solved. So, don't lose hope!
Have a good day!

[marantsmi]

Thank you for your patience with me and trying to help me here--I truly appreciate it!! I have attached the file in .xls format--hopefully that helps.

Let me try to explain my purpose for months and weeks. My reports for usage and sales are both generated at the end of each month; a weekly report is not possible. My forecast row is based on a monthly forecast divided by the number of weeks in each month (which I hope a formula can determine so that I don't have to manually divide by 4 or 5 each month respectively). I need to show weeks so that I can show shipment deliveries (orders), and so that I can see exactly when we will fall below our set inventory minimums (wk1, wk2, wk3, and so on). When I place orders with our suppliers, I entered those totals into the "Scheduled Receipts" row. The bottom row (projected balance) is the total of the previous week's balance, minus the current week's forecast, plus any deliveries. All reports and forecasting is done on a monthly basis, but I need to know what the weekly impact looks like, with any deliveries, etc. The company I work for does not yet have a more sophisticated MRP system; instead, we have a glorified accounting program. So, I do a lot of my work in Excel, and this is a format I learned at a training session I attended. It has worked well for me, except that my formulas are cumbersome, and I would really like to improve them, and make my future additions to the spreadsheet that much easier.

The reason I want the span A:ZZ is so that I can use one-identical formula for the weekly cells in the forecast row (this would exclude the EOM columns). If this is wishful thinking and impossible, then I'm open to other suggestions. By spanning my formulas only over a 14-15 time span, I am afraid that I will run into problems as I cut and paste and my formula moves with each cell-paste, and by the 5th week of a month, I may no longer be spanning 3-months of totals. I haven't fully thought this through, so it's possible this is not an issue.

Don't worry about the 10% thing.......I will be adding a different percentage increase based on actual data later.....this was just a safety measure for the past.

Does this help explain my wish?

[Sisyphus]

Hi,

There is no problem with your wishes. They seem quite modest. I have done forecast sheets of this kind and think I understand your requirements quite well

Next step, I need to look at your existing formulas. But for that I need a PC which I won't have until tomorrow. Meanwhile, I stumble over your mention of cut and paste. What is it you need to cut? Is it that you paste columns on the right of the sheet for every week?

If your worksheet is dynamic the way you deal with the end of the year is also important. Basically, there are two ways. One, you create a new sheet for each year, more or less. The other way is to keep a single sheet covering 12 months. Every 3 months you make an archive copy before deleting the oldest 3 months in the sheet. In this way you avoid having your formulas dig in other worksheets at the start of the year. Will that work for you?

I'll get back to you tomorrow.
Have a pleasant day

[Sisyphus]

Good morning,

While lookiong at your spreadsheet this morning I found this formula:

Code: Select all

=IF(BZ6<=0,(BY9-CA7+CA8),(BZ6-CA7+CA8))


Formulas make up a considerable part of the volume of your sheet. Therefore you probably want to keep their size to a minimum. The following formula will do the same job as the one you have:

Code: Select all

=IF(BZ6<=0,BY9, BZ6)-CA7+CA8


Tell me, why do you refer to "6" values in the following formula taken from your Sheet and what is the significance of the alternative calculation?

Code: Select all

=IF(COUNTA(BO4:BZ5)=6,(AVERAGE(BO4:BZ5)/4*1.1),BY7)


I take it that this is the formula that brought you to the forum. Is that right?

Regards,

[marantsmi]

Good morning.

Thanks for the shorter formula for calculating the Projected Available Balance. Most of my formulas tend to be quite cumbersome, so I appreciate the help! And the only reason I have the IF statement regarding the Actual Balance value being <=0 is because I want the formulas to continue working even if I have not yet entered the EOM data. I guess a COUNTA formula would have worked there too.

Regarding the formula: =IF(COUNTA(BO4:BZ5)=6,(AVERAGE(BO4:BZ5)/4*1.1),BY7).
Yes, this was the formula I wanted to improve.

Here's my interpretation behind the formula:
COUNTA determins whether I've entered all of the data for the past 3 months in the Actual Usage and Actual Sold rows (EOM columns of those rows). If I have, the total should equal 6 (3 and 3), and therefore will proceed to give me the average of those 6 cells, divided by the number of weeks in the current month (4), with an increase of 10% (a percentage pulled out of thin air, mostly just for buffer's sake!), or else the formula will return the last determined forecast value from the month before (BY7). For some items, I calculate the average of both the Actual Usage and Actual Sold rows to determine the forecast. For other items, I calculate only the Actual Sold row. For this reason, some formulas have the COUNTA formula for 6 entires and some for only 3. (The usage report that I pull from our system does not give me usage data on all items, nor are all item usage totals accurate for which it does provide the usage data...something our software team will be working on in the future.)

Thanks again for sticking with me and offering advice and help! It is much appreciated. You must get a sick sense of enjoyment out of this!! Speaking of.....I have about half-a-dozen spreadsheets I must attend to.......off to work.

Thanks!

[Sisyphus]

Hello Marantsmi,

Perhaps its time for some results. First, here is your date row:

Code: Select all

=AZ3+7


AZ3 must contain a date and the cell containing the formula must be formatted as Date with a format of "mmm yyyy". Let me know if you need help with that.

The following is the formula for your monthly columns header (the one in the merged cells):

Code: Select all

=EDATE(AY2,1)


AY2 must contain a date and the cell containing the formula must be formatted as Date with a format of "mmmm yyyy". In fact, the date in AY2 may have the following kind formula, though the sample was lifted from column BJ:

Code: Select all

=IF(BF2="",BE2, BF2)


The purpose of the condition is to accommodate either 4 or 5 weekly columns being referred to. The point is that either BE or BF must be the first of the merged cells.

And here is your formula for row 7:

Code: Select all

=(SUM(AA$7:AY$7)-SUMIF(AA$3:AY$3,"EOM",AA$7:AY$7))/(COUNTA(AA$7:AY$7)-COUNTIF(AA$3:AY$3,"EOM"))


It is the same formula for all columns. I have chosen the range arbitrarily. You can change it as you wish. Observe, however, that reference is made to the "EOM" in row 3 of the monthly columns. If it is missing the returned result will be faulty, as I am sure you can see. Let me know if you need the formula explained.

The best way to set up additional columns will be to create weeks first, then insert the monthly columns between them. This way the weekly date headers will turn out correctly. Row 7 isn't so sensitive. You can delete cells anywhere in the sequence. The monthly dates in row 2 alternate. You can always copy any set of 4 to the next set of four, then merge it with the fifth if required before copying any single month's header to the next column. I didn't create a formula for the "EOM". It should travel on the same ticket as the formula above it. In fact, I imagine that you will copy an entire previous month's column to the next month's column which you inserted between the weekly columns that extend further into the future.

Of course, the above doesn't allow you to start a new sheet. The formula can be adapted if such becomes your need in the future. For the moment you will find great easy in extending the sheet to thr right.

I have also not bothered to include the actual usage in my formula. Adaptation in this regard will be very easy and you may not require my assistance for it which will, however, be at your disposal - of course.
Have a great day!
Sisyphus

[Sisyphus]

Hello marantsmi,

The task was to "improve" your formulas. Perhaps this target has been reached. Improvement focuses on the formulas' capability of being copied to the right of the sheet. There is no improvement in their logic but their scope and flexibility is now better than before. So, I suppose we can close the thread. But before doing so I urge you to split your monthly and weekly data into separate sheets.

More than 60% of both formula volume and maintenance effort presently goes into maintaining monthly columns between the weekly ones. That is grossly inefficient, and fails not only to provide benefit but also prevents you from processing either data, where copying is just the tip of the iceberg. One example is Excel's Moving Average function which isn't available to you because your data is intermingled. You may read up on it at this link:

http://office.microsoft.com/en-us/excel-help/forecast-inventory-levels-with-moving-average-analysis-HA001086480.aspx

Let me know whenever I can be of further help.
Have a great day!
Sisyphus