Multiple Tiers by Member Question

M

Mgarcia8878

New Member
Joined
Oct 27, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Sorry if this question has been asked before. I apologize as I am new to this community. I'm trying to create a workbook where I can grab the row of ranges that are specific to a member's calc. This would be similar to a vlookup of the Member's ID. In the example below, member XYZ has its own start and end ranges as well as it own rates. I'm currently using a SUMPRODUCT formula, however it is tied specifically to the row for that member. Is there a way to have the range driven by the Member's ID?


xyz's formula = SUMPRODUCT((B2<='Rate Tab'!F2:I2)* (B2>'Rate Tab'!B2:E2)* (B2- 'Rate Tab'!B2:E2)* 'Rate Tab'!J2:M2)+ SUMPRODUCT(((B2>'Rate Tab'!F2:I2)* ('Rate Tab'!F2:I2-'Rate Tab'!B2:E2))* 'Rate Tab'!J2:M2)
abc's formula = SUMPRODUCT((B3<='Rate Tab'!F3:I3)* (B3>'Rate Tab'!B3:E3)* (B3- 'Rate Tab'!B3:E3)* 'Rate Tab'!J3:M3)+ SUMPRODUCT(((B3>'Rate Tab'!F3:I3)* ('Rate Tab'!F3:I3-'Rate Tab'!B3:E3))* 'Rate Tab'!J3:M3)

Calc Sheet:
1666929740640.png


Rate Tab:
1666929759110.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
We could not see the row / column index header & cell reference.
Could you attach a mini sheet?
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
Tier Example2.xlsx
ABCD
1MemberGAF $FS RateFS $
2xyz90,00016.7%15,000
3abc185,0003.4%6,200
Calc Sheet Example
Cell Formulas
RangeFormula
C2:C3C2=D2/B2
D2:D3D2=SUMPRODUCT((B2<='Rate Tab'!F2:I2)* (B2>'Rate Tab'!B2:E2)* (B2- 'Rate Tab'!B2:E2)* 'Rate Tab'!J2:M2)+ SUMPRODUCT(((B2>'Rate Tab'!F2:I2)* ('Rate Tab'!F2:I2-'Rate Tab'!B2:E2))* 'Rate Tab'!J2:M2)
 
Upvote 0
Stay in sheet Calc Sheet Example, any cell in row 2, create 3 dynamic names refer to start, end and rate:

start:
=OFFSET('Rate Tab'!$B$1,MATCH($A2,'Rate Tab'!$A$2:$A$3,0),,,4)

end:
=OFFSET('Rate Tab'!$F$1,MATCH($A2,'Rate Tab'!$A$2:$A$3,0),,,4)

rate
=OFFSET('Rate Tab'!$J$1,MATCH($A2,'Rate Tab'!$A$2:$A$3,0),,,4)

Then formula in D2 becomes:
Code: 
=SUMPRODUCT((B2<=end)* (B2>start)* (B2-start)*rate)+ SUMPRODUCT(((B2>end)* (end-start))*rate)
Need Formula For Total Column Thanks! v2.xlsx
ABCDEFGHIJKLM
1Member Start End Rate
2xyz02500050000750002500050000750001000000.10.150.20.25
3abc01000002500005000001000002500005000009999990.0450.020.0120.002
Rate Tab
Cell Formulas
RangeFormula
C2:E2C2=B2+25000
F2:H2F2=C2

Need Formula For Total Column Thanks! v2.xlsx
ABCDEFGH
1MemberGAF $FS RateFS $startendrate
2xyz90,0000.16666715,000#VALUE!50000#VALUE!
3abc185,0000.0335146,200
Calc Sheet Example
Cell Formulas
RangeFormula
C2:C3C2=D2/B2
D2:D3D2=SUMPRODUCT((B2<=end)* (B2>start)* (B2-start)*rate)+ SUMPRODUCT(((B2>end)* (end-start))*rate)
F2F2=OFFSET('Rate Tab'!$B$1,MATCH($A2,'Rate Tab'!$A$2:$A$3,0),,,4)
G2G2=OFFSET('Rate Tab'!$F$1,MATCH($A2,'Rate Tab'!$A$2:$A$3,0),,,4)
H2H2=OFFSET('Rate Tab'!$J$1,MATCH($A2,'Rate Tab'!$A$2:$A$3,0),,,4)
 
Upvote 0
Calculation part
I named the bracket information and the rate differentials
Commission2022.xlsm
ABCG
1MemberGAF $FS Rate
2xyz90,000.0016.67%15,000.00
Calc
Cell Formulas
RangeFormula
C2C2=D2/B2
G2G2=SUM((B2>aB_xyz)*(B2-aB_xyz)*aR_xyz)


