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.
It wont let me even highlight the cell that I need to high light. Is there any way I can email this to you? I can't figure out how to attach it to this forum. Unless you can help me with that, then I will e happy to do that as well.
Create and format tables (microsoft.com)
I had a quick search round but have yet to identify the perfect video for you. The Microsoft one gives a quick view on creating tables but from there on you will be reading. There must be something better out there!
Other things to search for are 'Structured References' since it is they that make table formulas readable.
Added: Found a recording by Mike Girvin
Excel Basics 15: Excel Table Feature & Dynamic Ranges for VLOOKUP, PivotTables, & more! - YouTube
- Thank you! I have no idea what I am doing. Is there anything you think I can watch that will help me get better at this?
I really appreciate you Peter! - It was simply a case of recognising that the user input array was not an Excel Table. Ctrl/T and a bit of tidying up allowed me to select the Type and Outlet fields and have Excel return the structured references that propagated down the table.
The formulas aren't sacred, play with them, get used to Tables and settle on something that suits your way of working. - OMG YOU ARE AMAZING! What happened, how did you change it?
Thank you!