Microsoft Excel Course Section 05 More on Entering Excel Formulas Notes Set 01

Advanced Study Material for Computer Science: MS Office

Explore structured advanced study materials through the Microsoft Excel Course Section 05 More on Entering Excel Formulas Notes Set 01. Tailored for learners, utilizing these Computer Science resources ensures thorough preparation and strengthens foundational knowledge before final evaluations.

Practice Computer Science Resources: MS Office

Access the complete useful resource PDF for Computer Science below. Regular practice with these targeted academic notes builds familiarity with complex topics and helps secure higher marks in final school evaluations.

Section 5 gives detailed examples of the Excel functions SUMIF, AVERAGEIF, COUNTIF, IF, AND, FREQUENCY, TREND and ZTEST, each with its syntax, arguments, remarks and worked formulas with results.

Section 5: More on Entering Excel Formulas

This Section looks at more examples of how to enter and execute Excel Formulas.

5.1 The SUMIF Function

Syntax

SUMIF(range,criteria,sum_range)

Range is the range of cells where Excel searches for the criteria that you want evaluated. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".

Sum_range are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.

Note: The SUMIF function can be read as: "Sum or add up sum_range if range meets criteria."

Example

A (Property Value)B (Commission)
2100,0007,000
3200,00014,000
4300,00021,000
5400,00028,000
FormulaDescription (Result)
=SUMIF(A2:A5,">160000",B2:B5)Sum of the commissions for property values over 160,000 (63,000)
=SUMIF(A2:A5,">160000")Sum of the property values over 160,000 (900,000)
=SUMIF(A2:A5,"=300000",B2:B3)Sum of the commissions for property values equal to 300,000 (21,000)

5.2 The AVERAGEIF Function

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

Syntax

AVERAGEIF(range,criteria,average_range)

Range is one or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Average_range is the actual set of cells to average. If omitted, range is used.

Note: The AVERAGEIF function can be read as: "Average average_range if range meets criteria."

Example: Averaging profits from regional offices

RegionProfits (Thousands)
East45,678
West23,789
North-4,789
South (New Office)0
MidWest9,678
FormulaDescription (Result)
=AVERAGEIF(A2:A6,"=*West",B2:B6)Average of all profits for the West and MidWest regions (16,733.5)
=AVERAGEIF(A2:A6,"<>*(New Office)",B2:B6)Average of all profits for all regions excluding new offices (18,589)

5.3 The COUNTIF Function

Counts the number of cells within a range that meet the given criteria.

Syntax

COUNTIF(range,criteria)

Range is one or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

Criteria is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Note: The COUNTIF function can be read as: "Count frequency or number of times or cells if range contains criteria."

Remark

You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example 1: Common COUNTIF formulas

Data (A)Data (B)
apples32
oranges54
peaches75
apples86
FormulaDescription (result)
=COUNTIF(A2:A5,"apples")Number of cells with apples in the first column above (2)
=COUNTIF(A2:A5,A4)Number of cells with peaches in the first column above (1)
=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)Number of cells with oranges and apples in the first column above (3)
=COUNTIF(B2:B5,">55")Number of cells with a value greater than 55 in the second column above (2)
=COUNTIF(B2:B5,"<>"&B4)Number of cells with a value not equal to 75 in the second column above (3)
=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")Number of cells with a value greater than or equal to 32 and less than or equal to 85 in the second column above (3)

Example 2: COUNTIF formulas using wildcard characters and handling blank values

Data (A)Data (B)
applesYes
orangesNo
peachesNo
applesYes

Cells A2:A7 and B2:B7 are used by the formulas; the table lists the entries in the order they appear, including the blank cells.

FormulaDescription (result)
=COUNTIF(A2:A7,"*es")Number of cells ending with the letters "es" in the first column above (4)
=COUNTIF(A2:A7,"?????es")Number of cells ending with the letters "es" and having exactly 7 letters in the first column above (2)
=COUNTIF(A2:A7,"*")Number of cells containing text in the first column above (4)
=COUNTIF(A2:A7,"<>"&"*")Number of cells not containing text in the first column above (2)
=COUNTIF(B2:B7,"No")/ROWS(B2:B7)The average number of No votes including blank cells in the second column above formatted as a percentage with no decimal places (33%)
=COUNTIF(B2:B7,"Yes")/(ROWS(B2:B7)-COUNTIF(B2:B7,"<>"&"*"))The average number of Yes votes excluding blank cells in the second column above formatted as a percentage with no decimal places (50%)

