Daily Average formula counting too many days...

daniel68616

Hello, 

I recently had pro desk help set up the following formula and it is counting more days then it should. I would love your help in solving this issue. Thanks

Intended formula function: My smartsheet has date entries with totals. In my other sheet that is gathering the grand totals from this sheet, I wanted to come up with a daily average total where it would grab the grand total number and divide it by the number of days entered in the specific month. Below is the formula. Two screenshots are added as well. The date column and the column with the grand total value and the daily average result. 

As you can see the daily average for october is dividing by 29 days as opposed to the 21 days. If it is in fact including the november entries how do i modify this formula to make sure it only grabs october's entries?

=[October 2018]@row / NETDAYS(MIN({Arrowcreek LS Sheet Range 4}), MAX({Arrowcreek LS Sheet Range 4}))

 

Brian W

Okay, there's probably be an easier way to do this, but here is a way to specify that you only want the days from October:

=[October 2018]@row / NETDAYS(MIN(COLLECT({Arrowcreek LS Sheet Range 4}, {Arrowcreek LS Sheet Range 4}, >=DATE(2018, 10, 1), {Arrowcreek LS Sheet Range 4}, <=DATE(2018, 10, 31))), (MAX(COLLECT({Arrowcreek LS Sheet Range 4}, {Arrowcreek LS Sheet Range 4}, >=DATE(2018, 10, 1), {Arrowcreek LS Sheet Range 4}, <=DATE(2018, 10, 31)))))

I think this will come up with the correct number. You would need to change the start and end dates manually for other months.

 

daniel68616

Great! I just tried it and it's calculating correctly. Thanks so much:)