Forum Discussion
Divide Sales Objective by number of working days
Hi everyone!
I would like to request help with the following problem.
I need to divide the sales objective by the number of business days in the month, the problem is that I can't sell half a car. The idea is to have only integers and the remainder must eventually be considered.
Please see the example (made poorly by hand):
| August 2022 | |||||||||||||||||||||||
| Sales Objective | 01 | 02 | 03 | 04 | 05 | 08 | 09 | 10 | 11 | 12 | 15 | 16 | 17 | 18 | 19 | 22 | 23 | 24 | 25 | 26 | 29 | 30 | 31 |
| 40 | 1 | 2 | 1 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 2 |
| 55 | 2 | 2 | 2 | 3 | 2 | 2 | 2 | 3 | 2 | 2 | 3 | 3 | 2 | 2 | 3 | 3 | 2 | 2 | 3 | 3 | 2 | 2 | 3 |
| 20 | 0 | 2 | 0 | 2 | 0 | 2 | 0 | 2 | 0 | 0 | 2 | 0 | 2 | 0 | 2 | 0 | 2 | 0 | 2 | 0 | 1 | 0 | 1 |
Please note that the monthly target and number of business days change every month.
Does anyone have any Excel formula I can use?
Thank you in advance!
See the attached demo workbook.
5 Replies
Himroldan5,
HansVogelaarand I also must have been working on the problem in parallel. I did it a little differently. See my example! I did not want to throw it in the trash.
I have exchanged the example again.
- I really like this one too, it considers even holidays! Thank you very much.
That's a more 'modern' solution, I like it.
But there's something strange with the calculation of Easter Monday. I see this in your workbook:
=7*ROUND((4&-A1)/7+MOD(19*MOD(A1,19)-7,30)*0.14,)-6+1
This causes an error when Excel recalculates. It should be
=7*ROUND((DATE(A1,4,1))/7+MOD(19*MOD(A1,19)-7,30)*0.14,)-6+1
See the attached demo workbook.
- Thank you very much for your help, it's exactly what I needed!
