Fill values top down

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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

BCD

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]2000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]400[/TD]

[TD="align: right"]400[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]750[/TD]

[TD="align: right"]750[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]800[/TD]

[TD="align: right"]800[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]95[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]80[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]8000[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

</tbody>
 
Sort by date Sort by votes
B5 copied across
=MAX(MIN(B3,$J$4-SUM(B3:$I3)+B3),0)

M.[/QUOTE]

IT WORKS. Thanks Marcelo Branco
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Formula in I5 is copied left.

Excel 2016
BCDEFGHIJ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1500[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=MAX(MIN(I3,$J$4-SUM(J3:$J3)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/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?
 
Upvote 0
Marcelo Branco said:
hmm..
The formula is not working? Could you show a scenario where it didn't work?

M.
Click to expand...

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

BCDEFGHIJ

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] , align: right"]814[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] , align: right"]2,112[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] , align: right"]24,989[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] , align: right"]139,193[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] , align: right"]1,598[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc99]#ffcc99[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]817[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]817[/TD]
[TD="align: right"]817[/TD]
[TD="align: right"]817[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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>
 
Upvote 0
Small adjustment to handle blank cells...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
0​
[/td][td]
814​
[/td][td]
2112​
[/td][td]
24989​
[/td][td]
139193​
[/td][td]
1598​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
817​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
817​
[/td][td]
0​
[/td][td]
0​
[/td][td][/td][/tr]
[/table]

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

M.
 
Upvote 0
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.
 
Upvote 0
B5 copied across
=MAX(MIN(SUM(B3),$J$4-SUM(B3:$I3)+SUM(B3)),0)

M.[/QUOTE]
IT WORKS. Thanks Marcelo Branco
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Filling Modules in a Trapezoid Shape Using Excel VBA
Replies
1
Views
250
Fluff
Fluff
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
605
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,160
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