🗂The order of operations used in MS Excel while evaluating formulas

MS Excel follows a standard math protocol to evaluate a formula.

This protocol is called “order of operations” – PEMDAS –

~Parentheses
~Exponents
~Multiplication
~Division
~Addition
~Subtraction

MS Excel also applies some customization to handle the formula syntax.

The order in which MS Excel performs calculations can affect the return value of the formula.

First of all, Excel evaluates any expressions in parentheses.

As we have seen in mathematical formulae too, parentheses essentially override the normal order of operations. It prioritizes certain operations.

Next, Excel resolves cell references like A1 (cell address). It evaluates range references like A1:A10, making them arrays of values.

It also performs range operations like a union (comma) and an intersection (space).

Next, Excel performs –

-Exponentiation
-Negation
-% conversions
-Multiplication and division
-Addition and subtraction
-Concatenation
-Logical operators

We are 10k+ now before the new year 💪

Here is a special channel where you will find FREE Data Analysis Books
👇👇
https://t.me/learndataanalysis

You guys are amazing

Thanks for sharing and supporting the channel ❤️❤️

1. What is the meaning of dropout in Deep Learning?

Dropout is a technique that is used to avoid overfitting a model in Deep Learning. If the dropout value is too low, then it will have minimal effect on learning. If it is too high, then the model can under-learn, thereby, causing lower efficiency.

2. What are sets in Tableau?

Sets are custom fields that define a subset of data based on some conditions. A set can be based on a computed condition, for example, a set may contain customers with sales over a certain threshold. Computed sets update as your data changes. Alternatively, a set can be based on specific data point in your view.

3. What is the difference between DROP and TRUNCATE commands?

DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.

4. What is slicing in Python?

Ans: Slicing is used to access parts of sequences like lists, tuples, and strings. The syntax of slicing is-[start:end:step]. The step can be omitted as well. When we write [start:end] this returns all the elements of the sequence from the start (inclusive) till the end-1 element. If the start or end element is negative i, it means the ith element from the end.

1. What is the difference between the RANK() and DENSE_RANK() functions?

The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.

2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?

One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.

3. Explain the Difference Between Tableau Worksheet, Dashboard, Story, and Workbook in Tableau?

Tableau uses a workbook and sheet file structure, much like Microsoft Excel.
A workbook contains sheets, which can be a worksheet, dashboard, or a story.
A worksheet contains a single view along with shelves, legends, and the Data pane.
A dashboard is a collection of views from multiple worksheets.
A story contains a sequence of worksheets or dashboards that work together to convey information.

4. How can you split a column into 2 or more columns?

You can split a column into 2 or more columns by following the below steps:
1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns. 2. Select the delimiter. 3. Choose the column data format and select the destination you want to display the split. 4. The final output will look like below where the text is split into multiple columns.

5. Do you wanna make your career in Data Science & Analytics but don't know how to start ?

https://t.me/sqlspecialist/94

Here is a complete roadmap from scratch that will make you technically strong enough to crack any Data Analyst and also learn Pro Career Growth Hacks to land on your Dream Job.

1. What do Tableau's sets and groups mean?

Data is grouped using sets and groups according to predefined criteria. The primary distinction between the two is that although a set can have only two options—either in or out—a group can divide the dataset into several groups. A user should decide which group or sets to apply based on the conditions.


3.What do you mean by a Bag of Words (BOW)?

It is used for word frequency or occurrences to train a classifier.
It contains a text representation that describes the frequency with which words appear in a document.
It has two steps:
-A list of terms that are well-known.
-A metric for determining the existence of well-known terms.

3. What are Nested Triggers?

Triggers may implement DML by using INSERT, UPDATE, and DELETE statements. These triggers that contain DML and find other triggers for data modification are called Nested Triggers.

4. What is a True positive rate and a false positive rate?

True positive rate or Recall: It gives us the percentage of the true positives captured by the model out of all the Actual Positive class.

