Excel Formulas

gistfile1.txt

1. Age Calculator: =DATEDIF(M2,TODAY(),"Y") & " Years, " & DATEDIF(M2,TODAY(),"YM") & " Months, " & DATEDIF(M2,TODAY(),"MD") & " Days"
=DATEDIF(M2,N2,"YM") & " Months, " & DATEDIF(M2,N2,"MD") & " Days"
Only Month
=DATEDIF(A2,B2,"M")  [1st is small, 2nd is large Date]
Only Year
=DATEDIF(A2,B2,"Y")  [1st is small, 2nd is large Date]
			
2. Count:
	a. Counting Specific name:  =COUNTIF(F2:F10, "MISSED")
	b. Count Total Date: =COUNTIF(C2:C300,">3/1/2020")
	c. Count Empty Cell/Count Blank: =COUNTBLANK(B2:B10)
	d. Count Unique Name: =COUNTIF(A2:A5,"Unique Name")
	e. Counting Specific name at any Position:  =COUNTIF(BL2:BL501, "*NA*")
  	f. Count only Number: COUNT(A1:A10)
  	g. Count any Data: COUNTA(A1:A10)
	h. Count Frequency: =COUNTIF(F:F,F2)

11. Fillup blank cell by any text: Select Cell then Home>Editing>Find and Replace>Go To Special (Ctrl+G or F9)
    for example Press 1. Ctrl+G>Special...>Blank>Ok 2. press 99 3. Ctrl+Enter
12. Copy Same things to multiple Sheet: Copy Sheet>Select all Sheet>Editing>Fill>Across Worksheets...>All>OK
13.1 Identify the sequence number (Make extra Column and copy this function): =IF(A3-A2=1,"","Missing")
13.2 Identify the sequence Date (Make extra Column and copy this function): =IF(A3=A2+1,"","Missing next day")
14. Frequency Check: =COUNTIF(C:C,C2)
15. Remove Extra Spaces from Text in Excel: https://www.youtube.com/watch?v=axZj8Irt5W8
16. Split numbers into columns in Excel
	a. Insert a column
	b. Select multi medicine column
	c. Data>Text to Columns>Delimited>Next>Check-Space>Next>Destination setup Column where to inserted>Finish
	
17. Conditional Counting =SUMIF(C2:C400, "1", I2:I400)
18. DEP FInding: Press Ctrl+Shift+Enter (Not just Enter)
 =SUM(--((IFERROR(SMALL(IF(B2:B400=1,ROW(B2:B400)),ROW(B2:400)),0)-IFERROR(SMALL(IF(B2:B400=1,ROW(B2:B400)),ROW(B2:B400)-1),0))>3))+1
 =SUM(--((IFERROR(SMALL(IF(B2:B400=1;ROW(B2:B400));ROW(B2:400));0)-IFERROR(SMALL(IF(B2:B400=1;ROW(B2:B400));ROW(B2:B400)-1);0))>3))+1

18.1 a. Days of Diarrhea: 
     =COUNTIF(B2:B400, "1")

  b. Number of DEP:
     =SUM(--((IFERROR(SMALL(IF(B2:B300=1,ROW(B2:B300)),ROW(B2:B300)),0)-IFERROR(SMALL(IF(B2:B300=1,ROW(B2:B300)),ROW(B2:B300)-1),0))>3))+1


  c. Number of DEP Stool Collection: 
     =SUMIF(B2:B400, "1", D2:D400)
19. Next (Ctrl+Page Down) or Previous Sheet (Ctrl+Page Up)
20.a Remove last 2 characters from string:  =LEFT(C2,LEN(C2)-2)
20.b Remove first 2 characters from string: =RIGHT(A2,LEN(A2)-2)
20.c Extract middle 2 digit: =MID(G2,6,2)
21. Restrict a cell. That cell only Input Date (1-Jan-2021 to 31-Dec-2021) and “Missed”
    =OR(A1="Missed",AND(A1>=DATE(2021,1,1),A1<=DATE(2021,12,31)))
22. Input 0 to 50 and only 99
    =OR(AND(A1>0,A1<51),AND(A1=99))
23.1 Anthro Sequential Weight Check: =IF(AND(F2=F1,N2<N1),"Problem","OK")
23.2 Anthro Sequential Height Check: =IF(AND(F2=F1,R2<R1),"Problem","OK")
24. Copy conditional formatting from one sheet to another in Excel
	Select all sheet>Home>Format Painter>single click>select another or group sheet>Click Select All
	Video Tutorial: https://www.youtube.com/watch?v=ZXM8hcnOiRM

25. Merging Individual Data with Roster Data through Power Query in Excel: 
Step-1: Data>Get Data>From File>From Workbook>Select Repeat Group excel file>Import>Select Multiple Items>Load
Step-2: Get Data>Combine Queries>Merge>
	1 section=repeat group (2nd Sheet), 2 Section= Main group (1st Sheet)>Select "_index" and "_parent_index">OK
Step-3: Select Last Column (Family_Member) Filter Option>Select what you want>Uncheck "Use original column name as prefix">OK>Close and Load>Close and Load