Fill values top down

[hsandeep]

B2:B9 is +ve Integers
C1 is Value
Output Required in D2:D9 as TO FILL VALUES STARTING D2 & Considering range B2:B9 TOP DOWN
Thanks in advance
Sheet1

	B	C	D
1		2000
2	400		400
3	750		750
4	800		800
5	1000		50
6	50		0
7	95		0
8	80		0
9	8000		0
10

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

 

[hsandeep]

B5 copied across
=MAX(MIN(B3,$J$4-SUM(B3:$I3)+B3),0)

M.[/QUOTE]

IT WORKS. Thanks Marcelo Branco

 

[hsandeep]

Marcelo Branco

Error is there in your formula....

 

[Marcelo Branco]

Marcelo Branco

Error is there in your formula....

hmm..
The formula is not working? Could you show a scenario where it didn't work?

M.

 

[Peter_SSs]

Great to see you Peter. IT WORKS. Thanks & Regards

You're welcome.

BTW, Marcelo's formula works for me.

 

[hsandeep]

Formula in I5 is copied left.

Excel 2016

	B	C	D	E	F	G	H	I	J
3	500	1000	9	8	2	700	200	500
4									1500
5	0	81	9	8	2	700	200	500

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula I5 =MAX(MIN(I3,$J$4-SUM(J3:$J3)),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

[/QUOTE]

There ARE situation when H3="" & I3=""
Then your formula does not works. I have to manually punch 0s in H3 & I3 to achieve the result.

Can the formula be adjusted to take care of NULLs in B3:I3?

 

[hsandeep]

hmm..
The formula is not working? Could you show a scenario where it didn't work?

M.

Here when NULL is in B3:I3
Sheet1 (2)

	B	C	D	E	F	G	H	I	J
3	0	814	2,112	24,989	139,193	1,598
4									817
5	0	0	0	0	0	817	817	817

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B5 =MAX(MIN(B3,$J$4-SUM(B3:$I3)+B3),0) C5 =MAX(MIN(C3,$J$4-SUM(C3:$I3)+C3),0) D5 =MAX(MIN(D3,$J$4-SUM(D3:$I3)+D3),0) E5 =MAX(MIN(E3,$J$4-SUM(E3:$I3)+E3),0) F5 =MAX(MIN(F3,$J$4-SUM(F3:$I3)+F3),0) G5 =MAX(MIN(G3,$J$4-SUM(G3:$I3)+G3),0) H5 =MAX(MIN(H3,$J$4-SUM(H3:$I3)+H3),0) I5 =MAX(MIN(I3,$J$4-SUM(I3:$I3)+I3),0) <tbody> </tbody>

<tbody>
</tbody>

 

[Marcelo Branco]

Small adjustment to handle blank cells...

	B​	C​	D​	E​	F​	G​	H​	I​	J​
3​	0​	814​	2112​	24989​	139193​	1598​
4​									817​
5​	0​	0​	0​	0​	0​	817​	0​	0​

Formula in B5 copied across
=MAX(MIN(--B3,$J$4-SUM(B3:$I3)+B3),0)

M.

 

[Marcelo Branco]

If the cells in B3:I3 contain formulas that, in some cases, return null string (""), then try

B5 copied across
=MAX(MIN(SUM(B3),$J$4-SUM(B3:$I3)+SUM(B3)),0)

M.

 

[hsandeep]

B5 copied across
=MAX(MIN(SUM(B3),$J$4-SUM(B3:$I3)+SUM(B3)),0)

M.[/QUOTE]
IT WORKS. Thanks Marcelo Branco

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.