(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.
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
Printer-friendly version
Send to friend
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
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.
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
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))
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
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))
Email: PPadmin@planningplanet.com
Replies