MS Excel for Loksewa: The 15 Formulas and Functions That Keep Appearing

If you are preparing for a Computer Operator, Assistant Computer Operator, or IT Assistant exam in Nepal, Excel will show up in both the written paper and the practical test. The written paper asks what a formula returns or which function fits a situation. The practical test asks you to build a working sheet against the clock.

The good news is that you do not need hundreds of functions. A small core set covers most of what these exams ask. This guide walks through 15 of them with worked examples you can type in and check yourself.

Note: Exact syllabi differ between the Public Service Commission, provincial commissions, and organizations like the police, army, and banks. Always check the current vacancy notice. Older papers were also written for Excel 2007 and 2010, so newer functions such as XLOOKUP are rarely the focus. Master the classics below first.

The Sample Data

Type this into a blank sheet starting at cell A1. Every example below uses it.

Before the Functions: Cell References

Many exam mistakes come from references rather than functions. A relative reference like C2 changes when you copy the formula down. An absolute reference like $C$2 stays fixed. A mixed reference like $C2 or C$2 locks only the column or only the row. Press F4 while editing a formula to cycle through them. You will need absolute references for VLOOKUP tables and RANK ranges below.

The 15 Formulas and Functions

1. SUM

Adds the numbers in a range.

=SUM(C2:C6) returns 188000.

Use the AutoSum shortcut Alt + = in the practical exam to save time.

2. AVERAGE

Returns the arithmetic mean, ignoring blank cells and text.

=AVERAGE(D2:D6) returns 69.

3. MAX and MIN

Find the largest and smallest values.

=MAX(C2:C6) returns 50000, and =MIN(C2:C6) returns 28000.

4. COUNT, COUNTA, and COUNTBLANK

Exam setters love this trio because the difference is easy to confuse.

• COUNT counts cells containing numbers only.
• COUNTA counts all non-empty cells, including text.
• COUNTBLANK counts empty cells.

=COUNT(D2:D6) returns 5, while =COUNT(A2:A6) returns 0 because names are text. =COUNTA(A2:A6) returns 5.

5. IF

The most tested logical function. Syntax: =IF(condition, value_if_true, value_if_false).

=IF(D2>=40,"Pass","Fail") returns Pass for Ram and Fail for Bikash.

Nested IF handles more than two outcomes:

=IF(D2>=80,"Distinction",IF(D2>=60,"First",IF(D2>=40,"Pass","Fail")))

For Ram (72), this returns First. Each IF sits inside the "false" part of the previous one, so remember to close every bracket at the end.

6. AND and OR

These combine several conditions inside an IF. AND needs all conditions true, and OR needs at least one.

=IF(AND(D2>=40,C2>30000),"Eligible","Not Eligible")

For Ram, salary is exactly 30000 (not greater than), so the result is Not Eligible. For Sita it is Eligible. Watch boundary values like this, because they are a favorite trap.

7. COUNTIF

Counts cells that meet one condition.

=COUNTIF(B2:B6,"IT") returns 2.

=COUNTIF(D2:D6,">=40") returns 4. Comparison operators go inside quotation marks.

8. SUMIF

Adds values in one range based on a condition in another. Syntax: =SUMIF(range, criteria, sum_range).

=SUMIF(B2:B6,"IT",C2:C6) returns 95000, the total salary of the IT department.

A common error is mixing up range and sum_range. The first is where Excel looks for the condition, and the last is what it adds up.

9. VLOOKUP

Searches for a value in the first column of a table and returns a value from another column in the same row. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).

Exact match (FALSE):

=VLOOKUP("Hari",A2:D6,3,FALSE) returns 28000, the value in the third column of Hari's row.

Approximate match (TRUE): useful for grade bands. Create a grade table in F2:G5 with these rows: 0 → D, 40 → C, 60 → B, 80 → A. Then:

=VLOOKUP(D2,$F$2:$G$5,2,TRUE) returns B for Ram's 72.

