Hi all,
I try to create a SUMPRODUCT function that averages the seasonality for different accounts over multiple years.
Example of my source data (Input_Sheet):
Example of how the output should look like (Output_Sheet):
For now, I am only trying to make the sum that is already not working, and afterward, I would have tried to make the average out of it.
This is the formula for the first value in the output table in the upper left corner:
Nevertheless, I am always getting #VALUE! errors, which is most likely because some rows/cells have in the source data only empty values.
If you also know how to create averages per month and account, I would appreciate it very much!
Thank you already in advance.
Best regards,
Jake
I try to create a SUMPRODUCT function that averages the seasonality for different accounts over multiple years.
Example of my source data (Input_Sheet):
Account_Number | 201701 | 201702 | ... (over multiple years - in the format of "YYYYMM") |
1001 | 120.3% | 115.2% | ... (in percent) - some cells/rows are empty |
1002 | 98.2% | 104.3% | ... (in percent) - some cells/rows are empty |
... hundreds of accounts | ... (in percent) - some cells/rows are empty | ... (in percent) - some cells/rows are empty | ... (in percent) - some cells/rows are empty |
Example of how the output should look like (Output_Sheet):
Account_Number | 01 | 02 | ... (the 12 months in the format of "MM" - 01 for January, 02 for February, ...) |
1001 | Average Percentage for "1001" in January (over a period of multiple years) | Average Percentage for "1001" in February (over a period of multiple years) | ... (in percent) |
1002 | Average Percentage for "1002" in January (over a period of multiple years) | Average Percentage for "1002" in February (over a period of multiple years) | ... (in percent) |
... hundreds of accounts (exactly the same as above) | ... (in percent) | ... (in percent) | ... (in percent) |
For now, I am only trying to make the sum that is already not working, and afterward, I would have tried to make the average out of it.
This is the formula for the first value in the output table in the upper left corner:
Excel Formula:
=SUMPRODUCT(Input_Sheet!$I$3:$AF$780 * (NUMBERVALUE(RIGHT(Input_Sheet!$I$2:$AF$2;2))=!$I$2) * (Input_Sheet!$H$3:$AF$780=$H$3))
Nevertheless, I am always getting #VALUE! errors, which is most likely because some rows/cells have in the source data only empty values.
If you also know how to create averages per month and account, I would appreciate it very much!
Thank you already in advance.
Best regards,
Jake