Detail and alternative structures for formula follow.
You can use either sum or sumproduct and the names are optional.

Commission2022.xlsm
ABCDEFG
1MemberGAF $FS RateFS $FS $FS $
2xyz90,000.0016.67%15,000.0015,000.0015,000.0015,000.00
3abc185,000.003.35%6,200.00
Calc
Cell Formulas
RangeFormula
C2:C3C2=D2/B2
D2:D3D2=SUMPRODUCT(--(B2>Rates!B2:E2),B2-Rates!B2:E2,Rates!G2:J2-Rates!F2:I2)
E2E2=SUMPRODUCT(--(B2>{0,25000,50000,75000}),B2-Rates!B2:E2,{0.1,0.05,0.05,0.05})
F2F2=SUMPRODUCT(--(B2>aB_xyz),B2-aB_xyz,aR_xyz)
G2G2=SUM((B2>aB_xyz)*(B2-aB_xyz)*aR_xyz)


Commission2022.xlsm
ABCDEFGHIJ
1Member Start Rate
2xyz0.0025,000.0050,000.0075,000.0010.00%15.00%20.00%25.00%
3abc0.00100,000.00250,000.00500,000.004.50%2.00%1.20%0.20%
4
Rates
 
Last edited:
Upvote 0
Dave Patton said:
Calculation part
I named the bracket information and the rate differentials
Commission2022.xlsm
ABCG
1MemberGAF $FS Rate
2xyz90,000.0016.67%15,000.00
Calc
Cell Formulas
RangeFormula
C2C2=D2/B2
G2G2=SUM((B2>aB_xyz)*(B2-aB_xyz)*aR_xyz)


Detail and alternative structures for formula follow.
You can use either sum or sumproduct and the names are optional.

Commission2022.xlsm
ABCDEFG
1MemberGAF $FS RateFS $FS $FS $
2xyz90,000.0016.67%15,000.0015,000.0015,000.0015,000.00
3abc185,000.003.35%6,200.00
Calc
Cell Formulas
RangeFormula
C2:C3C2=D2/B2
D2:D3D2=SUMPRODUCT(--(B2>Rates!B2:E2),B2-Rates!B2:E2,Rates!G2:J2-Rates!F2:I2)
E2E2=SUMPRODUCT(--(B2>{0,25000,50000,75000}),B2-Rates!B2:E2,{0.1,0.05,0.05,0.05})
F2F2=SUMPRODUCT(--(B2>aB_xyz),B2-aB_xyz,aR_xyz)
G2G2=SUM((B2>aB_xyz)*(B2-aB_xyz)*aR_xyz)


Commission2022.xlsm
ABCDEFGHIJ
1Member Start Rate
2xyz0.0025,000.0050,000.0075,000.0010.00%15.00%20.00%25.00%
3abc0.00100,000.00250,000.00500,000.004.50%2.00%1.20%0.20%
4
Rates
Click to expand...
Dave your solution works fine when there are only two members. But what would you do if you have 500 plus?
 
Upvote 0
Commission2022.xlsm
ABMN
1MemberGAFFS $FS Rate
2xyz90,000.0015,000.0016.67%
3abc185,000.006,200.003.35%
4abc1,200,000.0032,500.002.71%
5
Data
Cell Formulas
RangeFormula
M2:M4M2=SUMPRODUCT(--(B2>C2:F2),B2-C2:F2,H2:K2-G2:J2)
N2:N4N2=M2/B2


Commission2022.xlsm
ABCDEFGHIJKLMN
1MemberGAFBracketsBlankRatesFS $FS Rate
2xyz90,000.000.0025,000.0050,000.0075,000.0010.00%15.00%20.00%25.00%15,000.0016.67%
3abc185,000.000.00100,000.00250,000.00500,000.004.50%2.00%1.20%0.20%6,200.003.35%
4abc1,200,000.000.00100,000.00250,000.00500,000.005.00%4.00%3.00%2.00%32,500.002.71%
5
Data
Cell Formulas
RangeFormula
M2:M4M2=SUMPRODUCT(--(B2>C2:F2),B2-C2:F2,H2:K2-G2:J2)
N2:N4N2=M2/B2
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
264
RobP
R
H
  • Solved
Copy the column cells ("B:N") into the first empty row.
Replies
5
Views
223
mumps
mumps
HappyChappy
  • Question Question
Hours over basic
Replies
4
Views
389
Fluff
Fluff
S
  • Question Question
Employee Training Tracker
Replies
0
Views
233
Schuyla
S
A
  • Solved
Problem with multiple IFS, IF and AND in long formula
Replies
2
Views
450
arendberg
A

Forum statistics

Threads
1,223,758
Messages
6,174,334
Members
452,555
Latest member
colc007

Share this page

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
Back
Top