Offsetting Negative Value Across Positive Values

[ttruchok]

Hi All,

I am working with accounts receivable aging buckets, and each month I net customer deposits (which show as negatives) across other buckets from oldest to newest to offset them. Is there a formula that can handle this? The bold cells are currently what I enter manually, to offset the yellow cell customer deposit. I am thinking an If statement, but not sure? Any help would be very appreciated!

 

[jtakw]

Hi,

Shouldn't the 1st adjustment (8.00) under 25.00, 30-60 Bucket, be (15.00), and the 365+ Bucket be (29.00) ??
If not, please explain your logic.

 

[jtakw]

You've seen my question above, but no reply.
Assuming my post above is correct, use this:

Book3.xlsx
	A	B	C	D	E	F	G
1		Current	1-30 B	30-60 B	60-120 B	120-365 B	365+ B
2		50	40	25	4	10	(29.00)
3	Adjustments		0.00	(15.00)	(4.00)	(10.00)
Sheet1070

Cell Formulas
Range	Formula
G2	G2	=B2-SUM(C2:F2)
C3:F3	C3	=MIN($B2-SUM($C2:C2,$B3:B3),0)

 

[ttruchok]

You've seen my question above, but no reply.
Assuming my post above is correct, use this:

Book3.xlsx
	A	B	C	D	E	F	G
1		Current	1-30 B	30-60 B	60-120 B	120-365 B	365+ B
2		50	40	25	4	10	(29.00)
3	Adjustments		0.00	(15.00)	(4.00)	(10.00)
Sheet1070

Cell Formulas
Range	Formula
G2	G2	=B2-SUM(C2:F2)
C3:F3	C3	=MIN($B2-SUM($C2:C2,$B3:B3),0)

Hello thank you for the reply!

The (22) is considered a customer deposit, so that amount is fixed. We spread it across our oldest aging, so that our 365+ bucket doesnt look artificially low. Basically we offset it against the other buckets starting with the 120-365 bucket, and onto the next bucket, and the next, until it is totally used up. The original picture I posted is how the 'adjustment' line looks when the deposit is spread, and it nets the 120-365 bucket to 0, the 60-120 bucket to 0, and the 30-60 bucket to 17.

 

[KRice]

Try this:

Book1
	A	B	C	D	E	F	G
1		Current	1-30 Bucket	30-60 Bucket	60-120 Bucket	120-365 Bucket	365+ Bucket
2	Client Aging	50.00	40.00	25.00	4.00	10.00	(22.00)
3		0.00	0.00	(8.00)	(4.00)	(10.00)
Sheet1

Cell Formulas
Range	Formula
B3:F3	B3	=-MIN(B2,SUM(C3:$G3)-$G$2)

 

[ttruchok]

Try this:

Book1
	A	B	C	D	E	F	G
1		Current	1-30 Bucket	30-60 Bucket	60-120 Bucket	120-365 Bucket	365+ Bucket
2	Client Aging	50.00	40.00	25.00	4.00	10.00	(22.00)
3		0.00	0.00	(8.00)	(4.00)	(10.00)
Sheet1

Cell Formulas
Range	Formula
B3:F3	B3	=-MIN(B2,SUM(C3:$G3)-$G$2)

Amazing!!!! Thank you so much!!!!

 

[KRice]

I'm happy to help.