Averaging without ZEROs and incomplete months

[PCRIDE]

Hi, I have a complex dashboard that uses box plots, and other statistical formulas to come up with a forecast for how many Call Volumes are coming into the suppport center.

The problem I am having is the fields below point to a 9 month calendar and when we are in the current month, the volume is not complete so averaging previous months with a half month, will throw the forecast off. So I need to put a formula in front of some of these statistical measurements that says something like

If TODAY() is within the Current month, then only average the prior months.
or
If(MONTH(TODAY)=(TODAY(MONTH) "If Today falls within the month number is equal to Today in that month number, average Month numbers prior to this month... something like that. Its a bit easier to talk it out!

IRQ Average
Mean
STDEV
MAX
Q3
Median
Q1
Min
Bottom
2Q Box
3Q Box
Whis -
Whis +
IRQ, K-th, +2
IRQ, K-th, -2



Here is an example of what I have.

I have the future months built out, I need to keep the ZEROs there for other charts etc. to work properly. As you can see the months leading up to August are full complete months, but in August, let's say we are half way through the month, and I only want to average/apply statistics to April - July so I get a more accurate forecast on a monthly volume numbers.


April 15256
May 15896
June 18545
July 17586
August 8956
September 0
October 0
November 0

 

[JustynaMK]

Hi PCRIDE,

Add your months to column A and format them as 1st of each month, i.e.
01/04/2018
01/05/2018
...
01/11/2018

In column B you can add your values.

Taking Mean function as an example, you can try to use the following function:
=AVERAGE(B1:INDIRECT("B"&MATCH(TODAY(),A:A,1)-1))

The second part of the formula (INDIRECT) determines where your average range should finish. It is using MATCH to find a date that is less than the result of TODAY() function (in our case the result will be 5th row = August) and subtracts 1 from it (because we want to take the last full month into account). Let me know if such approach works for you.

 

[PCRIDE]

Hi PCRIDE,

Add your months to column A and format them as 1st of each month, i.e.
01/04/2018
01/05/2018
...
01/11/2018

In column B you can add your values.

Taking Mean function as an example, you can try to use the following function:
=AVERAGE(B1:INDIRECT("B"&MATCH(TODAY(),A:A,1)-1))

The second part of the formula (INDIRECT) determines where your average range should finish. It is using MATCH to find a date that is less than the result of TODAY() function (in our case the result will be 5th row = August) and subtracts 1 from it (because we want to take the last full month into account). Let me know if such approach works for you.

Thanks, I will try this! How do you format the month Name to a date as the first of each month? I didn't see a format I could use.

 

[JustynaMK]

Hi,

If you have your month names in column A, you can quickly convert them to dates by using the following formula:
=--("01-"&A1&"-2018")

e.g. if you have "April" in cell A1, this formula will return 43191. Now if you change a cell format to date you should see 01/04/2018 (dd/mm/yyyy).

 

[PCRIDE]

Hi,

If you have your month names in column A, you can quickly convert them to dates by using the following formula:
=--("01-"&A1&"-2018")

e.g. if you have "April" in cell A1, this formula will return 43191. Now if you change a cell format to date you should see 01/04/2018 (dd/mm/yyyy).

Thanks. I can't get it to work. What is "B" in your formula?

 

[PCRIDE]

I couldn't make it work, but found a simpler approach. I built a helper column, then used

=IF(CP33=0,"",(IF(CL33=MONTH(TODAY()),"","X")))

Then I was able to do an AverageIFs statement and use X as the Criteria.

 

[JustynaMK]

Hi, sorry - I just read your post. Column "B" in my formula are your values, meaning: 15256, 15896, etc.

Still glad that your formula solved the issue!

Take care,
J