Complex formula required

[Dustychickpea]

Hi All,

I have been racking my brains to come up with a suitable formula for quite a specific scenario.

In row 22 (highlighted) I need values pulled through calculating what is marked under columns week 1-4 (E10: H16) factoring in payment terms (column D) in days. the theory is that depending on the terms of payment, "cash received" in row 22 would calculate which week it would arrive. Any idea if this is possible? I know that this can be achieved by column representing 1 day only, but that would not work for this specific workbook.

Any help would be greatly appreciated.

 

[KRice]

Can you offer more detail about the expected results? In your example, you show payments spread across the weeks (E10:H16). I'm assuming you are not looking for a simple sum down the columns, so that E22 = SUM(E10:E16). Are the payments terms in days indicating a one-time payment (e.g., in C10, is 1000 due in 30 days and then the transaction is completed?...or is 1000 due every 30 days?) In row 22, are you looking for a formula that would essentially place the full amount in column C somewhere on the weekly calendar that corresponds to the payment terms...and then sum those amounts down the weekly columns to provide an indication of total payments received each week (assuming the payments are made exactly at the end of the payment term)?

 

[Dustychickpea]

Can you offer more detail about the expected results? In your example, you show payments spread across the weeks (E10:H16). I'm assuming you are not looking for a simple sum down the columns, so that E22 = SUM(E10:E16). Are the payments terms in days indicating a one-time payment (e.g., in C10, is 1000 due in 30 days and then the transaction is completed?...or is 1000 due every 30 days?) In row 22, are you looking for a formula that would essentially place the full amount in column C somewhere on the weekly calendar that corresponds to the payment terms...and then sum those amounts down the weekly columns to provide an indication of total payments received each week (assuming the payments are made exactly at the end of the payment term)?

Hello,
I should have been clearer on this, my apologies. the values spread across weeks (E10:H16) are what need to be collected in row 22 and not the values in column C. In column E where we have a value of 500 in cell E10 I would want this calculated in a manner so that it would fall automatically into cell H22, 30 days being closest to 4 weeks (if each column represented 1 week) In cell E11 where we have a value of 7500 and terms of 45 days I would expect this to fall into cell K22 with 6 weeks being the closest to the payment term. The values held in column E would only appear once in Row 22 but due to the varying payment terms they would appear in different cells for cash received. This would also have to be combined with what is held in the following week (column F and so on) since the varying payment terms will overlap.

 

[KRice]

Give this a try and let me know if it gives the desired outputs. This allows you to specify a "0 time" for a payment term, as though to place it on a yearly calendar indicating when the payment terms are originally declared---perhaps you would be at the beginning of wk #3 when you declare that the payment for Package 3 will be due in 30 days (see line 12). This would shift the payment due to 30 days after the beginning of week 3, which would fall in week #7. So the formula relies on inputs from columns D and E and then assumes that column F represents Wk #1, and that the weeks increase sequentially as you move to the right through the table.

MrExcel_20220503.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
7				Week		1	2	3	4	5	6	7	8	9
8				Pmt Terms (d)	Term begins @ start of Wk#
9		Package	Sale
10		Package 1	1000	30	1					1000
11		Package 2	15000	45	2								15000
12		Package 3	255	30	3							255
13		Package 4	800	30	1					800
14		Package 5	60000	14	1		60000
15		Package 6	150	30	1					150
16		Package 7	60	30	1					60
17
18		Total Sales	77265			0	60000	0	0	2010	0	255	15000	0
Sheet6

Cell Formulas
Range	Formula
F10:N16	F10	=IF(COLUMNS($F:F)=$E10-1+CEILING($D10,7)/7,$C10,"")
C18,F18:N18	C18	=SUM(C10:C16)