Excel- Tutorial Week 3

Question 1

When using the copy down function in excel, how do you fix the column only?

Answer 1

Put a dollar sign behind the column letter e.g. $D9

Question 2

When using the copy down function in excel, how do you fix the row only?

Answer 2

Put a dollar sign behind the row number e.g. D$9

Question 3

When using the copy down function in excel, how do you fix both the column and row?

Answer 3

Put a dollar sign behind the column letter and the row number e.g. $D$9

Question 4

How would you work out the mean/average of some cell values?

Answer 4

Use average function

So click on cell you want to see the mean/average in and type =average and then highlight the cells from which you want the mean/average calculated

Question 5

How would you work out the maximum of some cell values?

Answer 5

Use maximum function

So click on cell you want to see the maximum in and type =max and then highlight the cells from which you want the maximum calculated

Question 6

How would you work out the minimum of some cell values?

Answer 6

Use minimum function

So click on cell you want to see the minimum in and type =min and then highlight the cells from which you want the minimum calculated

Question 7

How would you use an IF statement?

Answer 7

One way in which you could use it is if you want one value in a cell if a cell is greater than a certain and another if it is less

Select the cell you want the formula in and write =IF, then follow the syntax that appears for guidance
The logical test can be if D9>5 then the cell can show “large” (true test) and if the D9<5 then the cell can show “small”

Any word that is to be displayed needs to be written in double inverted commas

Question 8

How do use the count statement?

Answer 8

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
Syntax: COUNT(value1, [value2], …)

Very basic- literally just counts number of cells

Question 9

How do you use the COUNTIF statement?

Answer 9

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list. In its simplest form, COUNTIF says:
=COUNTIF(Where do you want to look?, What do you want to look for?)
Syntax: COUNTIF(range, criteria)

For example:
=COUNTIF(A2:A5,”London”)
=COUNTIF(A2:A5,A4)