NOTE You can view the number as a percentage. Select the cell, and then on the Home tab in the Number group, click Percent Style.

5.4 The IF Function

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.

Value_if_true is the value that is returned if logical_test is TRUE.

Value_if_false is the value that is returned if logical_test is FALSE.

Remarks: Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.

Note: The IF function can be read as: "If Logical_test then Value_if_true otherwise Value_if_false"

Example

Score (A)
45
90
78
FormulaDescription (Result)
=IF(A2<50,"FAIL","PASS")Assigns either a pass or fail remark to the first score (FAIL)
=IF(A2>89,"A",IF(A2>79,"B",IF(A2>69,"C",IF(A2>59,"D","F"))))Assigns a letter grade to the first score (F)
=IF(A3>89,"A",IF(A3>79,"B",IF(A3>69,"C",IF(A3>59,"D","F"))))Assigns a letter grade to the second score (A)
=IF(A4>89,"A",IF(A4>79,"B",IF(A4>69,"C",IF(A4>59,"D","F"))))Assigns a letter grade to the third score (C)

5.5 The AND Function

Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

Syntax

AND(logical1,logical2, ...)

Logical1, logical2, ... are 1 to 255 conditions you want to test that can be either TRUE or FALSE.

Example 1

FormulaDescription (Result)
=AND(TRUE, TRUE)All arguments are TRUE (TRUE)
=AND(TRUE, FALSE)One argument is FALSE (FALSE)
=AND(2+2=4, 2+3=5)All arguments evaluate to TRUE (TRUE)

Example 2

Data (A)
50
104
FormulaDescription (Result)
=AND(1<A2,A2<100)Because 50 is between 1 and 100 (TRUE)
=IF(AND(1<A3,A3<100),A3, "The value is out of range.")Displays the second number above, if it is between 1 and 100, otherwise displays a message (The value is out of range.)
=IF(AND(1<A2,A2<100),A2, "The value is out of range.")Displays the first number above, if it is between 1 and 100, otherwise displays a message (50)

5.6 The FREQUENCY Function

Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula.

Syntax

FREQUENCY(data_array,bins_array)

Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

Remarks

1. FREQUENCY is entered as an array formula after you select a range of adjacent cells into which you want the returned distribution to appear.
2. The number of elements in the returned array is one more than the number of elements in bins_array.

Example

Scores (A)Bins (B)
7970
8579
7889
85
50
81
95
88
97
FormulaDescription (Result)
=FREQUENCY(A2:A10,B2:B4)Number of scores less than or equal to 70 (1); number of scores in the bin 71-79 (2); number of scores in the bin 80-89 (4); number of scores greater than or equal to 90 (2)

NOTE The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A12:A15, press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, there will be only one result in cell A12 (1).

5.7 The TREND Function

Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.

Syntax

TREND(known_y's,known_x's,new_x's,const)

Known_y's is the set of y-values you already know in the relationship y = mx + b. If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable. If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.

Known_x's is an optional set of x-values that you may already know in the relationship y = mx + b. The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a vector (that is, a range with a height of one row or a width of one column). If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.

New_x's are new x-values for which you want TREND to return corresponding y-values. New_x's must include a column (or row) for each independent variable, just as known_x's does. So, if known_y's is in a single column, known_x's and new_x's must have the same number of columns. If known_y's is in a single row, known_x's and new_x's must have the same number of rows. If you omit new_x's, it is assumed to be the same as known_x's. If you omit both known_x's and new_x's, they are assumed to be the array {1,2,3,...} that is the same size as known_y's.

Const is a logical value specifying whether to force the constant b to equal 0. If const is TRUE or omitted, b is calculated normally. If const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted so that y = mx.

Remarks

1. You can use TREND for polynomial curve fitting by regressing against the same variable raised to different powers. For example, suppose column A contains y-values and column B contains x-values. You can enter x^2 in column C, x^3 in column D, and so on, and then regress columns B through D against column A.
2. Formulas that return arrays must be entered as array formulas.
3. When entering an array constant for an argument such as known_x's, use commas to separate values in the same row and semicolons to separate rows.

Example

Month (A)Cost (B)
1133,890
2135,000
3135,790
4137,300
5138,130
6139,100
7139,900
8141,120
9141,890
10143,230
11144,000
12145,290
FormulaDescription
=TREND(B2:B13,A2:A13)Corresponding Cost (entered in C2:C13)
=TREND(B2:B13,A2:A13,A15:A19)Predicted Cost for the months 13, 14, 15, 16 and 17 listed in A15:A19 (entered in B15:B19)

NOTE The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range C2:C13 or B15:B19 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single results are 133953.3333 and 146171.5152.

5.8 The ZTEST Function

Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, mu0, ZTEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) - that is, the observed sample mean. To see how ZTEST can be used in a formula to compute a two-tailed probability value, see Remarks below.

