AVERAGE vs AVERAGEIF on Date Ranges

Question

I have a set of data that's row-by-row very similar to the temperature ranges for each month but my values are all positive real numbers (#s> 0 with potential decimal values out to four (4) digits).  Each month is a single row.  Here's the general structure:

Col:   B                 D                   E             AM              AO                         AP (see explanation below)

Date                   High             Low         Range        Range %                      

01/01/2008         53                 42            11            (53-42)*100/42

02/01/2008         56                 38            18            (56-38)*100/38

03/01/2008         65                 41            24            (65-41)*100/41

04/01/2008         74                 40            34            (74-40)*100/40

....

01/01/2019

02/01/2019

03/01/2019

04/01/2019

05/01/2019

When I attempt to determine my AVERAGE Range or AVERAGE Range % for each month of the year (1, 2, 3, 4.....) I always get the same value.

Here's my Excel function for the average based upon the month of the year

AP5 would have 2 for February; AP6 would have 3 for March etc.  I have Column AP containing the month numbers of the year (1 for January, 2 for February, etc. in Number format).  Hence, there are twelve (12) rows with the numbers 1 through 12, inclusive.  I do this at the top of my spreadsheet for easy viewing.

Obviously, there aren't nearly 5000 months from 01/01/2008 (mm/dd/ccyy format).  What I wanted the above AVERAGE function to do is to determine the average monthly Range % for all January months since 2008 (or whenever), same for February etc.  Hence, from 2008 through 2019 there would be twelve (12) Januarys but, currently, eleven (11) Junes since June 2019 hasn't ended.

FWIW, the value I get is the same and it's 30 for each month.  Obviously, temperatures (in this example)  would not give the same average each month.

What am I doing wrong?

TIA

Answer 1

I have populated following formula and it is working

=AVERAGE(IF(MONTH($B$3:$B$5000)=$AP3,IF($B$3:$B$5000<>"",$AO$3:$AO$5000)))

Download the working file from https://1drv.ms/x/s!Akd5y6ruJhvhnDcV69bI4iON1w_...

Answer 2

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link here.

HOW TO UPLOAD

1. Login to https://onedrive.live.com/ utilizing the same Login ID and Password which you have used on this forum. (or use the installed Onedrive on your computer)
2. Click Upload in the top OR drag and drop the file here.
3. After uploading, right click the file and choose share.
4. Optional but recommended - Uncheck the Allow Editing
5. Click Get a Link.
6. Copy the link and paste the link here.

Do let me know if you require any further help on this. Will be glad to help you.

Answer 3

Hi,

In cell AO4 apply this formula and drag it down. Format cells as %.

=IFERROR(SUMPRODUCT((MONTH($A$4:$A$5000)=AP4)*($B$4:$B$5000-$C$4:$C$5000))/($C$4:$C$5000),"")

Hope this helps.

IlirU

Answer 4

I get a #VALUE! error message after I do the Ctrl+Shift+Enter:

=AVERAGE(IF(MONTH($B$4:$B$5000)=$AP4,IF($B$4:$B$5000<>"",$AO$4:$AO$5000)))

I even tried the Month($B$4:$B$5000)<>""

Answer 5

Hi total1

Greetings! I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Use the below Array formula

=AVERAGE(IF(MONTH($B$4:$B$5000)=$AP4,IF($B$4:$B$5000<>"",$AO$4:$AO$5000)))

Do let me know if you have any more question or require further help.

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.