Dear Members,
I am working on excel dashboard, but stuck on one most important expression and want your help to evaluate the number continuity formula in excel, I have totally confused why my formula is not working or full fill my requirement.
My requirement is want to count number of day’s continuity if values in column is less than 97 and reset the counter if value is greater than 97
example is mention in the below table.
| Continuity | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7 | Day 8 | Day 9 |
| 0 | 98 | 100 | 97 | 99 | 100 | 100 | 100 | 97 | 100 |
| 2 | 98 | 0 | 97 | 0 | 99 | 30 | 98 | 90 | 30 |
| 4 | 98 | 0 | 97 | 0 | 99 | 30 | 80 | 90 | 92 |
| 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
My Formula is =IF(COUNTIF(B2:J2,"<97")>=9,9,IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>97,1,"")))),0)) (But not working like above table)
Hi Sheikh Misbah
Please attach a sample file containing your original data (without your formulas) and then the expected result (highlight them)
Do NOT include wrong results as it is very confusing.
Include some explanation on how you calculate them. They will help us to understand better.
From your original post, it is difficult to understand what you really wanted.
Sunny
SunnyKow said
Hi Sheikh MisbahPlease attach a sample file containing your original data (without your formulas) and then the expected result (highlight them)
Do NOT include wrong results as it is very confusing.
Include some explanation on how you calculate them. They will help us to understand better.
From your original post, it is difficult to understand what you really wanted.
Sunny
Hi Sunny,
Thanks for your time excel sheet is attached.
Hi Sheikh
This is as close as I can get to what you wanted.
It uses an ARRAY formula so you must finish with CTRL+SHIFT+ENTER instead of ENTER when you enter the formula.
I am searching for the last position (column) of any value >97 and then subtract it from the number of columns used, 13 in your case (Column M)
You did not mention what to do if the value is exactly 97 (you only mentioned <97 and >97). I am "resetting" only if the value is >97.
I also noted that the values in the cells are displayed as whole numbers whereas their underlying values are not. This can cause confusion to anyone looking at them. I suggest you either round them up or display them with the correct decimal places.
Hope this helps.
SunnyKow said
Hi SheikhThis is as close as I can get to what you wanted.
It uses an ARRAY formula so you must finish with CTRL+SHIFT+ENTER instead of ENTER when you enter the formula.
I am searching for the last position (column) of any value >97 and then subtract it from the number of columns used, 13 in your case (Column M)
You did not mention what to do if the value is exactly 97 (you only mentioned <97 and >97). I am "resetting" only if the value is >97.
I also noted that the values in the cells are displayed as whole numbers whereas their underlying values are not. This can cause confusion to anyone looking at them. I suggest you either round them up or display them with the correct decimal places.
Hope this helps.
Hi SunnyKow,
Exactly! you got my point i want reset the value in formula cell when series of number values mentioned in columns at right is greater then equal to 97.
