ExcelAtEverything
Active Member
- Joined
- Jan 30, 2021
- Messages
- 351
- Office Version
- 2019
- Platform
- Windows
Hello,
I was hoping to get some assistance as I'm having trouble with this. Using my table below as an example, how could I write a formula which will basically say:
* For each store, add all monthly dollar amounts for months that occur prior to the month of the "Reporting Date" in $B$2. (or in this case, Jan & Feb)
*For the month in the table which is equal to the "Reporting Date" in $B$2 (in this case, March), I would like to take the daily average of the dollar amount shown for that month (divided evenly by # days in that month (in this case 31)), and multiply by the number of days that have passed so far in the "Reporting Date" $B$2 (specifically up to but not including the "Reporting Date"). In other words using the displayed table, for the month of March (since that's a partial month due to the "Reporting Date"), Instead of entering $100, I would only enter $87.21 (since that is an estimated amount based on the "Reporting Date".
Thank you.
I was hoping to get some assistance as I'm having trouble with this. Using my table below as an example, how could I write a formula which will basically say:
* For each store, add all monthly dollar amounts for months that occur prior to the month of the "Reporting Date" in $B$2. (or in this case, Jan & Feb)
*For the month in the table which is equal to the "Reporting Date" in $B$2 (in this case, March), I would like to take the daily average of the dollar amount shown for that month (divided evenly by # days in that month (in this case 31)), and multiply by the number of days that have passed so far in the "Reporting Date" $B$2 (specifically up to but not including the "Reporting Date"). In other words using the displayed table, for the month of March (since that's a partial month due to the "Reporting Date"), Instead of entering $100, I would only enter $87.21 (since that is an estimated amount based on the "Reporting Date".
Thank you.