Forum Discussion
Commission Calculation totals
I should just confirm that your output table is an Excel Table (with or without the jolly stripes). The fact that your field names comprise multiple words makes the syntax slightly more complex, but it should come up correctly simply by clicking the target cell.
= LOOKUP( 1, 1 / ( tblC3[Revenue Type] = [@[Revenue Type]]) / ( tblC3[Outlet] = [@Outlet]), tblC3[Total part] * [@[Total spend]] * tblC3[Commission %] )The thing to note that if an additional square bracket appears, it should have a corresponding closing bracket.
Formula could be as
=XLOOKUP(
1,
( tblC[Revenue Type] = [@[Revenue Type]] ) *
( tblC[Outlet] = [@Outlet] ),
tblC[Total part] * [@[Total Spend]] * tblC[Comission %] )where tblC is the table with Commission parameters
I'd recommend to use structured tables, not ranges.
Please see in attached.
I am trying to type it in but the outlet, total part, and commission isn't taking. What am i doing wrong?
The objects you mention are the Headers applied to columns of the Table, which provide the basis for Structured References. The shortcut for creating a structured reference to a field is to hover over the top border of the header and click when the black down-arrow shows.
If this is not working, the first question must be "have you converted the array to a Table (Ctrl/T)?"
If that is OK, have you named the Table (Table Design ribbon tab and the Table Name appear at the extreme left)?
This is all way over my head lol. I have no idea what I am doing and I have tried looking at videos but it's not working for me.
Have you downloaded Sergei's workbook. Is that working as you require? I note it uses XLOOKUP so requires Excel 365 or 2021. Without that, the formula could be changed to
=LOOKUP( 1, 1 / ( tblC[Revenue Type] = [@[Revenue Type]] ) / ( tblC[Outlet] = [@Outlet] ), tblC[Total part] * [@[Total Spend]] * tblC[Comission %] )since the very old LOOKUP function ignores the #DIV0! errors created by the formula.
Meanwhile use F1 to bring up help and search for structured references. It is worth the effort to learn about structured references because they offer a far more precise way of referencing data.