LinkBack URL
About LinkBacks
Hi, If you can help me with a formula to count the number of consecutive zero starting from the last cell with non zero value.
Thank you in advance!
Hi, If you can help me with a formula to count the number of consecutive zero starting from the last cell with non zero value.
Thank you in advance!
Hi Intalzky
Assuming your list starts in A2, then in any cell on row 2 typeand copy down as far as necessary.
=IF(AND(A2=0,LEN(A2)=1),B1+1,0)
Let me know if this is OK for you
Regards
Alastair
Hi aydeegee, thank you for your Quick response. To give you a better understanding regarding my problem please refer below.
Capture.PNG
Column A would be the Names of Agents.
Column B:1 to K:1 would be number of days.
And below the days are their percentage. What I would like to do is to count the number of consecutive "0%" in Column M.
However, here is the catch. for example Agent 1 already have 4 consecutive "0%" from Day 1 to Day 6, but on Day 7 Agent 1 incurred 5%. the count will be reset and start again to count starting from the next "0%" Day. (Day 8 on the example). So Agent 1 have 3 days consecutive "0%".
And just to add, I would like also to exclude the blank cells in the count. Because blank cells means that agents are OFF from work during that day.
I really hope you can help me with this.
Thank you.
Hi Intalzky
I am unable to get access to Excel at present - serious hardware issues.
I will get back to you as soon as I can, but anyone else should feel free to assist.
Regards
Alastair
Hi Intalzky
Here a solution. It uses a macro - so if you have not used macros before, you may have to adjust your Excel security level.
Click on the blue button to run the macro. To see the macro press Alt+F8 / Step Into
Regards
Alastair
Hi Alastair, This perfectly works. However using macro is the last that I can think of because this is a running file. Number of days will be added as the days go by and so will for the number of agents. What I'm really preferring is an excel formula to use.
But if macro is the only way to do this. Is there as way to adjust the columns and rows if I'll be adding data? Can you please teach me as I'm not that good in terms of VB codes.
Thank you so much
Hi Intalzky
The macro already worked for as many rows as necessary, it now caters for as many columns as necessary.
It may be possible to do it with a formula, but I am not clever enough to work it out.
I have annotated the macro, so that you may better understand what is going on. Please note that I am not claiming that this is good programming, or anything - just that it works
Regards
Alastair
Hi Alastair. This is awesome!!! Thank you so muchLast Favor, is it possible to set a conditional format on the answers? I want to highlight in green the cell with Answer equal to 45 and above.
=IF(ISNA(LOOKUP(2,1/(B2:K2<>0))),COUNT(B2:K2),COUNTIF(INDEX(2:2,LOOKUP(2,1/(B2:K2<>0),COLUMN(A2:J2))+2):K2,0)) gives those results
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Martin - Nice one!
Intalzky
Martin's solution is what you originally asked for, but in case you still want the conditional formatting, here it is.
Regards
Alastair
Same result with frequency:
=IFERROR(INDEX(FREQUENCY(IF(B2:K2="","",COLUMN(B2:K2)-COLUMN(B2)+1),LOOKUP(2,1/(B2:K2<>0),COLUMN(B2:K2)-COLUMN(B2)+1)),2),COUNT(B2:K2))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Quang PT
Thank you everyone! You're all awesome!
Alastair's Macro and Martin's formula works perfectly..
Hi bebo,
Thanks for your response but I think something is missing in the formula. as it counts the all % including non zero cells
Last edited by Intalzky; 03-15-2014 at 01:20 AM.
Hi Alastair,
I came up with a new scenario using the template with macro. I really hope you can help me with this.
When the count already reaches 45, can the count be reset back to zero?
For example, Agent 1 already have 45 consecutive 0% can the count be reset starting from the next day Agent 1 have 0% again and be reset again for the next time Agent 1 reaches another 45 consecutive 0% again and so on.
And if possible, on the next sheet. I wanted to see the Dates where Agents reaches the 45 count.
Example:
1st 2nd 3rd
Agent 1 March 1 April 15 May 30 and so on.
Thank you so much in advance!
to count Zeros you could also use
copy paste the formula then hold control and shift together and hit enter to make it array formula
=SUM(--((COLUMN(B2:K2)>MAX(IF(B2:K2>0,COLUMN(B2:K2),0)))*(B2:K2<>"")))
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Hi Hemesh,
Your formula for counting consecutive zeros is working. however, i just came up with a new scenario that if the count reaches 45, the count will be reset back to zero by the next day.
And also if there's a formula to get the DATE where the agent got the 45th consecutive 0.00%
I really hope you can help me with this.
thank you in advance!
Hi Hemesh,
I have attached a sample file for your reference. Raw Data is in Sheet 1 and formula that I need to work is in Sheet 2.
Thank you so much in advance!
can you please upload a sample showing what you are trying to achieve. I think you will need a helper column.
Last edited by hemesh; 05-01-2014 at 02:54 PM.
Hello Intalzky,
This formula will work till data in HQ column if data range expands then change references in the formula about which I have written in attached book
try below in D2 in Sheet 2Formula:
=IFERROR(INDEX(Sheet1!$B$1:$HQ$1,MATCH(COLUMN(A1)*45,IF(FREQUENCY(MMULT(--(ROW(INDIRECT("2:225"))>=COLUMN($B2:$HQ2)),TRANSPOSE(((COLUMN(Sheet1!$B2:$HQ2)>MAX(IF(Sheet1!$B2:$HQ2,COLUMN(Sheet1!$B2:$HQ2),0)))*(Sheet1!$B2:$HQ2<>"")))),MMULT(--(ROW(INDIRECT("2:225"))>=COLUMN($B2:$HQ2)),TRANSPOSE(((COLUMN(Sheet1!$B2:$HQ2)>MAX(IF(Sheet1!$B2:$HQ2,COLUMN(Sheet1!$B2:$HQ2),0)))*(Sheet1!$B2:$HQ2<>"")))))>0,MMULT(--(ROW(INDIRECT("2:225"))>=COLUMN($B2:$HQ2)),TRANSPOSE(((COLUMN(Sheet1!$B2:$HQ2)>MAX(IF(Sheet1!$B2:$HQ2,COLUMN(Sheet1!$B2:$HQ2),0)))*(Sheet1!$B2:$HQ2<>"")))),0),0)),"")
above formula is array entered
drag down and drag to right
Hope this helps
Hi Hemesh,
Thank you for checking on this. The formula almost work but I think its still missing something.
For example on Agent 107, this agent incurred 45 consecutive 0.00% on 04/11/14. but the formula is showing blank.
for Agent 107 the consecutive 0 after non zero value is only 3 so formula is showing blank. If you make Cell BU107 to 0 instead of 3.33 then you will get your answerOriginally Posted by Intalzky
Last edited by hemesh; 05-02-2014 at 06:38 AM.
That is correct, that is why the formula on column B should be revised. Because still Agent 107 incurred a 45 consecutive 0.
If we can have the formula count the 45 consecutive 0, then start counting again on the next day with 0 value.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks