Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Progress Measurement - Excel

6 replies [Last post]
Jithin Kambhikanam
User offline. Last seen 1 week 21 hours ago. Offline

Hi All,

 

Not sure if I can post it here, if not please dont respond.

I need to calculate Overall Planned using if function or any other excel function. As and example I have plotted this below

On 3rd Nov I want to see the Planned % (A) = 15%

Date3-Nov-21 
ActivityDateWeightage
Excavation2-Nov-215%
Blinding3-Nov-2110%
Reinforcement and formwork8-Nov-2130%
Concrete Pouring9-Nov-2125%
Curing16-Nov-2115%
Painting17-Nov-215%
Backfilling18-Nov-2110%
 Total      100%
 Planned %       A

 

Can it be done in excel, ie one cell (A) will say what was the planned % a range on 3 Nov 21.

 

Thanks

Jithin

Replies

Jithin Kambhikanam
User offline. Last seen 1 week 21 hours ago. Offline

Thanks You,

 

Both the formula Works fine now.

 

Rafael,

Its just for a plotting an example, not in actual and I have considered foundation for a Pump which is small.

 

Regards

Jithin

Rafael Davila
User offline. Last seen 14 hours 19 min ago. Offline
Joined: 1 Mar 2004
Posts: 5073

And what about nonworking days and days with fewer work hours?

Weird weightage of excavation is less than weightage of curing, well the whole concept of weightages I find it flawed.

Steven Auld
User offline. Last seen 4 days 13 hours ago. Offline
Joined: 13 Sep 2017
Posts: 126

Jithin,

Try using SUMIF

Using Rodel's template, the calculation would be:

=SUMIF(B3:B9,"<="&B1,C3:C9)

Dont forget to include the quotation marks around the "<=" part.

SUMIF(Range,Criteria,SumRange)

Regards,

Steven

Rodel Marasigan
User offline. Last seen 2 hours 18 min ago. Offline
Joined: 25 Oct 2006
Posts: 1639

Hi Jithin,

Yes. Just change the match type to 1 instead of 0.

Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,1),1))

Jithin Kambhikanam
User offline. Last seen 1 week 21 hours ago. Offline

Thanks Rodel,

It does work but there is a small catch here.

If the date is not mentioned in Cell B1 doesnt match the dates in Cell B3:B9, then the result is #N/A.

Say in the above table in B1 if I put 4-Nov-21, Result = #N/A.

It should rather give 15%.

Any way to sort this out.

 

Thanks

Jithin

Rodel Marasigan
User offline. Last seen 2 hours 18 min ago. Offline
Joined: 25 Oct 2006
Posts: 1639

Yes, you can use sum function with offset and match to get the correct range. I assumed that your lookup is the date.

Column A = Activity
Column B = Date
Column C = Weightage

Ex: =SUM(OFFSET($C$2,1,0,MATCH($B$1,$B$3:$B$9,0),1))

Sum-function