Task 3. Examining the distribution of education

related expenditures

The data set provides information on the total education

related expenditure for each child during

the last academic year if the child went to school during the period. The team would like to examine

the expenditures across different school levels.

Task 3.1

First, the expenditure data should be disaggregated by school

age group. Follow the instructions

below to create 3 new variables based on "Total educational expenditure for the last school year (in

riels)" and "Age".

1)

Use the 3 columns next to "Total educational expenditure for the last school year (in riels)".

Label the columns. For example, "Expenditure, age 6

11", "Expenditure, age 12

14",

"Expenditure, age 15

17".

2)

For the first column, in the first cell under the label, type

=IF(AND($R2<12,$AV2<>""),$AV2,"")

3)

Then, copy and paste it to the remaining cells in the column.

4)

For the second column, you'd type

=IF(AND($R2>11,$R2<15,$AV2<>""),$AV2,"")

5)

For the third column, think by yourself.

6)

Examine the 3 new columns to check if you successfully disaggregated "Total educational

expenditure for the last school year (in riels)" by school

age group.

We used "" to ensure that the empty cells in "Total educational expenditure for the last school year

(in riels)" are preserved, instead of being automatically converted to the value 0. This is important

because it is unclear whether the empty cell represents zero spending or missing information.

However, while the use of "" generates empty cells to our eyes, it prompts Excel to insert a so

called

zero

length string in those cells. As a result, Excel will not recognise them as empty. Because this text

information in each empty

looking cell will interfere with computation, we should remove them

before analysing the 3 created variables. How do we remove something that is already invisible to

our eyes?

1)

Copy the 3 new columns. Then, paste onto the Task 3 sheet from cell A1, using "Paste" =>

"Paste Values" => "Values". This way, the entries are no longer formulae.

2)

To see if the empty cells are really empty, type =ISBLANK(A2) and press Enter at any cell

except columns A to C. You will then see FALSE to confirm that cell A2 is actually not empty.

(If empty, you'll get TRUE.)

3)

We first change the entry in the empty

looking cells to any visible entry, such as letter z. It is

recommended to use a letter instead of a number in order to avoid any potential mix

up

7

with actual expenditure data. Highlight the block A2:C1267. Go to Find & Select. Choose

Replace. Do not type anything in "Find what". Type any letter (say, z) in "Replace with". Tick

"Match entire cell contents". Click Replace All. Now you'll see all previously empty looking