Computer application ---Excel Assignment

1
Computer application---Excel Assignment

• Group 1
• Cheung Kwai Fong (2)
• Ko Ka Wing (6)
• Shui Ching Wan (19)

2
AVERAGE

• Description IT returns the average (mean) of the
  arguments. The arguments must either be number,
  or names, arrays or references that contain
  numbers. If an array or reference argument
  contains non-numerical data, like text, logical
  values, or empty cells, those values are ignored
  however, cells with the value zero are included.

3
AVERAGE

• Formula
• Average (Starting_cell Ending_cell)
• Example Average(A1A3)
• Result 8
• PS AVG () is not a function in Excel.
• Average (range) can be implemented by SUM
  (range)/COUNT (range)

A
1 5
2 15
3 4
4
COUNT

• Description It counts the number of cells that
  contain numbers. Non-numerical data (i.e. empty
  cells, logical values, text, or error values) in
  the cells will be ignored. If you need to count
  non-numerical data, use the COUNTA () function.
• It does not distinguish the representation of
  data, e.g.1/3/2007 and HK3.00 will be count when
  place in the cell. Formulas that return numerical
  value (even zero) will also be counted.

5
COUNTA

• COUNTA (range) (where A stands for all) counts
  the number of cells that are not empty. Formulas
  that return empty text () will also be counted.

6
COUNTBLANK

• COUNTBLANK (range) counts the number of empty
  cells in a specified range. Formulas that return
  empty text () will also be counted.

7
COUNTIF

• COUNTIF (range, criteria) counts the number of
  cells that meet the given criteria.
• The criteria is expressed as a string that
  starts with a relational operator, such as , ltgt,
  lt, gt, gt or lt.
• If the criteria involves a reference, the
  reference must be placed outside the quotation
  mark and the concatenation operator must be use.
• In particular, the equal operator () can be
  omitted.
• E.g. APPLES (same as APPLES). 44 (same as
  44)
• For equality involving a number the quotation
  marks can be omitted further,
• E.g. COUNTIF (A1A10, 44) is the same as
  COUNTIF (A1A10,44)

8
LARGE

• LARGE (range, k) returns the kth largest value
  in a data set, where k is1,2,3,
• LARGE (range, 1) is the same as MAX (range, 1)

9
MAX

• MAX () returns the largest value in a set of
  values. Like SUM () and AVERAGE (), cell
  references with non-numerical values are ignored.
  If logical values and text must not be ignored,
  use MAXA () instead. If the arguments contain no
  numbers, MAX () returns 0 (zero)
• For MAXA A stand for ALL
• Max(A1A3)
• RESULT 15

A
1 5
2 15
3 4
10
MIN

• MIN (range) returns the smallest value in a set
  of values. If the arguments contain no number,
  MIN () returns 0 (zero).
• Min(A1A3)
• RESULT 4

A
1 5
2 15
3 4
11
RANK

• RANK (number, range, order) returns the rank of
  a number in a list of numbers. The rank of a
  number is its position relative to other values
  in a list.
• RANK (number whose rank you want to find, a
  range of numbers, order).
• If order is 0 (zero) or omitted(??), Excel ranks
  the highest score as 1 (i.e. Descending order).
  Otherwise, Excel ranks the lowest score as 1
  (i.e. ascending order).
• If two numbers in the list are equal, they will
  have the same rank. The succeeding rank will be
  skipped and will not be assigned to any numbers.
  For example, to rank a list of marks in
  descending order, if two students have the same
  mark 70 and ranked 2, then the student with mark
  69 will be ranked 4 (no student will have a rank
  of 3)

12
SMALL

• SMALL (range, k) returns the kth smallest value
  in a data set, where k is 1,2,3
• If there are n data in the range, SMALL (range,
  1) returns the smallest value, and SMALL (range,
  n) returns the largest value.
• SMALL (range, 1) is the same as MIN (range, 1)

13
SUM

• SUM (range) adds all the values in the
  arguments. The arguments may be numbers, names,
  or references that contain numbers.
• You may sum a column(?),a row(?), an area or
  several areas. You may also mix cell references
  with numbers
• e.g. SUM (A1A2, B3C4,2)
• the sum of the are A1 to A2 and B3 to C4 and the
  nunber 2

14
SUMIF

• SUMIF (range, criteria, sum _ range) adds the
  cells that meet the given criteria. It can be
  used with two or three arguments
• SUMIF () with 2 arguments
• In this case, you supply one range of cells to
  SUMIF () and omit sum_range. Values that meet the
  given criteria will be summed. In other words,
  the criteria must be numerical and the range of
  cells are numbers.
• e.g. If SUMIF (xx xx, 4), the cells with a
  value equal to 4 will be added. Similarly, if
  SUMIF (xx xx.gt4), the cells with a value
  higher than 4 will be added.
• SUMIF () with 3 arguments
• In this case, two ranges of cells are provided as
  1st and 3rd arguments. The first range of cells
  are used to determine the criteria, the second
  range are cells which will be added. The first
  range can be text or numbers, but the second
  range must be numbers.

15
SUMPRODUCT

• SUMPRODUCT (array1, array2, array3...)
• SUMPRODUCT is a hidden gem within Excel. It's a
  function that accountants should get to grips
  with. You can use it for data analysis where
  previously you might have thought VBA was
  required.
• SUMPRODUCT was not originally designed to solve
  the problems below so you cannot call SUMPRODUCT
  using the function icon or from the menu bar
  "Insert/Function" to solve these problems. The
  dialog box offered by Excel is totally useless in
  these situations. You need to manually develop
  the formulas  "SUMPRODUCT (..."

16
SUMSQ

• SUMSQ (value 1, value 2) returns the sum of the
  squares of all the values and cells referenced in
  the argument list
• e.g.sumsq (5,6)
• 615566
• E.g. Let us assume that the cells A1, A2... A5
  contain numbers 11, 15, 17, 21, and 43. Then
• SUMSQ (A1A5) equals 2925

17
FREQUENT

• FREQUENCY (data, bins_vector) calculates how
  often numbers occur within a set of data, and
  then returns a vertical array of counts. The 1st
  argument, data, is the given table of data. The
  2nd argument, bins_vector, is the column of
  numbers for comparison.
• As FREQUENCY (data, bins_vector) returns multiple
  values, it is an array formula. You have to
  select multiple cells, press F2, enter the
  formula and then press CTRLSHIFTENTER.
• FFEQUENCY () is designed for number only.
  Non-numerical data, such as text, will return 0.
• Bins_vector must be in a vertical column.
• The values in bins_vector must be ascending down
  the column.