TPR = TP/ (TP+FN)

False Positive rate: It gives us the percentage of all the false positives by my model prediction from the all Actual Negative class.

FPR = FP/(FP+TN)

1. What are the uses of using RNN in NLP?
The RNN is a stateful neural network, which means that it not only retains information from the previous layer but also from the previous pass. Thus, this neuron is said to have connections between passes, and through time.
For the RNN the order of the input matters due to being stateful. The same words with different orders will yield different outputs.
RNN can be used for unsegmented, connected applications such as handwriting recognition or speech recognition.

2. How to remove values to a python array?
Ans: Array elements can be removed using pop() or remove() method. The difference between these two functions is that the former returns the deleted value whereas the latter does not.

3. What are the advantages and disadvantages of views in the database?
Answer: Advantages of Views:
As there is no physical location where the data in the view is stored, it generates output without wasting resources.
Data access is restricted as it does not allow commands like insertion, updation, and deletion.
Disadvantages of Views:
The view becomes irrelevant if we drop a table related to that view.
Much memory space is occupied when the view is created for large tables.

4. How to create a calculated field in Tableau?
Click the drop down to the right of Dimensions on the Data pane and select “Create > Calculated Field” to open the calculation editor.
Name the new field and create a formula.

1. What is the case when in SQL Server?

The CASE statement is used to construct logic in which one column’s value is determined by the values of other columns.
At least one set of WHEN and THEN commands makes up the SQL Server CASE Statement. The condition to be tested is specified by the WHEN statement. If the WHEN condition returns TRUE, the THEN sentence explains what to do.

When none of the WHEN conditions return true, the ELSE statement is executed. The END keyword brings the CASE statement to a close.


2. What is a relationship in SQL and what are they?

Database Relationship is defined as the connection between the tables in a database. There are various data base relationships, and they are as follows:.

One to One Relationship.

One to Many Relationship.

Many to One Relationship.

Self-Referencing Relationship.

3. What is the use of cycle fields in tableau?

Cycle fields help in switching and trying different colour combinations or views in a cyclic order. It will work only if we have a chart that allows more than one measure such as stacked bar chart and we are unable to finalize the visualizations then we can use cycle fields. To use cycle field, click on analysis menu in the toolbar then select cycle fields to take a quick look at an alternative visualization.

4. What is the difference between a function and a formula in Excel?

A formula is a user-defined expression that calculates a value. A function is pre-defined built-in operation that can take the specified number of arguments. A user can create formulas that can be complex and can have multiple functions in it. For example, =A1+A2 is a formula and =SUM(A1:A10) is a function.

Q1. What are sets and groups in Tableau?

Sets and groups are used group data based on some specific conditions. The main difference between these two is that a group can divide the dataset into multiple groups whereas a set can have only two options which is either in or out. A user should choose to apply group or sets based on the requirements.

Q2. What is Power Pivot & Power Query?

Power Pivot is an add-on provided by Microsoft for Excel since 2010. Power Pivot was designed to extend the analytical capabilities and services of Microsoft Excel.

Power Query is a business intelligence tool designed by Microsoft for Excel. Power Query allows you to import data from various data sources and will enable you to clean, transform and reshape your data as per the requirements. Power Query allows you to write your query once and then run it with a simple refresh.


Q3. State some ways to improve the performance of Tableau?

Use an Extract to make workbooks run faster
Reduce the scope of data to decrease the volume of data
Reduce the number of marks on the view to avoid information overload
Try to use integers or Booleans in calculations as they are much faster than strings
Hide unused fields
Use Context filters
Reduce filter usage and use some alternative way to achieve the same result
Use indexing in tables and use the same fields for filtering
Remove unnecessary calculations and sheets.

Q4. What is macro in excel?

Macro refers to an algorithm or a set of actions that help automate a task in Excel by recording and playing back the steps taken to complete that task. Once the steps are stored, you create a Macro, and it can be edited and played back as many times as the user wants.

