formulas
| ID | Description | Formula | Remarks |
|---|---|---|---|
| ef06 | Add +1 to below non-blank cell | =INDEX(
(
INDIRECT(ADDRESS(ROW() + 1, COLUMN(), 1, TRUE)) :
INDIRECT(ADDRESS(ROW() + 9999, COLUMN(), 1, TRUE))
),
MATCH(
TRUE,
INDEX(
(
(
INDIRECT(ADDRESS(ROW() + 1, COLUMN(), 1, TRUE)) :
INDIRECT(ADDRESS(ROW() + 9999, COLUMN(), 1, TRUE))
) <> 0
),
0
),
0
)
) + 1
| Finds first non-zero (or non-blank) value in the current column below and adds 1 to it |
| ef05 | Cell Has Formula | =ISFORMULA(
INDIRECT("rc", FALSE)
)
| Color Code : #CCFFFF |
| ef04 | Quarter and Financial Year | ="Q"&MOD(ROUNDUP(MONTH(B1)/3,0)+2,4)+1 & " FY" & IF(MONTH(B1)>=4,YEAR(B1)&"-"&RIGHT(YEAR(B1)+1,2),YEAR(B1)-1&"-"&RIGHT(YEAR(B1),2)) | |
| ef03 | Quarter and Short Financial Year | ="Q"&MOD(ROUNDUP(MONTH(B1)/3,0)+2,4)+1 & " FY" & RIGHT(IF(MONTH(B1)>=4,YEAR(B1)+1,YEAR(B1)),2) | |
| ef02 | Tax Period in “MMM-YYYY” | =TEXT(B1, "mmm-yyyy") | |
| ef01 | Date Difference in Years Months and Days | =DATEDIF(B1,A1,"Y")&" Years, "&DATEDIF(B1,A1,"YM")&" Months, "&DATEDIF(B1,A1,"MD")&" Days" |
formulas.txt · Last modified: by admin