Calculate percentages of dollar amounts on a table that can adjust to more or less accounts.

frkre05

New Member
Joined
May 4, 2018
Messages
6
Hi,
TIA for the help!
I have a table that I want to use to calculate commissions for client Life Insurance & annuity products. The table can increase or decrease depending on the number of cases on a given month. So on my table for Life Ins. I can ask it to add a whole column and then multiply it times a payout rate which is always fixed. So no manner how many rows I add or delete it calculates just fine. on the Annuities before it gets calculated times the payout rate it has to go threw a percentage of total dollar amount X the payout rate. the problem is that percentage varies so its not fixed and when I add or remove more rows it throws it off and wont calculate new rows.

New_Opportunities2.xlsx
ABCDEFGHIJK
1
2On The TableNet GDCNet AUMNet AnnuityRateProduct LineCarrierTotals:
3On the Table$0.00
4Hitting This Week$24,000.00
5AUM
6Underwritting$ -
7FF
8Hitting This WeekNet GDCNet AUMNet Annuity RateProduct LineCarrierCase Open
9$10,000.00$200,000.002.00%AnnuitiesCloses
10$5,000.00$100,000.004.00%AnnuitiesSubmitted
11$1,000.00$300,000.006.00%Appointments
12Underwrting Net GDCProduct LineCarrier
13Commissions:
14On the Table0.00
15Hitting This Week11,520.00
16AUM*Pending
17Fee Based0.00
18Fee Based PlanningAmmountTotal11,520.00
19
20Payout Rate48%
21
22
Numbers
Cell Formulas
RangeFormula
J3J3=SUM('Hidden Sheet'!B20+'Hidden Sheet'!B22)
J4J4=SUM('Hidden Sheet'!B15+'Hidden Sheet'!B17)
J6J6=SUM(Table1[Net GDC])
J14J14=SUM('Hidden Sheet'!B20+'Hidden Sheet'!B22)*Numbers!J20
J15J15=SUM('Hidden Sheet'!B15+'Hidden Sheet'!B17)*Numbers!J20
J17J17=SUM(Table1113[Ammount])
J18J18=SUM(J14:J17)
Cells with Data Validation
CellAllowCriteria
B3:B7Whole numberbetween -1000000000 and 1000000000
C3:E7Whole numberbetween -922337203685477 and 922337203685477
F3:F7List='Hidden Sheet'!$A$1:$A$10
B9:B11Whole numberbetween -1000000000 and 1000000000
C9:E11Whole numberbetween -922337203685477 and 922337203685477
F9:F11List='Hidden Sheet'!$A$1:$A$10
C13:C17List='Hidden Sheet'!$E$1:$E$4




New_Opportunities2.xlsx
ABCDEF
1Life1.Case Open/Case Prep100%STLife
2DI2.Needs Analysis75%LTDI
3LTC3.Closing50%LTC
4Annuities4.Application(s)10%Multiple
5Fee-Based Planning5.Signature(s)
6P&C
7Investment
8Advisory
9Brokerage
10Multiple
11
12
13
14Hitting this Week
15Net GDC16000
16Net AUM0
17Net Annuity$8,000.00
18
19On the Table
20Net GDC$0.00
21Net AUM$0.00
22Net Annuity$0.00
23
Hidden Sheet
Cell Formulas
RangeFormula
B15B15=SUM(Table410[Net GDC])
B16B16=SUM(Table410[Net AUM])
B17B17=SUM((Numbers!D9*Numbers!E9)+(Numbers!D10*Numbers!E10))
B20B20=SUM(Table39[Net GDC])
B21B21=SUM(Table39[Net AUM])
B22B22=SUM(Table39[Net Annuity])*0.04
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,181,772
Messages
5,931,942
Members
436,810
Latest member
cantresm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top