Macro is great for repetitive tasks and also eliminates errors. For example, suppose an account manager has to share reports regarding the company employees for non-payment of dues. In that case, it can be automated using a Macro and doing minor changes every month, as needed.

1. What data sources can Power BI connect to?

Ans: The list of data sources for Power BI is extensive, but it can be grouped into the following:
Files: Data can be imported from Excel (.xlsx, xlxm), Power BI Desktop files (.pbix) and Comma Separated Value (.csv).
Content Packs: It is a collection of related documents or files that are stored as a group. In Power BI, there are two types of content packs, firstly those from services providers like Google Analytics, Marketo, or Salesforce, and secondly those created and shared by other users in your organization.
Connectors to databases and other datasets such as Azure SQL, Database and SQL, Server Analysis Services tabular data, etc.


2. What are the different integrity rules present in the DBMS?

The different integrity rules present in DBMS are as follows:
Entity Integrity: This rule states that the value of the primary key can never be NULL. So, all the tuples in the column identified as the primary key should have a value.
Referential Integrity: This rule states that either the value of the foreign key is NULL or it should be the primary key of any other relation.


3. What are some common clauses used with SELECT query in SQL?

Some common SQL clauses used in conjuction with a SELECT query are as follows:
WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).
GROUP BY clause in SQL is used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.


4. What is the difference between count, counta, and countblank in Excel?

The count function is very often used in Excel. Here, let’s look at the difference between count, and it’s variants - counta and countblank.

1. COUNT
It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted.

2. COUNTA
It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted.

3. COUNTBLANK
As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into consideration.

1. What is the AdaBoost Algorithm?
AdaBoost also called Adaptive Boosting is a technique in Machine Learning used as an Ensemble Method. The most common algorithm used with AdaBoost is decision trees with one level that means with Decision trees with only 1 split. These trees are also called Decision Stumps. What this algorithm does is that it builds a model and gives equal weights to all the data points. It then assigns higher weights to points that are wrongly classified. Now all the points which have higher weights are given more importance in the next model. It will keep training models until and unless a lower error is received.

2. What is the Sliding Window method for Time Series Forecasting?
Time series can be phrased as supervised learning. Given a sequence of numbers for a time series dataset, we can restructure the data to look like a supervised learning problem.
In the sliding window method, the previous time steps can be used as input variables, and the next time steps can be used as the output variable.
In statistics and time series analysis, this is called a lag or lag method. The number of previous time steps is called the window width or size of the lag. This sliding window is the basis for how we can turn any time series dataset into a supervised learning problem.


3. What do you understand by sub-queries in SQL?
A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called as the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,< or =.

4. Explain the Difference Between Tableau Worksheet, Dashboard, Story, and Workbook?
Tableau uses a workbook and sheet file structure, much like Microsoft Excel.
A workbook contains sheets, which can be a worksheet, dashboard, or a story.
A worksheet contains a single view along with shelves, legends, and the Data pane.
A dashboard is a collection of views from multiple worksheets.
A story contains a sequence of worksheets or dashboards that work together to convey information.

If you are new to this channel then don't forget to Join and get regular Important Updates related to Data Analytics Domain.

1.Define RDBMS.

Answer: Relational Database Management System(RDBMS) is based on a relational model of data that is stored in databases in separate tables and they are related to the use of a common column. Data can be accessed easily from the relational database using Structured Query Language (SQL).

2.Define DML Compiler.

Answer: DML compiler translates DML statements in a query language into a low-level instruction and the generated instruction can be understood by Query Evaluation Engine.

3.Explain the terms ‘Record’, ‘Field’ and ‘Table’ in terms of database.

Answer
Record: Record is a collection of values or fields of a specific entity. For Example, An employee, Salary account, etc.

Field: A field refers to an area within a record that is reserved for specific data. For Example, Employee ID.

