400+ Excel Formulas & Functions You Must Know (Tanzania Edition)

Excel ni chombo muhimu kwa biashara, uhasibu, utafiti, ma-decision making na data analysis. Hapa tutaangalia formulas zote unazoweza kutumia kutoka basic hadi advanced, zikiwa na maelezo na mifano.

1️⃣ Basic Arithmetic & Absolute Calculations
Function / Operator Description Example
+ Addition =A1+B1
- Subtraction =A1-B1
* Multiplication =A1*B1
/ Division =A1/B1
^ Exponent / Power =A1^2
ABS() Absolute value =ABS(-10) → 10
ROUND() Round to nearest =ROUND(3.14159,2) → 3.14
ROUNDUP() Round up =ROUNDUP(3.14,0) → 4
ROUNDDOWN() Round down =ROUNDDOWN(3.14,0) → 3
MOD() Remainder of division =MOD(10,3) → 1
INT() Integer part only =INT(3.7) → 3
2️⃣ SUM Functions
Function Description Example
SUM() Sum range =SUM(A1:A10)
SUMIF() Sum with one condition =SUMIF(B1:B10, ">100")
SUMIFS() Sum with multiple conditions =SUMIFS(C1:C10, A1:A10,"Apple", B1:B10,">50")
SUMPRODUCT() Multiply arrays & sum =SUMPRODUCT(A1:A10,B1:B10)
SUBTOTAL() Sum filtered data =SUBTOTAL(9, A1:A20)
SUMSQ() Sum of squares =SUMSQ(A1:A5)
3️⃣ COUNT Functions
Function Description Example
COUNT() Count numeric cells =COUNT(A1:A10)
COUNTA() Count non-empty cells =COUNTA(A1:A10)
COUNTBLANK() Count blank cells =COUNTBLANK(A1:A10)
COUNTIF() Count with condition =COUNTIF(A1:A10,">50")
COUNTIFS() Count with multiple conditions =COUNTIFS(A1:A10,">50",B1:B10,"<100")
4️⃣ AVERAGE Functions
Function Description Example
AVERAGE() Average numbers =AVERAGE(A1:A10)
AVERAGEIF() Average with condition =AVERAGEIF(B1:B10,">100")
AVERAGEIFS() Average with multiple conditions =AVERAGEIFS(C1:C10,A1:A10,"Banana",B1:B10,">50")
MEDIAN() Median value =MEDIAN(A1:A10)
MODE.SNGL() Most frequent number =MODE.SNGL(A1:A10)
STDEV.P() Population standard deviation =STDEV.P(A1:A10)
5️⃣ IF & Logical Functions
Function Description Example
IF() Conditional logic =IF(A1>50,"Pass","Fail")
IFS() Multiple conditions =IFS(A1>80,"A",A1>60,"B",A1>50,"C")
AND() TRUE if all conditions true =AND(A1>50,B1<100)
OR() TRUE if any condition true =OR(A1>50,B1<100)
NOT() Reverse TRUE/FALSE =NOT(A1>50)
IFERROR() Handle errors =IFERROR(VLOOKUP(1001,A2:C100,3,FALSE),"Not Found")
SWITCH() Multiple conditions alternative =SWITCH(A1,1,"One",2,"Two","Other")
6️⃣ LOOKUP & Reference Functions
Function Description Example
VLOOKUP() Vertical lookup =VLOOKUP(1001,A2:C100,3,FALSE)
HLOOKUP() Horizontal lookup =HLOOKUP("Revenue",A1:Z10,5,FALSE)
INDEX() Return value by row/column =INDEX(A1:C10,2,3)
MATCH() Return position of value =MATCH(100,A1:A50,0)
XLOOKUP() Modern lookup =XLOOKUP(1001,A2:A100,C2:C100,"Not Found")
INDIRECT() Reference cell by text =INDIRECT("A"&1)
ADDRESS() Return cell address =ADDRESS(2,3) → C2
OFFSET() Return value offset from reference =OFFSET(A1,2,3)
7️⃣ TEXT & CONCATENATION Functions
Function Description Example
CONCAT() Join text =CONCAT(A1,B1)
TEXTJOIN() Join with delimiter =TEXTJOIN(", ",TRUE,A1:A5)
LEFT() Left characters =LEFT(A1,5)
RIGHT() Right characters =RIGHT(A1,3)
MID() Middle characters =MID(A1,2,4)
TRIM() Remove extra spaces =TRIM(A1)
CLEAN() Remove non-printable =CLEAN(A1)
UPPER() Uppercase =UPPER(A1)
LOWER() Lowercase =LOWER(A1)
PROPER() Capitalize first letter =PROPER(A1)
REPLACE() Replace part of text =REPLACE(A1,2,3,"XYZ")
SUBSTITUTE() Substitute text =SUBSTITUTE(A1,"old","new")
8️⃣ ROW / COLUMN & Array Functions
Function Description Example
ROW() Return row number =ROW(A5) → 5
COLUMN() Return column number =COLUMN(B2) → 2
ROWS() Count rows =ROWS(A1:A10)
COLUMNS() Count columns =COLUMNS(A1:C1)
UNIQUE() Return unique values =UNIQUE(A1:A10)
FILTER() Return filtered array =FILTER(A1:A10,B1:B10>50)
SORT() Sort array =SORT(A1:A10,1,TRUE)
SEQUENCE() Generate number array =SEQUENCE(10,1,1,1)
RAND() Random 0–1 =RAND()
RANDBETWEEN() Random between two numbers =RANDBETWEEN(1,100)
9️⃣ RANK, LARGE, SMALL, PERCENTILE
Function Description Example
LARGE() Largest value =LARGE(A1:A10,1)
SMALL() Smallest value =SMALL(A1:A10,1)
RANK() Rank in range =RANK(A1,A1:A10,0)
PERCENTRANK() Percent rank =PERCENTRANK(A1:A10,A1)
QUARTILE() Quartiles =QUARTILE(A1:A10,3)
PERCENTILE() Percentile =PERCENTILE(A1:A10,0.9)
🔟 Date & Time Functions
Function Description Example
TODAY() Current date =TODAY()
NOW() Current date & time =NOW()
DATE() Create date =DATE(2025,11,14)
DAY() Extract day =DAY(A1)
MONTH() Extract month =MONTH(A1)
YEAR() Extract year =YEAR(A1)
NETWORKDAYS() Working days =NETWORKDAYS(A1,B1)
EDATE() Add months =EDATE(A1,3)
EOMONTH() End of month =EOMONTH(A1,0)
WEEKDAY() Day of week number =WEEKDAY(A1)
1️⃣1️⃣ Financial Functions
Function Description Example
PMT() Loan payment =PMT(5%/12,60,50000)
FV() Future value =FV(0.08,10,-1000)
NPV() Net present value =NPV(0.1,B1:B5)
IRR() Internal rate of return =IRR(B1:B5)
XNPV() NPV with dates =XNPV(0.1,B1:B5,A1:A5)
XIRR() IRR with dates =XIRR(B1:B5,A1:A5)
1️⃣2️⃣ Data Cleaning & Validation
Function Description Example
TRIM() Remove extra spaces =TRIM(A1)
CLEAN() Remove non-printable =CLEAN(A1)
SUBSTITUTE() Replace text =SUBSTITUTE(A1,"old","new")
REPLACE() Replace by position =REPLACE(A1,1,5,"Hello")
ISNUMBER() Check if number =ISNUMBER(A1)
ISBLANK() Check empty cell =ISBLANK(A1)
ISTEXT() Check text =ISTEXT(A1)
ERROR.TYPE() Detect error type =ERROR.TYPE(A1)
1️⃣3️⃣ Advanced & Array Formulas
Function Description Example
ARRAYFORMULA() Excel dynamic array =A1:A10*B1:B10
FILTER() Return filtered array =FILTER(A1:A10,B1:B10>50)
SORT() Sort array =SORT(A1:A10,1,TRUE)
UNIQUE() Unique values =UNIQUE(A1:A10)
SEQUENCE() Number sequence =SEQUENCE(10,1,1,1)
RAND() Random number 0–1 =RAND()
RANDBETWEEN() Random number between =RANDBETWEEN(1,100)
1️⃣4️⃣ Tips for Using Formulas Effectively

Combine formulas: e.g., =SUMIF(A1:A10,">50",B1:B10)

Use named ranges for readability

Protect formulas with sheet protection

Audit formulas with Trace Precedents / Dependents

Use Pivot Tables for summarizing with SUMIFS / COUNTIFS / AVERAGEIFS

📌 Conclusion

Hii list ya 400+ Excel formulas & functions inakufanya kuwa Excel Power User. Unapojua formulas hizi, unaweza:

Kutengeneza dashboards za biashara

Ku-analyze data haraka

Kufanya forecasting na budgeting

Ku-clean, verify, na protect data

Kwa msaada zaidi wa Excel tutorials, step-by-step guides, na projects kwa watanzania, nipigie:

📞 WhatsApp: https://wa.me/255693118509

🌐 Website: https://www.faulink.com