I have been racking my brain for a simple way to accomplish something I do in Google Sheets for a while now and just have not come up with a solid idea that doesn’t involve reinventing the wheel.
I have a report I run daily comparing MTD sales in the current year to the prior year and estimate where sales might be based on the average daily sales. To do this I take the total MTD sales divided by the number of business days so far and then multiply the simple average times the total number of days in the month. (I know this is not a sophisticated projection but it tends to get the job done)
To accomplish this in Google Sheets I have a very automated sub-table on my report:
The only values I change are Start and End at the beginning of each month. The rest are based on formulas.
I can accomplish most of the formulas using Coda:
Today uses =today()
Report Day is =today()-Days(1)
Days Remaining =Total_Business_Days - Days_Down
What I am struggling with is how to accomplish the calculation for Total Business Days and Days Down because I use a specific formula in Google Sheets that Coda does not seem to have:
Because different store locations can be open different days of the week (some closed Sunday, some open all week, others only Monday-Friday) this equation helps tremendously.
Does anyone have any alternatives to this that might work on Coda?