Table: Table is the collection of records of specific types. For Example, the Employee table is a collection of records related to all the employees.

4.Define the relationship between ‘View’ and ‘Data Independence’.

Answer: View is a virtual table that does not have its data on its own rather the data is defined from one or more underlying base tables.

Views account for logical data independence as the growth and restructuring of base tables are not reflected in views.

1. What is the Difference Between a Shallow Copy and Deep Copy in python?

Deepcopy creates a different object and populates it with the child objects of the original object. Therefore, changes in the original object are not reflected in the copy. copy.deepcopy() creates a Deep Copy. Shallow copy creates a different object and populates it with the references of the child objects within the original object. Therefore, changes in the original object are reflected in the copy. copy.copy creates a Shallow Copy.

2. How can you remove duplicate values in a range of cells?

1. To delete duplicate values in a column, select the highlighted cells, and press the delete button. After deleting the values, go to the ‘Conditional Formatting’ option present in the Home tab. Choose ‘Clear Rules’ to remove the rules from the sheet. 2. You can also delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in the Data tab.

3. Define shelves and sets in Tableau?

Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data.
Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.

1. What are the ways to detect outliers?

Outliers are detected using two methods:

Box Plot Method: According to this method, the value is considered an outlier if it exceeds or falls below 1.5*IQR (interquartile range), that is, if it lies above the top quartile (Q3) or below the bottom quartile (Q1).

Standard Deviation Method: According to this method, an outlier is defined as a value that is greater or lower than the mean ± (3*standard deviation).


2. What is a Recursive Stored Procedure?

A stored procedure that calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required.


3. What is the shortcut to add a filter to a table in EXCEL?

The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.

4. What is DAX in Power BI?

DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.

1. What is concurrency control in DBMS?
This is a process of managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control:
Optimistic approach – Involves versioning
Pessimistic approach – Involves locking

2. What is a checkpoint in DBMS and when does it occur?
A checkpoint is a mechanism where all the previous logs are removed from the system and are permanently stored on the storage disk. So, basically, checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of crash.

3. What are groups in Tableau?
A group is a combination of dimension members that make higher level categories. For example, if you are working with a view that shows average test scores by major, you may want to group certain majors together to create major categories.

4. How are nested IF statements used in Excel?
The function IF() can be nested when we have multiple conditions to meet. The FALSE value in the first IF function is replaced by another IF function to make a further test.

1. What is the lambda function in Python?
Python Lambda Functions are anonymous function means that the function is without a name. As we already know that the def keyword is used to define a normal function in Python. Similarly, the lambda keyword is used to define an anonymous function in Python.
Eg. lambda_cube = lambda y: y*y*y

2. What is the difference between SQL and MySQL?
SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way.

3. What are Filters in Power BI?
The term "Filter" is self-explanatory. Filters are mathematical and logical conditions applied to data to filter out essential information in rows and columns. The following are the variety of filters available in Power BI:
👉 Manual filters
👉 Auto filters
👉 Include/Exclude filters
👉 Drill-down filters
👉 Cross Drill filters

1. Does SQL support programming language features?
It is true that SQL is a language, but it does not support programming as it is not a programming language, it is a command language. We do not have some programming concepts in SQL like for loops or while loop, we only have commands which we can use to query, update, delete, etc. data in the database. SQL allows us to manipulate data in a database.

2. What is a trigger?
Trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL.

3. What are aggregate and scalar functions?
For doing operations on data SQL has many built-in functions, they are categorized into two categories and further sub-categorized into seven different functions under each category. The categories are:
Aggregate functions:
These functions are used to do operations from the values of the column and a single value is returned.
Scalar functions:
These functions are based on user input, these too return a single value.

4. Define SQL Order by the statement?
The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.
By default ORDER BY sorts the data in ascending order.
We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

5. What is the difference between primary key and unique constraints? 
The primary key cannot have NULL values, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constraints. The primary key creates the clustered index automatically but the unique key does not.