Forum Discussion
Excel Formula
I Use M3 Formula calculate g3 date to one month ago , That data in 2025 & 2026 sheet , but this Formula calculate wrong answer.
2 Replies
Share a sample file and explain little more about your issues.
The problem is that you are doing an average of averages which is NOT correct.
Basically if you have the number 1,2,3,4,5,6,7,8,9 the average is 5 but if you do the average of 1,2,3 it is 2 and the average of 4,5,6,7,8,9 is 6.5 and then the average of 2 and 6.5 is 4.25 NOT 5
In your M3 formula you want to do a SUM and SUMIFS instead of the AVERAGE and AVERAGEIFS and then divide by the number of days (ed-sd+1).
That said you have another item I should point out:
You are using the EDATE formula to determine 1 month later but EDATE(...,1) of a day > than the next month total days is the last day of that month so EDATE('2025-01-31',1) is 2025-02-28. so that "month" range would only be from 2025-01-31 to 2025-02-27.
I'm also not a fan of the MAP and INDIRECT but not sure of a 'simple' alternative.
