Calculated field formulas

[trosasco23]

321 Loans Commissions Tracker.xlsx

Good Morning,

I'm working on a pivot table to track commissions for our reps. The commissions are based off this formula:

=LOOKUP(D4,{0,20,30,35,40,41},{0,25,35,45,75,85})

This formula is currently on the LIST tab on column E. (I honestly don't know if this particular list is even necessary so if u think I can delete it, I will)

The COMM PER DEAL column is not adding up (in the COMMISSION PER REP tab) as it should..For example, the rep called Chad Wilson has 22 deals so he should have $25.00 commission.

Can someone help me?

[Sam Capricci]

maybe adjust with this change? =LOOKUP(D10,{0,1,20,30,35,40,41},{0,25,35,45,75,85})

[trosasco23]

The formula I had works fine.. the problem is that I cant add it to a calculated field in the pivot table.

[hoyasaxa215]

What you want is a VLOOKUP function, which you can't calculate within the pivot table, so you'll just need to manually add the columns (highlighted in green).

[trosasco23]

Is there any way to create some type of formula that will calculate the same automatically? This manual one wont do. My boss wont accept it.

[hoyasaxa215]

Well, you can't enter formulas into pivot tables, so I'm not exactly sure what your boss wants you to do. You could add columns to your INPUT worksheet that keep a running total of deals per broker and then include that in the pivot table, but it'd be more complicated if you want to parse the data by month. You could create a separate table that used =COUNTIF or =SUMIF, but that wouldn't be a pivot table. I'd explain that you can't enter formulas to your boss and ask what he/she would like you to do.

[trosasco23]

Well as far as i know you are able to enter columns in the calculated field to update automatically. I have done it before. I just dont know how to enter this particular formula.

[FDibbins]

Maybe add the calc to your data, then pull that column into the PT?

[hoyasaxa215]

Yes, pivot tables can perform various calculations. However, you want to incorporate data from a table that's on a separate worksheet and that really isn't a calculation. It's simply a lookup. So, I'm not sure what options you have other than performing the running calculation on the INPUT worksheet and including it in your pivot table.

[trosasco23]

I already have the formula entered as a lookup in the INPUT tab. Its the second to last one on the right of the INPUT tab.. But since my calculations do their work per deal it always returns a zero. I guess because its counting the "cleared sales" or "deals" per entry. Right now it would only return the first $25 is there was a 20 in the AJ column...

[Loganeb]

trosasco,

I am having issues understanding your logic to determine people's sales. I filtered your actual data for Chad Wilson (Column D), Month of Pay Date (Column W = Oct), and Cleared Count (Column AJ = 1). Doing so, I see that Chad had 54 total rows of information. Am I using the wrong data to calculate his deals? I think I have a solution for you, but I need to understand exactly how you are going to sum your data first, to check against.

[trosasco23]

Before anything, thank you. In the LIST Tab there is a list of commissions and how they are to be paid. Our reps get paid based on a tier as you will see on the Commissions list. They only get paid commissions if the client has made their first payment and it has cleared. So in Chad's case, he has 22 cleared deals. The tier states if the rep has from 0-19 deals = there is no commission, 20-29=$25 each deal, 30-34=$35, 35-39=$45, 40 deals=$45 and anything over 41 deals cleared=$85...The reps get paid commissions monthly so that's why we needed to do the Month of pay date column.

My formula in the INPUT tab on column AK (commissions per deal) is calculated depending if that certain deal cleared or not. If there is a 1 on the cleared count (column AJ) then it does a vlookup from the list on the LIST tab. The problem is that the formula doesn't work because it always only returns a 1 or 0.

[Loganeb]

No matter how I filter the list, I am not getting 22 for Chad Wilson. I filtered by date of sale, Rep Name and Cleared? columns. Over 2 months, it has 24 rows of data for Chad. I assume that's 24 clears on payment? If you could, upload the file with the data filtered to show only the 22 for Chad, so I can understand what columns you're filtering on please.

[trosasco23]

Please look at the tab called COMMISSION PER REP. For the month of October, Chad Wilson has 22 Deals Cleared. So his commission should be at least $25.

[hoyasaxa215]

Yes, but that information is only reflected in the LIST worksheet. The 22 deals for Chad in October reflect $0 in Commissions Per Deal (Column AK) and $0 in Commissions Total (Column AL) on the INPUT worksheet. Consequently, the Pivot Table on the COMMISSIONS PER REP worksheet correctly reflects the data input from the INPUT worksheet.

[trosasco23]

Yes that is the problem I'm trying to fix. That's what I meant when I said, is there another way to calculate this automatically or in another formula?

[hoyasaxa215]

It depends on how you intend the manipulate the data and the level of functionality you'd like with your subtotals. What is the time frame for calculating the relevant deals? Annual? Monthly? That is, completing 19 deals earns $0 per deal, but the 20th deal completed bumps to $25 per deal. Does the 20th deal have to be completed within one month or one year (or whatever potential time you rely on)?

[trosasco23]

Our reps get paid Monthly.. However many deals they clear in the month is what they get paid for.

[newdoverman]

Attachment 354600

This formula is currently on the LIST tab on column E. (I honestly don't know if this particular list is even necessary so if u think I can delete it, I will)

There is nothing currently referring to this list and as such it is doing nothing for you. It would become of value if you are calculating commissions for sales over a period of time like on the Commissions per Rep worksheet. The Commissions per Rep has nothing referring to it and delivers nothing to the other worksheets. Right now, it is doing nothing for you.

The Input worksheet has a lot of columns that are repeated between the white area and the purple area. All that I see from this is that it is repetition and bloats the data making it difficult to follow what is going on.

The only worksheet that the INPUT worksheet refers to is the LIST worksheet. All the other worksheets are at present independent not drawing data from other worksheets nor supplying data to other worksheets.

I recommend that you simplify the INPUT worksheet, eliminating the duplication and to give the columns titles that clearly define what the data under them is.

The COMMISSION PER REP, NEW COMMISSIONS and PER REP could draw some of the data from the INPUT worksheet and possibly the other worksheets thereby making them of more value and less labour intensive at the same time.

I don't mean this to be harsh but a suggestion to make your life easier.

[trosasco23]

The input tab is the only tab where I enter info..all the gray area (which you called purple) is just formulas to make my reports work. The list tab is just so I can create the drop down menus on the input tab.. the rest are all pivots to show my data in different views.. which is what the boss wants.

[newdoverman]

That makes more sense. The pivot tables didn't show as Pivot tables when I opened the file with editing enabled...very strange.

[newdoverman]

Here is something that might be workable. I copied the pivot table on COMMISSION PER REP then on a new worksheet pasted values. I filled in column A with the months then applied SUB TOTALS after deleting the totals inserted by the Pivot Table. This table is NOT linked to the Pivot Table so would have to be manually redone for changes. I think that a macro could be written or recorded that would automate the process.

[trosasco23]

Unfortunately this is not going to work for us manually. Whenever we get a new employee or someone leaves, we would need to change this info manually as well. That is too much of a hassle for the person that inputs the information. Thank you so much for trying though. I really appreciate all you help. All of you that tried to help. Have a good day.