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
Offline
Joined: 22 Mar 2011
Posts: 181

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%

 Date 3-Nov-21 Activity Date Weightage Excavation 2-Nov-21 5% Blinding 3-Nov-21 10% Reinforcement and formwork 8-Nov-21 30% Concrete Pouring 9-Nov-21 25% Curing 16-Nov-21 15% Painting 17-Nov-21 5% Backfilling 18-Nov-21 10% 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
Offline
Joined: 22 Mar 2011
Posts: 181

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
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
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
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
Offline
Joined: 22 Mar 2011
Posts: 181

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
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))