Forum Discussion
Help needed with IF and COUNTIFS Formulas
Is anyone able to advise the following formula:
=COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & V3,$D5:$D15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & V3,$H5:$H15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & V3,$L5:$L15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & V3,$P5:$P15, ">" & V3)
- Is there a way to simplify this?
- Is there a way to make this more accurate?
Cells in column G & H, I & J, O & P are using the following format:
=IF(C6="","",C6+E6)
Cells in U4:CC4 are using the following format:
=COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & U3,$D5:$D15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & U3,$H5:$H15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & U3,$L5:$L15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & U3,$P5:$P15, ">" & U3)
Cells in U5:CC15 are using the following format:
=IF(U$4>=$T5,1,"")
My issue is is when I put in the three break times, the mid break comes out at a shorter time.
My other issue is is that when I put in the times in row 5,6and 11, the data is coming up as a combined data in rows 5, 6 and seven on the page two.
Just for reference, "page two" is the same spreadsheet.
What I need to happen is that I enter in the shift start time and finish time. This then populates through to Break 1, 2 and 3.
The Time entry is the time the break starts. ie: 1 hour after start of shift, 1 hour after coming back from break, etc.
The break entry is the duration of the break taken. ie: 30 minutes.
Once all the info is put in, the relevant "Time Block" on "Page 2" shows a 1.
What is happening at the moment is that when I enter all the time data, the time blocks are not populating correctly in accordance to the entry.
Basically, If I have numerous people on shiftI need the time blocks to show where I have shortfalls in shift cover and not having too many people on break at the same time.
IE:
Link to Live Copy:
https://www.dropbox.com/scl/fi/eur1j526htu1j8a4d4290/Staff-Breaks.xlsx?rlkey=r4tm9xts4tonofpa2th2cusfw&st=nueyk0d7&dl=0
Any ideas would be greatly appreciated.
4 Replies
I believe the problem you are having is due to round off errors
as you can see the 8:30 AM in cell H5 is rounded up to ....7 while the 8:30 AM in cell AA3 is truncated at ....6
this error could go either way. Forcing a ROUND(AA3,9) and of the values in columns C:P may be needed but rounding of columns C:P will cause propogation of roundoff error as they 'build' on each other. and you can't use ROUND(H5:H15) in the COUNTIFS formula because that must be a range.
You could convert that whole formula to use something like:
=LET(t,ROUND(U3,9),s,($B$5:$B$15=$R$4)* ( (ROUND(--$C5:$C15,9)<=t)*(ROUND(--$D5:$D15,9)>t) -(ROUND(--$G5:$G15,9)<=t)*(ROUND(--$H5:$H15,9)>t) -(ROUND(--$K5:$K15,9)<=t)*(ROUND(--$L5:$L15,9)>t) -(ROUND(--$O5:$O15,9)<=t)*(ROUND(--$P5:$P15,9)>t) ), SUM(IF(ISERR(s),0,s) ) )in this case I round all the values to 9 digits and then do the comparisons.
I will edit this if I think of something better.
I’ve tried to link with Dropbox, OneDrive and iCloud but it won’t let me
Please attach a sample file to your post or share via OneDrive, Dropbox, Google Drive or similar service.
Link added at the end of my feed.
