Monthly Scope Distribution using IF Formula

loginid

New Member
Joined
Jun 5, 2015
Messages
8
Dear friends,

In the below sheet "Balance Scope" needs to be distributed monthwise based on Start date and Finish date columns automatically.
requirement is: if "Done" E column is >0 and Data Date (G2) is > subsequent months (i.e. L5,M5,N5,O5,P5......) then balance scope should be distributed in the remaining months
And previous month plan values should NOT change.

Copy of Petrapole_ Tracker_KKD.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Data Date
230-09-20
331-07-20
4Seg-1 Grid Q-X/4-8 (1/3rd Area)Latest Tracked as on Jun-20Per day Qty
5Sl NoActivityUnitScopeDoneBalanceStartFinishJun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Total% Complete
61PilingNos625 2 623 23-Jul-2012-Sep-2012.22Plan - 98 379 147 - - - - - - 623 0.32%
7Achieved 2 2
82Sheet PilingRmt278 - 278 26-Jun-202-Sep-204.09Plan 16 127 127 8 - - - - - - 278  
9Achieved -
103ExcavationCum27173 - 27,173 2-Sep-2014-Oct-20646.98Plan - - - 18,115 9,058 - - - - - 27,173  
11Achieved -
124Metal FillingCum1595 - 1,595 15-Oct-2030-Oct-20106.33Plan - - - - 1,595 - - - - - 1,595  
13Achieved -
145PCCCum445 - 445 17-Oct-201-Nov-2029.67Plan - - - - 415 30 - - - - 445  
15Achieved -
166RCC FDNCum7950 - 7,950 20-Oct-2025-Nov-20220.83Plan - - - - 2,429 5,521 - - - - 7,950  
17Achieved -
187RCC Col , Wall,Deck slabCum1875 - 1,875 23-Oct-2022-Dec-2031.25Plan - - - - 250 938 688 - - - 1,875  
19Achieved -
208FillingCum1043 - 1,043 4-Feb-2116-Mar-2126.06Plan - - - - - - - - 626 417 1,043  
21Achieved - -
Tracker-PTB (2)
Cell Formulas
RangeFormula
E6,E20,E18,E16,E14,E12,E10,E8E6=SUM(K7:T7)
F6,F20,F18,F16,F14,F12,F10,F8F6=D6-E6
G16:H16,G6:H6G6='TCP-PTB'!H23
K6K6=MIN(IF($G6<K$5,(K$5-$G6)*$I6,0),$D6)
L6,L20:T20,L18:T18,L16:T16,L14:T14,L12:T12,L10:T10,L8:T8L6=MIN(IF($G6<L$5,(L$5-$G6)*$I6,0),$F6)-SUM($K6:K6)
M6:T6M6=IF($E6>0,IF($G$2>M5,MIN(IF($G6<M$5,(M$5-$G6)*$I6,0),$D6)-SUM($K6:L6),MIN(IF($G6<M$5,(M$5-$G6)*$I6,0),$F6)-SUM($K6:L6)),"")
V6,V20,V18,V16,V14,V12,V10,V8V6=IF((U7/U6)>0,U7/U6,"")
G8:H8G8='TCP-PTB'!H28
K8,K20,K18,K16,K14,K12,K10K8=MIN(IF($G8<K$5,(K$5-$G8)*$I8,0),$F8)
G12:H12,G10:H10G10='TCP-PTB'!H29
G14:H14G14='TCP-PTB'!H32
G18G18='TCP-PTB'!H34
H18H18='TCP-PTB'!I34+15
D20D20=278*0.75*5
G20:H20G20='TCP-PTB'!H131
I6,I20,I18,I16,I14,I12,I10,I8I6=F6/(H6-G6)
U6:U21U6=SUM(K6:T6)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
V6,V8,V10,V12,V14,V16,V18,V20,V25,V27,V29,V31,V33,V35,V37,V39,V44,V46,V48,V50,V52,V54,V56,V63,V65,V67,V69,V71,V73,V75,V77,V58Other TypeDataBarNO


Formula tried : M6 Cell =IF($E6>0,IF($G$2>M5,MIN(IF($G6<M$5,(M$5-$G6)*$I6,0),$D6)-SUM($K6:L6),MIN(IF($G6<M$5,(M$5-$G6)*$I6,0),$F6)-SUM($K6:L6)),"")

Thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,498
Messages
5,832,060
Members
430,109
Latest member
tinezi

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
Top