The introduction of LET function is fantastic.

To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.

Look at this problem statement:

We need to know how many customers will get an incentive, based on meeting all following conditions:

a. Either of the Product Goals has to be met
b. Achievement has to be more than 100%
c. Should have been with company for > 2 years


As a user you get more control over the way you open Excel.

Go to Command Prompt

Excel does not have an inbuilt function to count errors.

ISERROR will return an array of TRUE / FALSE.
Unary (double negative) will coerce that into 1 / 0.

Finally we count the number of 1.


The formula will extract numbers from text.

Formula:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {“0”,”1",”2",”3",”4",”5",”6",”7",”8",”9"}, “”)))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER( — MID(A2,ROW(INDIRECT(“$1:$”&LEN(A2))),1))* ROW(INDIRECT(“$1:$”&LEN(A2))),0), ROW(INDIRECT(“$1:$”&LEN(A2))))+1,1) * 10^ROW(INDIRECT(“$1:$”&LEN(A2)))/10),””)

Another option will be to use VBA Code


Excel is fantastic. But you are really missing out if you do not use Power Query and Power Pivot.

Power Query is inbuilt in Excel under Data > Get and Transform Data.
https://powerquery.microsoft.com/en-us/

It is an ETL tool that can connect to hundred of data sources, has 300+ transformations and…


Let us start with some numbers:

Numerous sources online, including authoritative business websites like Business Insider and the Harvard Business Review indicate that Microsoft Office has over 750 million active monthly users.

The over 750 million users is the official figure shared online in 2017 by Microsoft’s CEO Satya Nadella


Pivot Tables are fantastic — and are such an integral part of our spreadsheet workflow that we do not even think about how powerful it is.

GETPIVOTDATA() allows you us to access values from the Pivot Table. But you still cannot modify the Pivot Table.

It would be great if…


Dynamic arrays work slightly differently in Excel and Google Sheets.

Excel takes care of it automatically.

Read here:
https://support.microsoft.com/en-us/office/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2

Google Sheets requires =ARRAYFORMULA


Visual Basic Applications (VBA) is the programming language of Excel and other Office programs.

Most of us have used it at some point or at the very least heard of it. …

HAVISH MADHVAPATY

Founder @ Havish M Consulting | 40u40 [Analytics Insight] | AuthorX20 | MOS | MCDA | MCT | Taught at IIM ABCLK

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store