Question description:
This user has given permission to use the problem statement for this
blog.
i have a column of numbers(d column), in the e column i want the cells to return nothing if the d column is not a 12 and if the d column is a 12 i want the e column to return the number of cells since the last 12.
Solved by O. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/10/2018 - 05:28
Hi
Excelchat Expert
05/10/2018 - 05:28
Welcome to Got it Pro
User
05/10/2018 - 05:28
hi
Excelchat Expert
05/10/2018 - 05:28
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
05/10/2018 - 05:28
Please tell a bit about the requirement so that i can help better
User
05/10/2018 - 05:29
so on my spreadsheet when i press f9 the number in my table all change, when i press f9 if column d is a 12 i would like column e to count how many cells since the last 12
User
05/10/2018 - 05:31
so in row 32 there is a 12 in column d, so column e displays an 18 because there are 18 cells between 32 and the last 12 in row 14
Excelchat Expert
05/10/2018 - 05:42
We can make the formula but we will need to make slight adjustments after first 12 is found and so on
User
05/10/2018 - 05:43
like what kind of adjustment, i have 10,000 rows on my shreadsheet
Excelchat Expert
05/10/2018 - 05:43
Then let me figure out a better way
Excelchat Expert
05/10/2018 - 06:07
Hey are you there?
User
05/10/2018 - 06:07
yes
Excelchat Expert
05/10/2018 - 06:07
Can you see column I?
User
05/10/2018 - 06:08
yes
Excelchat Expert
05/10/2018 - 06:08
I have conditional formatted the cells where we want to see the count
Excelchat Expert
05/10/2018 - 06:08
Do you see cells in green?
User
05/10/2018 - 06:08
yes
Excelchat Expert
05/10/2018 - 06:08
Then in column I have used this formula for I15
Excelchat Expert
05/10/2018 - 06:08
=IF(I13=I14,1,IF(H15<>0,I14+1,I14))
Excelchat Expert
05/10/2018 - 06:09
If previous 2 values before this are equal then count should start from 1
Excelchat Expert
05/10/2018 - 06:09
Do you see?
User
05/10/2018 - 06:09
yeas
Excelchat Expert
05/10/2018 - 06:09
Using this logic you can drag this down to as many rows and will get the right answer
Excelchat Expert
05/10/2018 - 06:10
Please see and let me know if this helps
User
05/10/2018 - 06:10
ok 1 sec
Excelchat Expert
05/10/2018 - 06:12
Did you see?
Excelchat Expert
05/10/2018 - 06:12
Does it help you out?
Excelchat Expert
05/10/2018 - 06:12
this is the best method i could think of using formulas
User
05/10/2018 - 06:13
im trying to get it to work on my sheet
Excelchat Expert
05/10/2018 - 06:13
otherwise it can be done by VBA but this platform does not support vba
Excelchat Expert
05/10/2018 - 06:13
ok
Excelchat Expert
05/10/2018 - 06:13
Conditional formatting is important here
Excelchat Expert
05/10/2018 - 06:13
ones which are green
Excelchat Expert
05/10/2018 - 06:13
if the column value next to it is 0 then green
User
05/10/2018 - 06:14
ok
User
05/10/2018 - 06:15
so column i needs to have conditional formatting?
Excelchat Expert
05/10/2018 - 06:15
Not necessarily
Excelchat Expert
05/10/2018 - 06:16
I just populated the required value in front in column E as per your initial ask based on values in column I
Excelchat Expert
05/10/2018 - 06:16
Just forllow this and you will be able to do this
Excelchat Expert
05/10/2018 - 06:16
Please do give good rating and reviews, it would really mean a lot
User
05/10/2018 - 06:17
i think this will work but when i put it in my sheet i am getting a 2 in row 14
User
05/10/2018 - 06:17
the column nect to it is a 0 like it should be
User
05/10/2018 - 06:18
=IF(D14<>12,1,0)
User
05/10/2018 - 06:18
reads 0
Excelchat Expert
05/10/2018 - 06:18
Just use the same formulas as i have used
Excelchat Expert
05/10/2018 - 06:18
Please see the formulas correctly
User
05/10/2018 - 06:26
something isn't working my h14 is reading 1
User
05/10/2018 - 06:26
=IF(D14<>12,1,0)
Excelchat Expert
05/10/2018 - 06:26
Can you give your data i can make it for you
Excelchat Expert
05/10/2018 - 06:26
if this is working here it should work in your file as well
Excelchat Expert
05/10/2018 - 06:26
I think you have pasted the formula one row up or down
Excelchat Expert
05/10/2018 - 06:26
that could be the reason
Excelchat Expert
05/10/2018 - 06:27
Please adjust it as i have done
Excelchat Expert
05/10/2018 - 06:27
Time is about to go
Excelchat Expert
05/10/2018 - 06:27
I hope you willl give good ratings
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
