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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
or

Commission2022.xlsm
ABM
1MemberGAFFS $
2xyz90,000.0015,000.00
3abc185,000.006,200.00
4a 1,200,000.0032,500.00
5
Data
Cell Formulas
RangeFormula
M2:M4M2=SUM((B2>C2:F2)*(B2-C2:F2)*(H2:K2-G2:J2))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

HappyChappy
  • Question
Hours over basic
Replies
4
Views
172
Fluff
Fluff
A
  • Solved
Problem with multiple IFS, IF and AND in long formula
Replies
2
Views
128
arendberg
A
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
907
felixstraube
F
SaraWitch
  • Solved
Count number of cells over 18 but discount dates
Replies
3
Views
152
Fluff
Fluff
SaraWitch
  • Solved
Join text and return dates where there is one but ignore if empty cells
2
Replies
12
Views
547
SaraWitch
SaraWitch

Forum statistics

Threads
1,214,944
Messages
6,122,387
Members
449,080
Latest member
Armadillos

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