For approximate matches, the first column of the lookup table must be sorted in ascending order. Forgetting this, or leaving out the last argument, is the classic VLOOKUP mistake. Also note that VLOOKUP cannot look to the left of the lookup column.

10. HLOOKUP

Works like VLOOKUP but searches across the top row and returns a value from a row below. If quarters are listed in A1:D1 and their sales in A2:D2:

=HLOOKUP("Q3",A1:D2,2,FALSE) returns the sales figure under Q3.

Use VLOOKUP when data runs down columns and HLOOKUP when it runs across rows.

11. ROUND

Rounds a number to a set number of digits.

=ROUND(45678.456,2) returns 45678.46.

=ROUND(45678.456,0) returns 45678.

=ROUND(45678.456,-2) returns 45700. A negative second argument rounds to the left of the decimal point (tens, hundreds, and so on). Also learn the related ROUNDUP and ROUNDDOWN.

12. RANK

Shows a value's position in a list. Syntax: =RANK(number, ref, order), where order 0 (or omitted) ranks highest first and 1 ranks lowest first.

=RANK(D2,$D$2:$D$6,0) returns 3 for Ram, because 91 and 85 are higher. Lock the range with $ so it does not shift when you copy the formula down.

13. CONCATENATE and the & Operator

Joins text from different cells.

=CONCATENATE(A2," - ",B2) returns Ram - Admin.

The shorter version does the same: =A2&" - "&B2. You can also use CONCAT in newer versions.

14. LEFT, RIGHT, and MID

Extract characters from text.

• =LEFT("Kathmandu",4) returns Kath.
• =RIGHT("Kathmandu",3) returns ndu.
• =MID("Kathmandu",5,3) returns man, meaning three characters starting from position 5.

These often appear in written papers as "what is the output?" questions. Count carefully from position 1, not 0.

15. TODAY and NOW

=TODAY() returns the current date, and =NOW() returns the current date and time. Both update whenever the sheet recalculates and take no arguments, though the brackets are still required.

Related functions worth knowing: DAY, MONTH, YEAR, and DATEDIF for calculating age or service length.

Bonus Topics That Frequently Come with These

• Pivot tables: know how to summarize data by category (for example, total salary by department) using Insert → PivotTable, and understand the four areas: Rows, Columns, Values, and Filters.
• Sorting and filtering: sort by one or more columns, and apply AutoFilter for conditions.
• Text helpers: LEN, UPPER, LOWER, PROPER, and TRIM are short and easy to memorize.
• Conditional formatting: highlight cells above or below a value, or duplicate values.
• Charts: be able to create and label a column, line, or pie chart.
• Error values: #DIV/0!, #N/A, #VALUE!, #REF!, and #NAME?, and what causes each.

Common Mistakes to Avoid

1. Forgetting $ in ranges when copying formulas down.
2. Skipping the exact-match argument in VLOOKUP. Write FALSE explicitly.
3. Mismatched brackets or quotation marks in nested IF formulas.
4. Typing numbers as text (for example, with a leading apostrophe), which makes SUM ignore them.
5. Not reading the question's wording, such as "greater than" versus "greater than or equal to."

How to Practice

Reading the syntax is not enough. Excel questions are easy to recognize once you have typed each formula and watched the result appear. Try this routine over one week:

• Days 1 to 2: functions 1 to 4, 11, and 12 (math and counting).
• Days 3 to 4: functions 5 to 8 (logic and conditional functions).
• Day 5: functions 9 and 10 (lookups) with a grade table and an employee table.
• Day 6: functions 13 to 15 (text and date).
• Day 7: build a full payroll or result sheet that uses at least eight of the functions above, with a timer running.

Then test yourself on real questions. Work through the Excel and office-package questions in previous papers, such as the PSC Computer Operator 2082 paper or the Loksewa Computer Operator 2082 set, and browse the full exam archive to find more.

Final Word

Excel rewards repetition. Fifteen functions, practiced until you can type them without looking, will cover most of what the written and practical papers ask. Build the sample sheet above today, then change the data and predict each result before you press Enter.