0

I have a working system for giving employees an opportunity to track their goal progress on a week to week/monthly basis. I've now protected all cells except the 'Monthly Admission Goal' and grid spaces that correspond to specific monthly calendar work days (currently filled with zeros).

One issue though, if a zero is deleted from the grid and not replaced with another numeric value...I need it to default back to zero. The zero entry is vital to the COUNT function and if an employee removes one, leaving it blank, and forgets to replace with their desired number or back with a zero it throws the whole thing off. This has to be a fairly common requirement and should be an easy enough but can't seem to find the right Google search terms to correct this. See below link for example image.

enter image description here

Improve this question
6
  • Use one extra step of value use; enter value (or blank) in e.g. A1, in A2 use =IFERROR(VALUE(A1);0) (e.g, setting 0 as default, change for any other value), then use the value in A2 in your calculations.
    – Hannu
    Commented Jun 29, 2022 at 17:23
  • Hello, thanks for assistance. Did you look at the image? I think the solution you are proposing assumes there are calculations held within the cells I need to revert back to a zero. There are not. They have a zero pre-filled into their grid for the corresponding days of that month but no formulas in place. H7 to H11 each has a formula in place to collect all cells within their respective row that have an entry (why it's important for a zero to be in place, if no other number) and then calculate from there.
    – Jackson
    Commented Jun 29, 2022 at 17:33
  • If I understand right, the problem is demonstrated in H7. Can you show us the formula you use?
    – harrymc
    Commented Jun 29, 2022 at 17:40
  • Of course! H7=SUM(H3*COUNT(B7:F7)) H8=SUM(H3*COUNT(B8:F8)) And so on down to H11 That is all calculating correctly...provided either a zero or other numeric is in the unprotected cells that correspond with the working days for that month (all designated by zero currently in the image). The issue is if an employee removed a zero from a day and forgets to replace it with the appropriate number they are wanting to change to...and just leave it blank....it borks the calculations because of the reliance on the COUNT function.
    – Jackson
    Commented Jun 29, 2022 at 17:46
  • BTW @harrymc there is no issue with the way H7 is currently calculating. There is only working one day within that week so the appropriate calculation is made for that weekly goals. Week 2, 3, 4 and 5 all have five working days...and are calculating their weekly goals correctly as well. The COUNT function is the key here.
    – Jackson
    Commented Jun 29, 2022 at 17:50

1 Answer 1

Reset to default
0

Forcing the solution :

=SUM(H3*(COUNT(B7:F7)+COUNTBLANK(B7:F7)))

Reference : COUNTBLANK function.

Improve this answer
1
  • Unfortunately, this solution does not maintain the original purpose, as the zeros currently in the rows are what allows my H7:H11 formulas to understand how many workdays are in that given week. They are prefilled by me. Using COUNT and COUNTBLANK will effectively recognize every cell within the selection. Which is not the goal. This is why I take the H3 value (that is determined by working days in the month and amount of referrals needed) and multiply it by the working days in that week to get the weekly referral goal figures.
    – Jackson
    Commented Jun 29, 2022 at 20:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .