Excel-Buzz

Hi One and All, This articles demonstrates that how can many times a number has repeated consecutively. So here is the explanation:- The Array formula I have used is this, which has been dragged down.. =IF(AND(A4<>0,A5=0,A6=0),MATCH(FALSE,A5:A$16=0,0)-1,"") ----(1) First I will explain you the And() part of the formula… i.e. AND(A4<>0,A5=0,A6=0)

Hello Everyone, So here is the situation.. As you can see, I am arranging data from array or table into a list..  Actually this can be done with two different formulas based on similar kind of approaches… Now first we will take INDEX Function Approach… The formula used is.. =INDEX($A$1:$C$4 , 1+INT((ROWS($D$1:D1)-1)/3) , 1+MOD(ROWS($D$1:D1)-1,3)) Table Row No. Column No. Actually, the trick lies in calculating Row and Column No. Based on the TABLE INDEX we have used.. I.e. $A$1:C$$4, we have to use following Row and column No's Row No. Column No. 1 1 For Row no., I have used <---- 1 ...

Hi one and all, This time I have come up with a macro that will be activated automatically when you open the file and will ask you at regular intervals say 5 minutes, 10 minutes to save your file. So here are the steps:- 1. Open the Workbook. Press Alt + F11 to open Visual Basic Window. 2. On the Left Side of the Screen, there would be a Thisworkbook Module. Double Click it. 3. Now Copy and Paste the code below there:- Private Sub Workbook_Open() Call Ontime End Sub 4. Now Insert Module from the Insert Menu in the Menu Bar. 5. Copy and Paste the following code there:- Dim Hours, Minutes, Seconds Sub Backup() Ans = MsgBox("Wanna Save the File..?", vbYesNo) If Ans = vbYes Then ActiveWorkbook.Save Call Ontime End Sub Sub Ontime() Hours = 0  'Put Hours here Minutes = 1  'Put Minutes here Seconds = 0  'Put Seconds here Application.Ontime Now + TimeSerial(Hours, Minutes, Seconds), "Backup" End ...

Explanation… Formula used =INDEX($C$2:$C$9,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$9),0),1) First of all, we will try to understand this part of the forumla.. ie. COUNTIF($E$1:E1,$C$2:$C$9) COUNTIF(Range,Criteria) when entered as any array formula .. Countif will count of each element to Criteria array (ie. AAA, BBB, CCC and so on) in Range array (is {0}) it returns an array of the same size or length as the Criteria Range ie. {0;0;0;0;0;0;0;0} it is returning zeros as it has not found any match for each element of Criteria array in Range a...