I have a schedule that has a flag for every day off, displaying a 1. If they worked, the cell returns "".
I need to count how many days each employee worked ("" cells) consecutively from Sunday and working backwards.
Basically, I need to make sure people are not working too many consecutive days over two schedules.
M T W T F S S 1 1 3 1 1 1 1 1 1 1 0 1 1 1
Try this in H2:
=COLUMNS(A2:G2)-MATCH(9.99E+307,A2:G2)
this should work
-deleted-
EDIT: same answer as above.
Last edited by dosydos; 12-20-2018 at 03:50 PM.
Awesome, this works great! Thank you.
(how/why does this work?)
Follow-up question;
How would I do the exact same thing, but this time counting from left to right?
MATCH(9.99E+307,A2:G2)
more specifically 9.99E+307 is the largest number in excel. Match uses this to find the last number in the array and returns its relative position. then it is just simple math of total column - relative position.
AND FOR LEFT TO RIGHT YOU CAN USE AN ARRAY FORMULA. Enter with ctrl + shift + enter
=INDEX(COLUMN(A2:G2),,MIN(IF(A2:G2<>"",COLUMN(A2:G2),9.99E+307)))-1
This returns #REF!
Last edited by AliGW; 12-21-2018 at 09:13 AM.
did you use ctrl shift and enter?
it should ad { } around the formula
Yes, still doesn't work
Last edited by AliGW; 12-21-2018 at 09:13 AM.
I attatched the sheet i made with the formula in yellow cells.
Yes, your one works great, but when I copy the formula, change the range/reference and ctrl+shift+enter i still get #REF!
Last edited by AliGW; 12-21-2018 at 09:14 AM.
=index(column(ak4:aq4),,min(if(ak4:aq4<>"",column(ak4:aq4),9.99e+307)))-1
This should work for left to right:
=MATCH(1,A2:G2,0)-1
Bl1nd - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks 63falcondude!
You're welcome. Happy to help.
Merry Christmas!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks