Page 418 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

OKAY! The main focus is now on the first argument and the second argument. You see the first argument is a
range, and the second argument is also a range. And the whole formula is an array formula.
• IF(B5:B14>0,B5:B14,FALSE) → here Excel will create an array internally with the positive numbers and False
statements.
• Output → {15, FALSE, 14, FALSE, FALSE, 45, 78, 65, 54, FALSE}
AVERAGE(IF(B5:B14>0,B5:B14,FALSE)) → this becomes AVERAGE({15, FALSE, 14, FALSE, FALSE, 45,
78, 65, 54, FALSE}).

• Output → 45.167
The AVERAGE function finds out the average of the values in the array, except the FALSE values, the False
values are not numbers, so the AVERAGE function neglects the FALSE values.

• As usual, press ENTER.

Related Exercises:
• You are managing a bookstore and want to calculate the total revenue generated by each genre of books over
a specific period. Create an Excel spreadsheet with the following details:
• Column A: Book titles.
• Column B: Genre of each book.

• Column C: Unit price for each book.
• Column D: Number of units sold for each book.
• Row 1: Use an array formula to calculate the total revenue generated by each genre by summing the
revenue from each book sold in that genre. Ensure that the array formula adjusts correctly for each genre
as you copy it across.
• You are organizing a charity event and want to track the donations received from different sources. Create an
Excel spreadsheet with the following details:
• Column A: Donation amounts.
• Column B: Donation sources (e.g., Individuals, Corporations, Organizations, etc.).
• Row 1: Use an array formula to calculate the total donations received from each source by summing the
donations from each source. Ensure that the array formula adjusts correctly for each donation source as
you copy it across.

403
CITS : IT & ITES - Computer Software Application - Exercise 68 CITS : IT & ITES - Computer Software Application - Exercise 68