Few things in life are constant. Heraclitus said: The only thing constant is change. Change is constant and variables are used to capture change. In the context of Excel, the names of workbooks change, the number of workbooks you have open is variable, the number of sheets, pivot tables and charts is usually variable, the name of sheets is variable, the values in cells are variable, and on, and on.
Continue readingPEMDAS and the Mathematical Order of Operations with Microsoft Excel
Microsoft Excel works similarly to using a calculator but there are important differences. On a calculator to perform a calculation you might type numbers, then math symbols like plus (+) or minus (-) and then you would press the equal key to complete your calculation.In Excel we don’t end with the equal sign, we begin with the equal sign. Additionally, you need to be aware of the mathematical order of operations, commonly associated with the acronym PEMDAS (Please Excuse My Dear Aunt Sally).
What the order of operations theorem states is that parenthesis () overrides exponentiation (^) which has a higher precedence than multiplication (*) or division (/) and that multiplication and division have a higher order of precedence than addition (+) and subtraction (-). For those of us who don’t have degrees in rocket science, exponentiation is the term used to say raise one number to the power of another number, like 28 which would equal 256 (2*2 eight times).
To see how PEMDAS applies to real world examples let’s look at two formulas:
Example of PEMDAS:
= 2 + 3 * 4
Most people would read that formula from left to right and calculate 2+3 equals 5 and 5 times 4 equals 20, and it wouldn’t be entirely wrong. However, because multiplication has a higher order of precedence than addition, Excel will first calculate 3*4 equals 12 and 12+2 equals 14.
Therefore, if you want the formula to produce 20 instead of 14 you need to override the order of operations by grouping the 2+3 terms inside parentheses as in:
= (2 + 3) * 4
In Excel formulas it is common to see many many parentheses. That is because most functions (predefined formulas) use parentheses to enclose the arguments (inputs) and those arguments may nest other functions in parentheses and each function may require parenthesis around the inputs. For example,
=IF(D2=”R”,INDEX($K$3:$K$8,MATCH(B2,$J$3:$J$8,FALSE)*C2),IF(D2=”C”,INDEX($H$3:$H$8,MATCH(B2,$G$3:$G$8,FALSE)*C2),IF(D2=”H”,INDEX($N$3:$N$8,MATCH(B2,$M$3:$M$8,FALSE)*C2))))
What is important to realize about using parentheses is that if you use () symbols you have to be careful to balance every open parentheses with a close parenthesis or the formula will produce an error. If you’re unsure of why this formula uses $ signs in the cell references, please refer back to our blog about absolute versus relative cell references.
To learn more about our Excel Training in New York City contact us here.
Excel Class Launches Online VBA Training
Excel Class receives requests for Microsoft Excel VBA training courses from every corner of the country. We are pleased to announce that our new exclusive series: The Excel VBA webinar is now available online.
Our training sessions begin once a month at different start times. Each session lasts two hours and includes no less than 30 minutes of homework to practice between sessions. In total the classes run for 12 weeks. Since we realize you may not be able to attend 12 consecutive weeks we allow you to retake the class an unlimited number of times and you can start in one class and skip into others classes (by providing us with advance notice). We offer exclusive video libraries where students can log in to a secured area to review videos of your class and download example workbooks.
Though our New York class runs for 28 hours, our webinar is spread across 48 hours to allow more time to review homework, provide time for questions and answers, and to go into further detail.
Excel Class VBA Macro Adds IfError Functions To Every Formula
Excel Class VBA Macro Adds IfError Functions To Every Formula
Today a student asked Excel Class if there was a macro to automatically add an IfError function to every formula on every sheet in a selected workbook, assuming there was not already an IfError function used.
On short notice the Excel Class instructor provided the following code which we wanted to share with our readers.
Remember that macros have no Undo command. Once you run this VBA macro the only undo is to close the file without saving changes. The macro leaves a selected workbook open so you can review the changes before deciding to save or not.
To use this code, press the Alt+F11 key to open the VB Editor.
Select the Insert menu > Module command.
Then paste the following code into the empty window:
To run the program, press the F5 key, or from Excel select the Ribbon View Tab > Macros button and run the AddIfError macro.
Public Sub AddIffError()
‘purpose: IF there is a formula add iferror
‘if there’s an iferror already, ignore the formula
‘process all sheets in wb
‘Handle Errors
On Error GoTo Errorhandler
‘Step 10: Ask user to open a wb
MsgBox “Please click OK to select a workbook to process”
‘Step20: Prompt user to open wb to process
With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With
‘Step30: On every sheet add iferror
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then ws.Select
‘Select all cells
Range(“A1”).Select
ActiveCell.CurrentRegion.Select
Dim r As Range
For Each r In Selection
If Left(r.Formula, 1) = “=” Then
If Left(r.Formula, 8) = “=IFERROR” Then
‘Do Nothing
Else
Dim myformula
myformula = Right(r.Formula, Len(r.Formula) – 1)
r = “=IfError(” & myformula & “,0)”
End If
End If
Next r
Next ws
Exit Sub
Errorhandler:
If Err.Number <> 0 Then
MsgBox “Something went wrong”
End If
End Sub
If you want to learn more about Excel VBA programming check out our 2-day Excel VBA course in New York City.
Data Tables And Interactive Charts
The Excel Class blog provides an opportunity to supplement the material we teach in our class. This tutorial assumes our readers are familiar with named ranges. (See: Advanced Excel Class)
Excel’s data table feature is a what-if analysis tool that allows users to create tables (rows/columns) of results based on a one or two variable formula. Data tables are useful for simple calculations like future value tables or mortgage payment tables to examine different scenarios based on varying input assumptions. Our previous post examined array formulas with vlookup (Vlookup Array Formula Tutorial) and data tables are an extension of the discussion of array formulas.
Download the finished workbook here
In the following example we use a data table to show rates of return for an investment and then use the data table as the basis of an interactive chart using an offset function in an array formula.
The formula for Future Value is:
FV = PV * (1 + i) ^ t
Where:
FV means future value
PV means present value
i is the interest rate
t is time
To Create A Data Table
1. Set up variable inputs, optionally (recommended) name the cells to use in the formula;
2. To the right of the PV label, in cell B1, type a number like 1. In this example 1 means $1, our present value.
3. In a separate cell, create the formula that references the named ranges. In this example the formula appears in cell C4 which will become the upper left corner of the data table;
4. To the right and below the formula, add variable inputs;
5. Select the range to use for the data table, including the formula in the upper left corner;
In this example select cells C4:K24;
6. On the ribbon, select the Data tab > What If Analysis > Data Table command;
Select a row input cell (refer to a cell with a name); In this example, cell B2 is the row input (interest).
Select a column input cell (refer to a cell with a name); In this example, cell B3 is the column input (time).
Click the OK button to generate the data table.
Note: The formulas produced by the data table in cells D5:K24 are array formulas. Be aware that array formulas remove the ability to insert or delete a row or a column inside the data table.
To Create An Interactive Chart Based On The Data Table
1. In cell C26, type a 0 (zero);
2. Select (highlight) cells D26:K26;
3. Type an = (equals) sign;
Enter the following formula:
=OFFSET(D5:K5,C26,0)
To create an array formula, hold the Ctrl+Shift keys and press the Enter key.
The offset function offsets the range D5:K5 by the value in cell C26 (zero rows)
4. To add an interactive spinner control, show the Developer tab of the ribbon. To show the Developer tab, right click any tab of the ribbon, choose the Customize Ribbon… command. In the Customize Excel Options dialog, check the Developer tab.
5. On the Developer tab of the ribbon, select the Insert > Form Controls > Spinner command.
6. Click and drag a small spinner control in a cell to the left of cell C26.
7. Right click the spinner control, left click the Format Control command.
In the Format Control dialog:
Set the minimum value to 0
Set the maximum value to 20 (the number of years in column C)
Set the link cell to cell C26
8. The final step is to create the interactive chart.
Select cells D26:K26
On the ribbon, select the Insert tab > 2D Line Chart command
9. To see the chart become interactive, click the up arrow on the spinner control to increase the value in cell C26.
Note that as the value increases the offset function plots different rows of the data table in the chart.
Final note: I am obligated to give credit to Tushar Mehta for his excellent charting tutorials. Learn more about Tushar’s chart tutorials here: http://tushar-mehta.com/excel/charts/ In fact, I always encourage Excel students to study Tushar’s tutorials on: 3D Surface Charts, Normal Distribution (Bell Curves), Waterfall (Cash flows), and Values That Differ By Magnitude.
SumProduct Function Applications
SumProduct Function Applications
When teaching Excel classes students occasionally ask about the SumProduct function. There are many aspects of SumProduct that mirror the SumIfs functions capabilities but SumProduct can produce different answers when combined with the unary operator. This tutorial is not meant to be a thorough discussion of SumProduct but is meant to provide an addendum to class notes.
Click Here To Download A Sample Workbook
I have provided a picture of the worksheet for those who cannot open Excel workbooks:
SumProduct – Example 1
The SumProduct function multiplies one array by another array. An array is typically a range of cells in a single column. In the first example imagine that our goal is to calculate the total gross pay for all employees in all divisions and departments. The ‘old fashioned’ Excel approach is to write a formula to calculate hours * rate for each employee (cells G2:G21) then Sum those values. In this Level 1 example, we would need to generate 20 formulas in 20 cells to find the answer, where a SumProduct function calculates the result with less effort.
With SumProduct
=SUMPRODUCT(E:E,F:F)
Without SumProduct
=(E2*F2)+(E3*F3)+(E4*F4)+(E5*F5)+(E6*F6)+(E7*F7)+(E8*F8)+(E9*F9)+(E10*F10)+(E11*F11)+(E12*F12)+(E13*F13)+(E14*F14)+(E15*F15)+(E16*F16)+(E17*F17)+(E18*F18)+(E19*F19)+(E20*F20)+(E21*F21)
SumProduct – Example 2
About fifteen years ago Excel did not offer a SumIfs function and SumProduct was an alternative to using an array formula. Like the SumIfs function, SumProduct can work conditionally using criteria. Where SumIfs only allows AND criteria, SumProduct also supports OR criteria (but this generally double counts and generates incorrect results). Several studies have shown that SumIfs functions calculate significantly faster than SumProduct functions.
In example 2 imagine that we want to only sum the gross pay if the division equals Maine and the department equals Sales. In this example the asterisk (*) between Array1 and Array2 operates as an AND operator. The formula reads, If column C contains “Maine” AND Column D contains “Sales”, then Sum the value in column E times column F. If we use a plus (+) instead of an asterisk, this represents an OR criteria but double counts the products of Maine and Sales.
With SumProduct
=SUMPRODUCT((C:C=”Maine”)*(D:D=”Sales”),E:E,F:F)
With SumIfs
=SUMIFS(G:G,C:C,”Maine”,D:D,”Sales”)
Array Formula Sum If
{=SUM(IF((C:C=”Maine”)*(D:D=”Sales”),E:E*F:F))}
SumProduct – Example 3
At times we see a double hyphen (- -) in a formula. In Excel the double hyphen is known as a unary operator. The purpose of the unary operator is to force text values to 0/1 values where zero means false and one means true.
SumProduct Formula
=SUMPRODUCT(–(C:C=”Maine”)*–(D:D=”Sales”),E:E,F:F)
Like example 2 except now the – operator converts values where a cell contains Maine or Sales to 0 for false and 1 for true, then uses the corresponding 1 values to determine which values in columns E and F are multiplied and summed.
SumProduct is also used to compare values in two columns to count how many words or numbers match (typically columns in separate workbooks). In general, do not sort the data, this technique compares corresponding values within the same row.
SumProduct Formula
=SUMPRODUCT(–($C:$C=”Maine”),–($I:$I=”Maine”))
This formula asks Excel to compare the values in column C to the values in column I for the word Maine. The unary operator converts results to 0 for false or 1 for true, and then the SumProduct function sums the values for 1/true.
SumProduct can also compare the value in two columns to count identical values
SumProduct Formula
=SUMPRODUCT(–(E:E=40),–(K:K=40))
Like the previous example, this formula asks SumProduct to sum the 1 values if the numbers in column E and K are identical and equal 40.
SumProduct – Example 4
The most valuable use of the SumProduct is to calculate a weighted average (this question only occurs about once every ten years). The straight average sums the values and divides by the count. A weighted average considers some values have a higher weight. In this example imagine the weighted average is based on the count of Divisions, where there are six Connecticut values, four Maine values, and five New Hampshire and five Vermont values.
In this example the SumProduct weights the average gross pay based on the count of divisions.
SumProduct Formula – Longhand notation for weighted average
=SUMPRODUCT({780,780,860,504,589,504,350,213,860,589,472.15,176,860,880,443.75,880,472.15,780,840,350},{4,6,4,6,5,6,5,6,5,6,5,5,5,5,5,5,6,4,4,5})/SUM(H2:H21)
SumProduct Formula – Shorthand notation for weighted average
=SUMPRODUCT(E2:E21,F2:F21,H2:H21)/SUM(H2:H21)
Two Criteria Lookups
Two Criteria Lookups
In the process of teaching Excel over the past 20 years I have found that most students are often shocked and unaware that Vlookup and Index/Match can only locate the first item in a list. They find this unacceptable and I would tend to agree. When a value repeats in many rows, Microsoft suggest you use the upcoming Xlookup function to locate what is known as the ‘nth’ occurrence, but most Office365 installations will not offer that feature before July 2020 – at the earliest! For a long time there has been a workaround that does not require Xlookup, which despite Microsoft’s best intentions likely is not necessary!
In the “real world” a list typically includes items that repeat. Consider an invoice, it will typically include many line items for a single invoice number. Where one column may contain a repeating invoice number, the line items in rows could be for the same product with different size or color options. I recently came across several clients who require this functionality. While the topic of this blog post is beyond most students needs, I include it here because the class notes have finite space. This post assumes that students have taken the advanced Excel class and already understand how to work with Index and Match functions.
To follow along with the example, I recommend you download this sample workbook: Index Match Two Criteria
In this example notice that column A contains invoice numbers that repeat (orange cells – first criteria), and column B shows a unique price (second criteria). The goal is to locate the correct quantity for each invoiced item based on its price.
The formula required in cell C2 is:
=INDEX(DataToSearch!$F$1:$F$5061,MATCH(1,(InvoiceTwoCriteria!A2=DataToSearch!$B$1:$B$5061)*(InvoiceTwoCriteria!B2=DataToSearch!$E$1:$E$5061),0))
Given the formula’s complexity, I also recommend simplifying the readability by using named ranges, which could read:
=INDEX(Quantity,MATCH(1,(InvoiceTwoCriteria!A2=InvoiceNumber)*(InvoiceTwoCriteria!B2=Price),0))
After copying one of these formulas, take a moment to study and read it but… do not simply press the enter key, this formula requires the use of an array – hold both Ctrl+Shift while pressing the Enter key. As you study the format the formula, pay special attention to the placement of the open and close parentheses!
Because it is any array formula, we cannot reference an entire column, it requires a fixed range with absolute references (recall that named ranges are also absolute references). Also bear in mind that array formulas have limitations that can be found at this URL: https://docs.microsoft.com/en-us/office/troubleshoot/excel/arrays-limitations
The way to read and interpret the formula is:
Index (return) a value from column F. The Match lookup value of 1 creates a matrix of 1/0 values. The Match lookup array for criteria one reads: Look for the value in cell A2 (invoice #1) by searching cells B1 to B5061. The multiplication symbol before the second lookup array is treated like an AND operator. The second lookup array reads: find the price in cell B2 (34.95) by searching cells E1 to E5061. When the matrix returns a 1 match (true) for the invoice in column B, and a 1 match (true) for the price in column e, then return the corresponding value from column F (price). And if some of you are wondering, yes, this can be extended to include three criteria if needed!
If you would like to practice creating these types of formulas, download the following workbook and enable macros. Note that the submit answer feature is not expecting you use named ranges, though not incorrect the program is looking for range references, not named ranges! Index Match Two Criteria Lab
Excel Class Training NYC Announces Spring Excel Calendar
Excel Class Training NYC Announces Spring Excel Calendar for Introduction To Excel class dates, Intermediate – Advanced Excel class dates, and Excel VBA class dates
Continue readingExcel Class NYC – Free Excel Training Tutorial – Excel Text Functions
Excel Class nyc offers a free excel training blog tutorial post. This Excel Class Blog is part I of a three part series of Excel training about text functions.
Continue readingExcel VBA Class Announces East Coast Schedule For Q1 2011
Excel Class is pleased to announce our Q1 2011 Excel VBA Classes schedule.
Philadelphia, PA
February 16th – 18th – Register Here
Boston, MA
February 23rd – 25th – Register Here
Baltimore, MD
March 1st – 3rd – Register Here
Miami, FL
March 7th – 9th – Register Here
Atlanta, GA
March 16th – 18th – Register Here
Tampa, FL
March 28th – 30th – Register Here