I know you like to format the cells with formulas in green, but you do this manually. You can use conditional formatting to do this for you. 1. Hit the home cell which selects the entire sheet 2. Go to conditional formatting. Choose New Rule--then Use a formula to determine... 3. In the Format Values box enter =ISFORMULA(A1), the choose the color you want. 4. Click OK three times (I think), and any cell with a formula will will have that color. I found this information at www.contextures.com/excelisformulafunction.html
I read the issue a different way but the solution is the same. I think she wanted the count to continue, not reset, after next 0 value. Either way, the real magic is the sum function.
Hi Mike, your videos are just awesome, I like the way you explain.Note: I have a problem with an excel file I want you to help me with.I want a formula to extract duplicated values, I used Index and Match but they only extract the First one. I have a sample file but I don't know how to attach it to you? is there any way that I attach it to you?
+Ibrahim Omer , I have many videos on extracting multiple values from one lookup value. Check out this playlist: ruclips.net/p/PL63A7644FE57C97F4 Great over all video: ruclips.net/video/NqO3iekP1VY/видео.html Three part lesson of basics: ruclips.net/video/Tp7I5u1MqiM/видео.html ruclips.net/video/R5ZWAiNJLNo/видео.html ruclips.net/video/132ZdpxBm1U/видео.html
Hi, I'm hoping you can help! I need to do something similar but the reference cells are text. So I need the sequence/count to start over at 1 when a new text value is in column A. I am trying to use your lookup formula to change the absolute value inside of the if formula and not having much luck. thank you!
+Elvin Wei I do not know how to download videos. But try Googling, maybe there is a way. However, there are 2700 videos so I am not sure you want to download all of them...
I do have a question about the prior formula you used before the one that Bill recommended: COUNTIFS(INDEX($I$4:I4,MATCH(2,INDEX(1/($I$4:I4=0),))):I4,">0"), What does the 2 do in the match function and what does the second index function do? Is this from a prior video? If so, can you provide the link to it so I can understand it?
+Horace Greenley , It is from my Book & DVD: Ctrl + Shift + Enter Mastering Excel Array Formulas: www.amazon.com/Ctrl-Shift-Enter-Efficient-Calculating/dp/1615470077 This book and DVD go into great detail about the "Big Number" concept for looking up last item when doing approximate match lookup. Here is the video from the DVD that covers this topic: Ctrl + Shift + Enter: Excel Array Formulas 13: Dynamic Ranges: INDEX & OFFSET Functions ruclips.net/video/5ko5ES8ZLuY/видео.html
Thank you, I thought this might be the case. I did buy book and I do remember going over that but, as you know, unless you lose you lose it. I'm review the video. What does the second index function do in the Match formuia - I think I understand part of it but not really sure how it works
+Horace Greenley It should all become clear in this video: Ctrl + Shift + Enter: Excel Array Formulas 13: Dynamic Ranges: INDEX & OFFSET Functions ruclips.net/video/5ko5ES8ZLuY/видео.html
i have a question. im making document number base on Date. Like today is Feb. 27th. and i have 5 Documents and each document has unique numbers. something like this 0227-01,0227-02,0227-03,0227-04,0227-05. how to make it happen? please help. thank you for the video.
Guys, i'm new here but i'm not sure if i can ask your help here. I've been trying to do similar formula, a lilte different with sumifs on a table with range names. For example, i need a running total on a table with clients number and code number. It will increase the sum as long as the condition don't change. After some HOURS trying to figure it out, i came up with the solution, of course with sumifs, (i got a litle help i must say) BUT not with their range names but with it's relative positions, like $I3:$I12455. My question is: how could i do it with range names on my formula?
+Pedro Cabral da Camara , If you want mixed cell references with a Defined Name, it is possible! Just create name and then edit it to whatever type of cell reference that you would like. Use Name Manager, then edit.
I know you like to format the cells with formulas in green, but you do this manually. You can use conditional formatting to do this for you.
1. Hit the home cell which selects the entire sheet
2. Go to conditional formatting. Choose New Rule--then Use a formula to determine...
3. In the Format Values box enter =ISFORMULA(A1), the choose the color you want.
4. Click OK three times (I think), and any cell with a formula will will have that color.
I found this information at www.contextures.com/excelisformulafunction.html
+Michael Connors Thanks for the tip! Mr Excel and I did a video on this topic too: ruclips.net/video/7McnWwCleDk/видео.html
I like that solution very simple and effective !
+ExcelStrategy Yes, indeed!!!
Here is a version that does a running count of random repeating text or any kind of input:
=IF(A2=A1,SUM(B1+1),1)
hi,actually i have tried without sum function and it worked on excel for mac 2016.=IF(C13,D12+1;0)
Also you can use:
=IF(C13,N(D2)+1;0)
or
=IF(C13,sum(D2;1);0)
Blessings!
+John Jairo Vergara Domínguez , Awesome!!!! Love the N!!!
I read the issue a different way but the solution is the same. I think she wanted the count to continue, not reset, after next 0 value. Either way, the real magic is the sum function.
+Ian Carandang Thanks for your thoughts! Although the text may be interpreted in different ways, she said that the solution did the job.
Hi Mike, your videos are just awesome, I like the way you explain.Note: I have a problem with an excel file I want you to help me with.I want a formula to extract duplicated values, I used Index and Match but they only extract the First one. I have a sample file but I don't know how to attach it to you? is there any way that I attach it to you?
+Ibrahim Omer , I have many videos on extracting multiple values from one lookup value. Check out this playlist:
ruclips.net/p/PL63A7644FE57C97F4
Great over all video:
ruclips.net/video/NqO3iekP1VY/видео.html
Three part lesson of basics:
ruclips.net/video/Tp7I5u1MqiM/видео.html
ruclips.net/video/R5ZWAiNJLNo/видео.html
ruclips.net/video/132ZdpxBm1U/видео.html
hi mike,
i need to do count the cell in horizontal situation
criteria is to count the cell between value >=50 and =0
Hi, I'm hoping you can help! I need to do something similar but the reference cells are text. So I need the sequence/count to start over at 1 when a new text value is in column A. I am trying to use your lookup formula to change the absolute value inside of the if formula and not having much luck. thank you!
Sorry... Is
=IF(C13,N(D12)+1;0)
or
=IF(C13,sum(D12;1);0)
+John Jairo Vergara Domínguez Awesome!!!! Love the N!!! (No problem with typ-o: we got it!) :)
Hi, is there a way to download all of these useful videos? I want to view the videos at the same time trying to following your examples.
+Elvin Wei I do not know how to download videos. But try Googling, maybe there is a way. However, there are 2700 videos so I am not sure you want to download all of them...
+ExcelIsFun ah, ok. Thanks anyways. Keep up the great work.
Super smart :)
+pmsocho , Bill Szysz to the rescue!!!!
I do have a question about the prior formula you used before the one that Bill recommended:
COUNTIFS(INDEX($I$4:I4,MATCH(2,INDEX(1/($I$4:I4=0),))):I4,">0"),
What does the 2 do in the match function and what does the second index function do? Is this from a prior video? If so, can you provide the link to it so I can understand it?
+Horace Greenley , It is from my Book & DVD: Ctrl + Shift + Enter Mastering Excel Array Formulas:
www.amazon.com/Ctrl-Shift-Enter-Efficient-Calculating/dp/1615470077
This book and DVD go into great detail about the "Big Number" concept for looking up last item when doing approximate match lookup. Here is the video from the DVD that covers this topic:
Ctrl + Shift + Enter: Excel Array Formulas 13: Dynamic Ranges: INDEX & OFFSET Functions
ruclips.net/video/5ko5ES8ZLuY/видео.html
Thank you, I thought this might be the case. I did buy book and I do remember going over that but, as you know, unless you lose you lose it. I'm review the video. What does the second index function do in the Match formuia - I think I understand part of it but not really sure how it works
+Horace Greenley It should all become clear in this video:
Ctrl + Shift + Enter: Excel Array Formulas 13: Dynamic Ranges: INDEX & OFFSET Functions
ruclips.net/video/5ko5ES8ZLuY/видео.html
i have a question. im making document number base on Date. Like today is Feb. 27th. and i have 5 Documents and each document has unique numbers. something like this 0227-01,0227-02,0227-03,0227-04,0227-05. how to make it happen? please help. thank you for the video.
Guys, i'm new here but i'm not sure if i can ask your help here. I've been trying to do similar formula, a lilte different with sumifs on a table with range names. For example, i need a running total on a table with clients number and code number. It will increase the sum as long as the condition don't change. After some HOURS trying to figure it out, i came up with the solution, of course with sumifs, (i got a litle help i must say) BUT not with their range names but with it's relative positions, like $I3:$I12455. My question is: how could i do it with range names on my formula?
+Pedro Cabral da Camara , If you want mixed cell references with a Defined Name, it is possible! Just create name and then edit it to whatever type of cell reference that you would like. Use Name Manager, then edit.
Hi, could someone, please, explain to me what =(1+$B$1) means. Thanks
sir how can i divide excel data into three part.
for eg
i have 3 lac data and i divide 1 lac each in three part
+Akhilesh gupta I am sorry, but I do not understand your question. Can you re-state your question?
i have 3 lac data and have to divide 3 person equally.
+Akhilesh gupta
REPLY ME I AM WAITING
+Akhilesh gupta I do not know how to solve your problem. I am sorry.