Syntax

ZTEST(array,mu0,sigma)

Array is the array or range of data against which to test mu0. Mu0 is the value to test. Sigma is the population (known) standard deviation. If omitted, the sample standard deviation is used.

Remarks

If array is empty, ZTEST returns the #N/A error value. ZTEST is calculated as follows when sigma is not omitted:

ZTEST(array,mu0,sigma) = 1 - Norm.S.Dist((x - mu0) / (sigma / SQRT(n)), TRUE)

or when sigma is omitted:

ZTEST(array,mu0) = 1 - Norm.S.Dist((x - mu0) / (s / SQRT(n)), TRUE)

where x is the sample mean AVERAGE(array); s is the sample standard deviation STDEV(array); and n is the number of observations in the sample COUNT(array).

ZTEST represents the probability that the sample mean would be greater than the observed value AVERAGE(array), when the underlying population mean is mu0. From the symmetry of the Normal distribution, if AVERAGE(array) < mu0, ZTEST will return a value greater than 0.5.

The following Excel formula can be used to calculate the two-tailed probability that the sample mean would be further from mu0 (in either direction) than AVERAGE(array), when the underlying population mean is mu0:

=2 * MIN(ZTEST(array,mu0,sigma), 1 - ZTEST(array,mu0,sigma))

Example

Data (A)
3
6
7
8
6
5
4
2
1
9
FormulaDescription (Result)
=ZTEST(A2:A11,4)One-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 4 (0.090574)
=2 * MIN(ZTEST(A2:A11,4), 1 - ZTEST(A2:A11,4))Two-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 4 (0.181148)
=ZTEST(A2:A11,6)One-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 6 (0.863043)
=2 * MIN(ZTEST(A2:A11,6), 1 - ZTEST(A2:A11,6))Two-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 6 (0.273913)

You have reached the end of Section 5. You can save and close your file.

NOTE: The Excel Functions discussed in Section 5 are only a few of the numerous Excel Functions available in Microsoft Office 2007. More assistance and exposure can be obtained through the Microsoft Excel Help (F1).

Free CBSE Study Guides: Computer Science MS Office

Quick Revision Material for MS Office

Review targeted study resources for MS Office tailored for learners. Utilizing these structured notes and quick-revision tools ensures complete alignment with current CBSE evaluation standards.

Expert Notes and Solved Exam Questions

Designed around the official curriculum, these study guides guarantee standard compliance. Reviewing step-by-step solutions clarifies complex sub-topics within MS Office and demystifies standard marking schemes for Computer Science evaluations.

Additional Study Resources for

Maximize your grade potential by utilizing Computer Science practice papers in tandem with these structured notes. All printable assignments and interactive mock tests on our platform are updated for the 2026 session and available free of charge.

FAQs

Where can I find the most advanced study material for [current-page:node:field_board] [current-page:node:field_class] Computer Science for 2026-27?

The latest 2026-27 advanced study resources for Computer Science are available for free on StudiesToday.com which includes NCERT Exemplars, high-order thinking skills (HOTS) questions, and deep-dive concept summaries.

What does the 2026-27 Computer Science study package for [current-page:node:field_class] include?

Our exhaustive Computer Science package includes chapter wise revision notes, solved practice sheets, important formulas and Concept Maps to help in better understanding of all topics.

Is this study material enough for both [current-page:node:field_board] exams and competitive tests?

Yes. For , our resources have been developed to help you get better marks in school exams and also build fundamental strength needed for entrance tests including Competency Based learning.

How should [current-page:node:field_class] students use this Computer Science material for maximum marks?

in , students should use Active Recall method, read the concept summary, then solve the Important Questions section without looking at the answers and then check your answers.

Can I download [current-page:node:field_class] Computer Science study notes in PDF for offline use?

All Computer Science study materials are provided in mobile-friendly PDF. You can download and save them on your device.

Are the [current-page:node:field_class] Computer Science resources updated for the latest NEP guidelines?

Yes, our team has ensured that all Computer Science materials for are strictly aligned with the National Education Policy (NEP) 2020 and the latest 2026-27 syllabus.