Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using Specifiers in Tables for Formulas, Excel 2019

0

What is the formula to calculate the Commission Amount if I want to sum the combined total of all the Commission Amounts and Sales Amounts outside the table? (This  is an academic exercise, as I am learning Excel.)  I can sum one column but not 2 columns.    

Also, I have a question about the calculation for the Commission Amount calculation inside the Table:

=[@Sales Amount]*[@[% Commission]]

Why does the one @ appear directly before the name (@Sales Amount) and the other @ has a [ between the @ and the % Commission?

See attached file.

Thanks.

Rob

Answer
Discuss

Answers

0
Selected Answer

Rob

Looks like you're doing the built-in Help example for table calculations.

If you had numbers in regular cells (not in a table), normally you'd calculate each commission (= Sales Amount x commission  %) then just sum all those numbers.

If you don't want to show all those numbers for some reason, then sum of all commissions could be written most simply as:

=C2*D2 + C3*D3 + C4*D4 + C5*D5 + C6*D6 + C7*D7 
etc. That can become very unweildy if you have lots of cells but...

Excel has worksheet function SUMPRODUCT however which multiplies figures from one set of cells (array) with another array (or several arrays) so you get a much shorter equation. The mathematical product per row (say) is calculated (like C2*D2) then all such values are added (the sum). The equation becomes just:

=SUMPRODUCT(D2:D7,E2:E7)

Inside a table, Excel uses a structured formula like the example in your question. I believe that the second term  [% Commission] is enclosed in square brackets (unlike the first) since the % sign is somewhere in the name- if you change the heading to remove the %  (so it's just Commission) and go to F2 to create the structured multiplication, it will now show: 

=[@Sales Amount]*[@Commission]
without the second set of square brackets.

Note that you can use the SUMPRODUCT form outside the table. In your sheet, just paste the equation above into a cell in E9 say and the number will appear.

For an example of multiplying (and summing) three numbers, see the attached file where I've added another column (Sales Quantity) so each individual commission is qty x price x commission (%). In the yellow cell is a SUMPRODUCT calculation (away from the table).

Hope this helps.

Discuss

Discussion

Thanks for selecting my answer Rob. Good luck in your learning- you will be rewarded for your efforts.

Excel is a great program and there's always something new to learn (even after years) 😁
John_Ru (rep: 4312) Dec 16, '20 at 11:01 am
Add to Discussion
0

When you create an Excel table, Excel assigns a name to the table, and to each column header in the table. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. Here's an example of what Excel does:

Instead of using explicit cell references

Excel uses table and column names

=Sum(C2:C7)

=SUM(DeptSales[Sales Amount])]

That combination of table and column names is called a structured reference. The names in structured references adjust whenever you add or remove data from the table.

Structured references also appear when you create a formula outside of an Excel table that references table data. The references can make it easier to locate tables in a large workbook.

Regards,
Jerry

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login