ants123456789

Data Science: Excel for Data Mining

In our new series, you will be interested if you are beginner in data mining or visual learner. This series of data mining articles will be a great learning on the data mining techniques and its mathematical background. Let discuss further on excel basic in this article to prepare our excel data mining journey.

Why Excel?

excel cartoon
excel cartoon
  1. Excel allows you to work with data in a transparent manner,, meaning when n excel file is opened, the data is visible immediately and every step of data processing is also visible.
  2. Intermediate result contained in excel worksheet and can be examined while you care conducting your mining task.

Prepare Some Excel Skills

  1. Here are several fundamental excel skills and functions that we need to be familiar.

Formula

  1. Formula is the most important feature of excel.
  2.  Simple formulas always start with an equal sign (=), followed by constants that are numeric values and calculation operators such as plus (+), minus (–), asterisk(*), or forward slash (/) signs.
  3. Step by step:
    • Select C1 and type (=)
    • Right click A1
    • Type (+)
    • Right click A2
    • Press enter
formulas1
formulas1

Absolute Reference

  1. When you copy A FORMULA that contains cell references, what happens to the cell references?
  2. Usually the CELL REFERENCES will CHANGE! If you copy a formula 2 rows to the right, then the cell references in the formula will shift 2 cells to the right. 
  3. Absolute reference is when a reference has the dollar sign ($).
  4. It locks a reference in the formula.
  5. Add $ to the formula to use absolute references.
  6. Place a “$” before a row number if you want that to always stay the same.
    1. For example, “$C$3” refers to cell C3, and “$C$3” will work exactly the same as “C3”

IF Function Series

IF Function Series

IF FUNCTION SERIES
IF FUNCTION SERIES
  1. The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result.
    • For example, to “pass” scores above 70: =IF(A1>70,”Pass”,”Fail”).
  2. More than one condition can be tested by nesting IF functions.
  3. The IF function can be combined with logical functions like AND and OR to extend the logical test.

Syntax =IF (logical_test, [value_if_true], [value_if_false])

COUNTIF(S) FUNCTION

  1. COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. 
  2. COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

Syntax =COUNTIF (range, criteria)

Syntax =COUNTIFS (range1, criteria1, [range2], [criteria2], …)

SUMIFS FUNCTION

  1. SUMIFS is a function to sum cells that meet multiple criteria.
  2. SUMIFS can be used to sum values when corresponding cells meet criteria based on dates, numbers, and text. 

Syntax =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …)

AVERAGEIF FUNCTION

  1. AVERAGEIF function calculates the average of numbers in a range that meet supplied criteria.

Syntax =AVERAGEIF (range, criteria, [average_range])

Finally, we learn some of key functions in excel that is important for data mining.

Thanks you and take care.

Leave a comment