Function IF, VLOOKUP, PIVOT TABLE, CHART, WEKA, Cluster Analysis, Data Mining, Microsoft Excel

This document provides an overview of various functions and tools used in Data Handling & Business Intelligence. It covers functions like IF, VLOOKUP, PIVOT TABLE, and CHART in Microsoft Excel. It also explains the concept of WEKA, Cluster Analysis, and Data Mining. Additionally, it discusses the advantages and disadvantages of using WEKA over Microsoft Excel. Overall, it provides valuable insights into the tools and techniques used in data analysis and business intelligence.

Data Handling & Business
Intelligence

Definitions –
a) Function IF
In Excel, IF function is considered as one of the useful one, which allows users to make
logical comparisons among set of given value on the basis of expectations (Schmidt, Church and
Riley, 2020). This functions when a condition is given, checks whether it met or not. In case of
condition is met, then it returns true value otherwise another value in false situation. The syntax
for IF function in MS-Excel used as – IF(Condition, “True value”, “False value”.
For example – To underscore a number, whether it lies above a limit or below it as
“High” or “Low”, then IF Function can be used in following way –
As per this example, in row B, input the formula of IF as - =IF (A2>500, “High”, “Low”)
then in case, if value of A2 lies above given condition, then respective function gives value as
“High” otherwise “Low”. Therefore, IF function in Excel refers to a built-in function which is
categorized in terms of Logical Function. So, this function can be utilised as a worksheet
function, as part of a formula in worksheet’s cell.
b) Function VLOOKUP
The VLOOKUP function in excel, performs a vertical lookup through searching for a
value within first column of a table, then return the value in same row in terms of index_number
position (Anderson and et. al., 2020). It is a built-in function which is categorized in excel, as a
Lookup or Reference Function. In terms of worksheet function, VLOOKUP can be entered as a
formula in desired cell. The syntax for function VLOOKUP in MS-Excel used as – VLOOKUP
(value, table, index_number, [approximate_match]. Here, first parameter i.e. value indicates the
value to be searched for in Table’s first column. While, table depicts two or more columns of
1

data which is sorted within ascending order. Index_number shows column no. in table where
matching value needs to be returned. The last parameter i.e. [approximate_match] is optional
one, where Enter FALSE to search an exact match, while Enter TRUE to identify an
approximate match.
For example –
In this mentioned spreadsheet, VLOOKUP function can be used as –
=VLOOKUP(10251, A1:B6, 2, FALSE)
then, it will the below result as -
"Pears" 'Returns value in second column
c) PIVOT TABLE
This function refers to a table of statistics which summarises the data from a spreadsheet,
database or business intelligence program, into a meaningful way. Such summarised data
includes averages, sums or other statistical information, by arranging or rearranging the same to
draw the users’ attention (Lee, Kerler and Ivancevich, 2018). In Microsoft Excel, PIVOT Table
is considered as most powerful tool and intimidating functions, because it helps in understanding
large numerical data by exploring or extracting meaningful information from jumbled and
seemingly endless data. Through extracting and grouping data, aid users to draw valid and
accurate conclusion. Thus, this pivot tool can be used in business context for summarising and
reorganising selected rows and columns in a spreadsheet to obtain desired output (Basheer and
et. al., 2019).
2

d) CHART
A picture shows worth of thousand words in a simple way, that helps to understand and
visualize large data in easy manner. Therefore, CHART refers to one of the important function,
that visualise entire trends and patterns of a data set graphically. MS Excel provides a number of
options for transforming data into graphical figure as Pie chart, Bar chart, Column chart, Line
chart and more (Repaso and Capariño, 2020). Hereby, Pie chart helps in quantifying selected
data items either as percentage or in direct value form. While Bar chart is made to compare data
by categorising information to some useful groups and run the same horizontally. On contrast,
column chart shows data into horizontal plane. Line chart basically makes to represent
information or trends over a time period as monthly, quarterly, days or more.
Easiest function
Among all above tools or functions of Excel, CHART and Pivot tables both are considered
as the most powerful and useful tool, which is being utilised more in business. PivotCharts gives
complement to PivotTables through adding the visualizations to summarise data in a PivotTable
(Alghamdi and Alharby, 2019). It allows users or business persons to easily compare current
sales, profitability and other performance with previous one, so that in case of loss, informed
decisions can be taken for improvement in future performance. Along with this, external data
sources like SQL Server tables, Azure Marketplace, XML files, Office Data Connection (.odc)
files, SQL Server Analysis Services cubes, and more, can also be connected with files to analyse
business information more specifically in critical way.
e) What is WEKA?
WEKA refers to Waikato Environment for Knowledge Analysis, is an open source
software that provides tool used for data pre-processing, implementation of various machine
learning algorithms (Bansal and Srivastava, 2018). It provides visualisation tools to users for
developing machine learning techniques then applying the same to real-world problems of data
mining. In other words, Weka defines as a collection of the machine learning algorithms to
resolve data mining tasks, where algorithms can be applied to a dataset directly (Sulianta and
Fauzi, 2020). This software like Excel contains a number of tools for data pre-processing like
visualisation, classification, regression, association rules and clustering as summarised by below
figure –
3

