Forum Discussion
Solved
Formula question as i have no idea please.
I would like to have a set value in a row of days of a month to calculate absent workers. Example there is a requirement for 15 staff daily. Each day I record the number of staff into a cell under the date. On the 4th day of the month two people away so I add the number 13. On the 12th day of the month 4 staff are off.
Can l then have cell that automatically knows that the total staff missing for a day (2 form 4th and 4 from 12th) then counts them in a separate cell? Populating itself.
Thank you in advance. I'm very new to excel.
It should be calculable, if I understand your request correctly. For your "counts them in a separate cell", I'm interpreting "them" as the total of staffperson-days. (If you instead meant the count of days, then the formulas are simpler than those discussed below.)
So long as the required staff count is constant at 15, the number of staffperson-days you can expect is 15 multiplied by the count of days for which you have entered data. And the number of actual staffperson-days is just the sum of the values you entered. So subtracting the latter from the former gives you the cumulative shortage.
See cell AG2 in the attached workbook.
But might you record more staff present for a day than the 15? Say, one person gets sick midday and leaves, but you get a replacement in for the afternoon. Would you record 16 for the day? Or if there are extra staff working some days because of a special activity? If yes to either case, then you may want to calculate the expected and actual staffperson-day values only when less than 15. See cell AG3 for that calculation.
Overall, it's the use of the COUNT, SUM, COUNTIF, and/or SUMIF built-in Excel functions that should get the results you need.
It should be calculable, if I understand your request correctly. For your "counts them in a separate cell", I'm interpreting "them" as the total of staffperson-days. (If you instead meant the count of days, then the formulas are simpler than those discussed below.)
So long as the required staff count is constant at 15, the number of staffperson-days you can expect is 15 multiplied by the count of days for which you have entered data. And the number of actual staffperson-days is just the sum of the values you entered. So subtracting the latter from the former gives you the cumulative shortage.
See cell AG2 in the attached workbook.
But might you record more staff present for a day than the 15? Say, one person gets sick midday and leaves, but you get a replacement in for the afternoon. Would you record 16 for the day? Or if there are extra staff working some days because of a special activity? If yes to either case, then you may want to calculate the expected and actual staffperson-day values only when less than 15. See cell AG3 for that calculation.
Overall, it's the use of the COUNT, SUM, COUNTIF, and/or SUMIF built-in Excel functions that should get the results you need.
SnowMan55 I can't thank you enough. You are the MVP.. :1st_place_medal:
