Multiple a dynamic Range based on conditions

P

Pepperoni

New Member
Joined
Nov 30, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I have a bit of convoluted question regarding multiplying dynamic amounts based on date and time conditions. I have tried a fair few different formulas including sumproduct, vlookup and ifs - but nothing gives the right answer. This is quite a large sheet so I have only copied half here as I can't post anything larger.

I need to design a sheet where the user will input hours worked in column D so we can work out how many hours someone worked in a contract period (see B6) and work out any repayments that are potentially available.
As people generally don't start their contracts that align exactly with the repayment period that we can access, I have transposed the repayment period calculation into columns L-AK, and then worked out the daily rate in the yellow cell matrix. (e.g. This contract started on the 06/01 - however the repayment period we are accessing started on the 01/01. L14 says that they worked 7 days in the first period of 01/01 - 14/01, then the 13 and 14/01 are counted in the next repaynent period).

I am looking for a formula that can multiple the daily rate (G), by it's corresponding repayment period - G14 would align with L14, G15 would align with M14 AND M15. And then add everything from that row in the total hours. For example in row 14 of the larger matrix:
- 10 hours worked in the first week = 1.43 daily hours. (G14)
1.43*7 = 10.0 (g14*L14)
-.71*2 = 1.42 (g14*m14)
Total hours in row= 11.42 worked in that repayment period range.
The problem I have is that if any weeks are missing or equal 0, we inevitably get a value error. Ideally we need to assess up to 6 months of their contract (total rows is actually B14:B39).

I really hope this makes sense as I've been wracking my brain trying to figure out what will work. If further clarifying details are needed - I can post. Or, if a fully sheet is preferred, I can post a link to the full sheet.

Repayment Test.xlsx
ABCDEFGHIJKLMNAL
1Repayment Calulator
2
3
4
5
6Contract TypeWeekly7
7Repayment FrequencyFortnightly1440
8Repayment Start1/01/2022
9Repayment End2/07/2022PAYMENT AMOUNT HELPER
10Total Repayment$769.23
11Contract Start6/01/2022
12
13Contract StartContract Type EndHoursOtherTotal HoursDaily Rate HRSCumulative Average HoursRepayment StartRepayment End12/01/202219/01/2022########Total Hours Worked in Repayment Period
1416/01/202212/01/202210101.4310.001/01/202214/01/20227.002.000.00
15213/01/202219/01/2022550.717.5015/01/202228/01/20220.005.007.00
16320/01/202226/01/202211111.578.6729/01/202211/02/20220.000.000.00
17427/01/20222/02/202212121.719.5012/02/202225/02/20220.000.000.00
1853/02/20229/02/2022991.299.4026/02/202211/03/20220.000.000.00
19610/02/202216/02/2022440.578.5012/03/202225/03/20220.000.000.00
20717/02/202223/02/2022110.147.4326/03/20228/04/20220.000.000.00
21824/02/20222/03/202210101.437.759/04/202222/04/20220.000.000.00
2293/03/20229/03/202210101.438.0023/04/20226/05/20220.000.000.00
231010/03/202216/03/202214142.008.607/05/202220/05/20220.000.000.00
241117/03/202223/03/202220202.869.6421/05/20223/06/20220.000.000.00
251224/03/202230/03/202218182.5710.334/06/202217/06/20220.000.000.00
261331/03/20226/04/202230304.2911.8518/06/20221/07/20220.000.000.00
27147/04/202213/04/2022      
281514/04/202220/04/2022      
Sheet1
Cell Formulas
RangeFormula
D6D6=IFS(B6="Weekly","7",B6="Fortnightly","14",B6="Monthly","30.41")
D7D7=IFS(B7="Fortnightly","14",B7="Monthly","30.41",B7="Quarterly","91",B7="Milestone", "91",B7="Lump Sum","182")
E7E7=IFS(B7="Fortnightly","40",B7="Monthly","86.60",B7="Quarterly","260",B7="Milestone", "260",B7="Lump Sum","520")
B9B9=IF($B$2="A",$B$8+91,$B$8+182)
D10D10=IFS(B7="Fortnightly","$769.23",B7="Monthly","$1,666.67",B7="Quarterly","$5,000",B7="Milestone", "$5,000",B7="Lump Sum","$10,000")
L13:AK13L13=TRANSPOSE(UNIQUE(C14#))
B14:B39B14=LET(p,MATCH(B6,{"Weekly","Fortnightly","Monthly"},0),SEQUENCE(CHOOSE(p,26,13,6),,B11,CHOOSE(p,7,14,30.41)))
C14:C39C14=B14#+IF(B6="Weekly",6,IF(B6="Monthly",29.41,13))
F14:F28F14=IF(D14+E14=0,"",D14+E14)
G14:G28G14=IF(F14<>"",IFS($B$6="Weekly",F14/$D$6,$B$6="Fortnightly",F14/$D$6,$B$6="Monthly",F14/$D$6),"")
H14H14=IF(F14<>"",IFS($B$6="Weekly",$F$14/1,$B$6="Fortnightly",$F$14/1,$B$6="Monthly",$F$14/1),"")
H15H15=IF(F15<>"",IFS($B$6="Weekly",SUM($F$14:F15/2),$B$6="Fortnightly",SUM($F$14:$F$15/2),$B$6="Monthly",SUM($F$14:F15/2)),"")
H16H16=IF(F16<>"",IFS($B$6="Weekly",SUM($F$14:F16/3),$B$6="Fortnightly",SUM($F$14:$F$16/3),$B$6="Monthly",SUM($F$14:F16/3)),"")
H17H17=IF(F17<>"",IFS($B$6="Weekly",SUM($F$14:F17/4),$B$6="Fortnightly",SUM($F$14:$F$17/4),$B$6="Monthly",SUM($F$14:F17/4)),"")
H18H18=IF(F18<>"",IFS($B$6="Weekly",SUM($F$14:F18/5),$B$6="Fortnightly",SUM($F$14:$F$18/5),$B$6="Monthly",SUM($F$14:F18/5)),"")
H19H19=IF(F19<>"",IFS($B$6="Weekly",SUM($F$14:F19/6),$B$6="Fortnightly",SUM($F$14:$F$19/6),$B$6="Monthly",SUM($F$14:F19/6)),"")
H20H20=IF(F20<>"",IFS($B$6="Weekly",SUM($F$14:F20/7),$B$6="Fortnightly",SUM($F$14:$F$20/7),$B$6="Monthly",""),"")
H21H21=IF(F21<>"",IFS($B$6="Weekly",SUM($F$14:F21/8),$B$6="Fortnightly",SUM($F$14:F21/8),$B$6="Monthly",""),"")
H22H22=IF(F22<>"",IFS($B$6="Weekly",SUM($F$14:F22/9),$B$6="Fortnightly",SUM($F$14:$F$22/9),$B$6="Monthly",""),"")
H23H23=IF(F23<>"",IFS($B$6="Weekly",SUM($F$14:F23/10),$B$6="Fortnightly",SUM($F$14:F23/10),$B$6="Monthly",""),"")
H24H24=IF(F24<>"",IFS($B$6="Weekly",SUM($F$14:F24/11),$B$6="Fortnightly",SUM($F$14:$F$24/11),$B$6="Monthly",""),"")
H25H25=IF(F25<>"",IFS($B$6="Weekly",SUM($F$14:F25/12),$B$6="Fortnightly",SUM($F$14:F25/12),$B$6="Monthly",""),"")
H26H26=IF(F26<>"",IFS($B$6="Weekly",SUM($F$14:F26/13),$B$6="Fortnightly",SUM($F$14:F26/13),$B$6="Monthly",""),"")
H27H27=IF(F27<>"",IFS($B$6="Weekly",SUM($F$14:F27/14),$B$6="Fortnightly","",$B$6="Monthly",""),"")
H28H28=IF(F28<>"",IFS($B$6="Weekly",SUM($F$14:F28/15),$B$6="Fortnightly","",$B$6="Monthly",""),"")
J14:J26J14=LET(p,MATCH(B7,{"Fortnightly","Monthly","Quarterly","Milestone","Lump Sum"},0),SEQUENCE(CHOOSE(p,13,6,2,2,1),,B8,CHOOSE(p,14,30.41,91,91,182)))
K14:K26K14=J14#+IFS(B2="A",90,B7="Fortnightly",13,B7="Monthly",29.41,B7="Quarterly",90,B7="Quarterly",90,B7="Milestone",90,B7="Lump Sum",181)
L14,M15,N16L14=IF($J14<>"",LET(y,SEQUENCE($K14-$J14+1),x,TRANSPOSE($J14+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
M14,N15M14=IF($J14<>"",LET(y,SEQUENCE($K14-$J14+1),x,TRANSPOSE($J14+y-1),MMULT((x>=$B15)*(x<=$C15),y^0)),"")
N14N14=IF($J14<>"",LET(y,SEQUENCE($K14-$J14+1),x,TRANSPOSE($J14+y-1),MMULT((x>=$B16)*(x<=$C16),y^0)),"")
L15,M16,N17L15=IF($J15<>"",LET(y,SEQUENCE($K15-$J15+1),x,TRANSPOSE($J15+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L16,M17,N18L16=IF($J16<>"",LET(y,SEQUENCE($K16-$J16+1),x,TRANSPOSE($J16+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L17,M18,N19L17=IF($J17<>"",LET(y,SEQUENCE($K17-$J17+1),x,TRANSPOSE($J17+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L18,M19,N20L18=IF($J18<>"",LET(y,SEQUENCE($K18-$J18+1),x,TRANSPOSE($J18+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L19,M20,N21L19=IF($J19<>"",LET(y,SEQUENCE($K19-$J19+1),x,TRANSPOSE($J19+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L20,M21,N22L20=IF($J20<>"",LET(y,SEQUENCE($K20-$J20+1),x,TRANSPOSE($J20+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L21,M22,N23L21=IF($J21<>"",LET(y,SEQUENCE($K21-$J21+1),x,TRANSPOSE($J21+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L22,M23,N24L22=IF($J22<>"",LET(y,SEQUENCE($K22-$J22+1),x,TRANSPOSE($J22+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L23,M24,N25L23=IF($J23<>"",LET(y,SEQUENCE($K23-$J23+1),x,TRANSPOSE($J23+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L24,M25,N26L24=IF($J24<>"",LET(y,SEQUENCE($K24-$J24+1),x,TRANSPOSE($J24+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L25,M26,N27L25=IF($J25<>"",LET(y,SEQUENCE($K25-$J25+1),x,TRANSPOSE($J25+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L26,M27,N28L26=IF($J26<>"",LET(y,SEQUENCE($K26-$J26+1),x,TRANSPOSE($J26+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L27,M28L27=IF($J27<>"",LET(y,SEQUENCE($K27-$J27+1),x,TRANSPOSE($J27+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
L28L28=IF($J28<>"",LET(y,SEQUENCE($K28-$J28+1),x,TRANSPOSE($J28+y-1),MMULT((x>=$B14)*(x<=$C14),y^0)),"")
A14:A28A14=IFERROR(INT((B14+5-DATE(YEAR(B14+365),-11,-1))/7),"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H14:H39Expression=AND($B$6="Monthly",$H14<86.8)textNO
H14:H39Expression=AND($B$6="Fortnightly",$H14<40)textNO
H14:H39Expression=AND($B$6="WEEKLY",$H14<20)textNO
L14:AK39Cell Value=0textNO


Thank you.
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You must log in or register to reply here.

Similar threads

A
  • Question Question
How do you write the formula to check for duplicates from an entire range A2:J5?
Replies
8
Views
294
arthurz11
A
W
  • Solved
Formula simplification request
Replies
4
Views
170
Peter_SSs
Peter_SSs
tezza
  • Solved
Dynamically extend list help
Replies
3
Views
91
Cubist
Cubist
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
580
Rnkhch
R
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
289
Alex Blakenburg
A

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

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