Figure 1: WEKA Software
WEKA Software proves beneficial for employers or associated persons of business
entities in properly handling the data, by applying many measures. In business, WEKA data
mining uses as an approach for evaluating insight of business related information, so that distinct
perspective can be gained for obtaining better suggestions on improvement of performance
(Olayinka and Chiemeke, 2019). It also refers as tested machine learning and open source
software which can be accessed via GUI (graphical user interface), Java API and standard
terminal applications. In general, WEKA software is widely used for research purpose, industrial
applications, teaching and more, because it contains a specific plethora of built-in tools that gives
additional transparency in accessing information from well-known toolboxes like scikit-learn,
Deeplearning4j etc.
4

f) What is Cluster Analysis?
Cluster analysis is one of the statistical classification techniques, under which a unique
set of points or objects having similar characteristics are generally grouped together in the form
of clusters, for organising data into meaningful way. It encompasses with a number of algorithms
as well as methods, which are all used to group similar kinds of objects into the respective
categories (Nafie Ali and Mohamed Hamed, 2018). The main purpose cluster analysis behind
organize observed data within useful structures is to help users to gain further insight of
information. Along with this, it also helps users in placing objects into clusters or groups, in
desired way, not defined a priori, like objects to be similar in a given cluster within same or
different sense with each other (Bansal and Srivastava, 2018). Cluster analysis can also be used
to summarize data instead of finding "natural" or "real" clusters; which is also known as
dissection. In this regard, SAS or STAT are some procedures used for clustering, are usually
oriented toward hierarchical or disjoint clusters from distance or coordinate data; a correlation or
covariance matrix etc.
g) What are the most common data mining that can be used in business?
Data mining which in simple terms, known as predictive analysis or knowledge
discovery, defines as a set of techniques to discover different patterns within a large dataset.
Such patterns help users to create a predictive model for staying on the top of future behaviours
(Alghamdi and Alharby, 2019). Today, mostly each and every organisation would like to
capitalise on big data irrespective of their own domain. Therefore, they use sophisticated
analytical methods for extracting meaning information from Big data. For this purpose, a number
of tools of data mining can be used such as explained below – Classification analysis – This tool is used for retrieving relevant and important
information, for classifying data by segmenting them into different groups (Repaso and
Capariño, 2020). Using this data mining tool, analyst can apply algorithm for deciding
the way data can be classified and characterised. Association rule learning: This data mining helps in identifying relationship among
different variables in large dataset, by un-hiding the different patterns. In business
entities, this tool is used to examine and forecast the customer behaviour by building
machine learning programs.
5

 Outlier detection: As per name suggest, this technique refers to observation of some data
