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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,418
Members
412,529
Latest member
cTatch
Top