Archives: Math and Trig

Calculate hours between two cells

We needed that several times in the past Then I found it in an old file in my laptop, thought to share it for others to help So, when A1 has the start time, B1 has the end time Time different in one day will be from formula =IF(OR(A1=””,B1=””),””,ABS((HOUR(A1)+MINUTE(A1)/60)-(HOUR(B1)+MINUTE(B1)/60))&” hrs”)   I added to prevent • Read More »

Fx to cut long column into two columns

Convert one long column into two This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column This was the answer for this question Yahoo! Answers question Show two • Read More »

Multiple Dual-Validations

It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it Mutliple-dual-validations that depend on each other. Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the • Read More »

Index, CountA, Names and Listbox to have active form reading from table

Using Index and CountA Formulas, Names and Forms > ListBox to have a list with connected formulas Can also be used as form to read from a large table in another sheet   After creating two names, one is “Months” having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1) And the other one is “Month_All” having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3) Draw a listbox using “Developer • Read More »

Validation based on another Validation

Creating a Data Validation based on another Data Validation Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it. You need to create two names (Formula > Name Manager), one has this formula =OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2)) The other one has this