items which do not match with particular pattern or expected behaviour (Basheer and et.
al., 2019). In business, this data mining tool can be used within variety of domains like
system health monitoring, fraud and fault detection etc. Clustering analysis - It defines as a process of discovering groups and clusters within a
large dataset in such a manner, that degree of association in two clusters can be the
highest, if they contain similar sense or characteristics (Lee, Kerler and Ivancevich,
2018). Regression analysis: It refers to a process of identifying and determining relationship
between two variables, to evaluate inter-dependency of each other. In business, this tool
can be used to predict and forecast sales performance of business over a period of time.
h) Real-life examples of Data Mining that can be used in business
In business, a number of data mining tools are used for exploring and utilising essential
information to take more effective decisions, for running enterprise successfully. It includes
Artificial Intelligence & Machine learning that helps firms like Amazon in analysing behavioural
trends of marketplace, to categorise the company’s products as per more chances of its sales by
predicting customers’ purchasing behaviour (Anderson and et. al., 2020). AI can also be used to
show the recommendations related to product, by developing better theories. Similarly, usage of
data mining tools in retail and stores indicates another importance of data mining tools, by
following the purchasing behaviour of loyal customers. Along with this, Data mining technique
can be used in the field of educational research for understanding the factors that lead learners to
engage in behaviours that reduce learning and efficiency of them.
i) What is Microsoft Excel?
Mircrosoft excel is a part of Microsoft office suite for various applications, in which user
not only make the data in different format, but also analyse and represent it in various forms.
Using this spreadsheet program that contains tables arraged in various rows and columns, users
can save their data in a well-mannered way, where other people can find out the specific
information in more easy way (Sulianta and Fauzi, 2020). In this regard, today, this tool refers to
be more powerful tool that are utilised in every type of business entities, teaching purpose and so
on, where users can keep information and update it easily. MS-Excel providers a number of tools
like IF, OR, Average, Median and many mathematical or statistical operators, to solve logical
6

and complex problems in simple way, which is not easy to be computed manually. Microsoft
excel contains various row and column, where people can input data and obtain output by using
its tools.
This spreadsheet software mainly developed for Android, iOS, macOS and Windows by
Microsoft which includes features like macro-programming language, pivot tables, graphing
tools, calculations and more (Olayinka and Chiemeke, 2019). MS-Excel extremely beneficial for
business context, where associated people can record income and expenditure information, plan
budgets for running various organisational activities, succinctly present the fiscal results and
more.
Advantages and Disadvantages of WEKA over Excel
In business organisations, strategic planning is one of the most essential function that
requires valid information for making timely and better decisions. For this purpose, to input,
track, analyse and store data, choosing the right software and program is also necessary, that
employers and associated staff to make the best choices to enhance business performance. In this
regard, both WEKA and Excel are the most populous software used in today’s business (Nafie
Ali and Mohamed Hamed, 2018). But over the drawbacks of Excel like Lack of security,
learning ability to learn Syntax of each command requires skill and proper knowledge, etc.
WEKA tool is mostly used in today’s firms. Along with this, the most advantage factor of using
WEKA is its features like AI and machine learning that supports cluster analysis, regression,
classification and others (Schmidt, Church and Riley, 2020). But users can experience some
problems while working on WEKA software if amount of data would exceed the limit. Another
main drawback point of this tool is its usage for teaching and research purpose, therefore, mostly
organisations avoid to use it and do most of the work on Excel software.
7

REFERENCES
Books and Journals
Alghamdi, B. and Alharby, F., 2019. An Intelligent Model for Online Recruitment Fraud
Detection. Journal of Information Security. 10(3). pp.155-176.
Anderson, D. R. and et. al., 2020. Essentials of modern business statistics with Microsoft Excel.
Cengage Learning.
Bansal, A. and Srivastava, S., 2018. Tools Used in Data Analysis: A Comparative
Study. International Journal of Recent Research Aspects. 5(1). pp.15-18.
Basheer, M. Y. I. and et. al., 2019. Predictive analytics of university student intake using
supervised methods. IAES International Journal of Artificial Intelligence. 8(4). p.367.
Lee, L., Kerler, W. and Ivancevich, D., 2018. Beyond Excel: Software Tools and the Accounting
Curriculum. AIS Educator Journal. 13(1). pp.44-61.
Nafie Ali, F. M. and Mohamed Hamed, A. A., 2018. Usage Apriori and clustering algorithms in
WEKA tools to mining dataset of traffic accidents. Journal of Information and
Telecommunication. 2(3). pp.231-245.
Olayinka, T. C. and Chiemeke, S. C., 2019. Predicting Paediatric Malaria Occurrence Using
Classification Algorithm in Data Mining. Journal of Advances in Mathematics and
Computer Science, pp.1-10.
Repaso, J. A. A. and Capariño, E. T., 2020. Analyzing and Predicting Career Specialization
using Classification Techniques. International Journal. 9(1.3).
Schmidt, P. J., Church, K. S. and Riley, J., 2020. Clinging to excel as a security blanket:
investigating accountants' resistance to emerging data analytics technology. Journal of
Emerging Technologies in Accounting. 17(1). pp.33-39.
Sulianta, F. and Fauzi, E., 2020. Different Kinds of Prototyping and Generator Tools to Develop
Analytics Information System. International Journal of Psychosocial
Rehabilitation. 24(2).
8