Planned vs Actual Formula

Hello there,

Can someone please help me fix the formula in my variance report?

In my sample template, Person A should be overutilized since his actual hour is greater than than as planned. However, i'm getting an "underutilized" result. I suspect there is something wrong with the formula in the Planned hours column -- IFERROR((VLOOKUP(B3,I13:J14,2,0)*8),"0"). Even if the value is "zero", the formula in Status column take it as Planned (0) is greater than the Actual (32).

Thanks in advance.

Thanks for the help, Dude!!!

Apologies I am not really aware of that features.

Anyway, I already clicked the star icon to say thank you for the helped provided by benishiryo.

Before I mark this thread close, I would like to seek assistance in creating a single formula for my summary tab applicable to all months. In my sample, I want to show the performance of Bruce Wayne in every project he worked for June and July so that we will know if he was properly utilised or not. Would that be possible?

Thank you again in advance.

Apologies I am not really aware of that features.

Anyway, I already clicked the star icon to commend benishiryo's helped

Before I mark this thread close, I would like to seek assistance in creating a single formula for my summary tab applicable to all months. In my sample, I want to show the performance of Bruce Wayne in every project he worked for June and July so that we will know if he was properly utilised or not. Would that be possible?

Thank you again in advance.

Hi,

Find the updated sheet with formula in Summary sheet some changes are made in summary sheet.

Punnam

Hi Punnam,

Tried to use the formula you created in my sample but I am getting an #NAME? error.

Maybe the formula doesn't suit in the original template. I attached the original template hope that you can help me create a formula applicable in the file. I wish the formula can show the utilisation in the Summary by deducting the plan effort in the actual time (Actual Effort tab) worked by the resource every month.

Thanks!!!

Hi,

Find the updated file with necessary changes please check the correctness of the data manually & Conform the same.

Punnam

This is great....

Though we need some tweaks.

> Values in plan effort is in days and should be converted to hours (multiplied to 8 hours).
> Is there a way we can avoid showing #VALUE!?
> Black Widow utilisation doesn't match as expected. For June, Black Widow has been allocated for Project YA for total hours of 24 (3 days x 8 hours - for week June 30) and worked for the same project for 40 hours. So, variance that should reflect is 16 hours which is overutilised.

Hi,
1) Multiplication with 8 hour in a day for the plan effort in summary sheet
2) #Value are generated due to data in Plan Effort sheet i observed that some cells are showing blank but having " " (i.e (Space) i have to be identified and remove manually by Replace command .
3) I have corrected the formula to match u r requirement .
Punnam

Hi Punnam,

I mapped the formula on my template it looks great though there are still cells that showing an #VALUE!. I really don't know what and how to fix it. Sorry to bother but can you please help me again to perfect the formula.

Also, I observed wrong value for Bruce Wayne for June under Project 10. His allocation was 40 hours and the actual work was 16 hours (if you sum up the Project time (col C) of Bruce for June 30) but we are getting 32 hours (see attached).

Again, thank you in advance for the help.

Hi,

Please check the Cell H18,J18 & AH 25 of plan effort Sheet they are not having any number or Symbol or Character this can be due importing a data from software or eels . you need to identify them make sure they are deleted . to avoid #Value.
Delete the Above said cell content and check in summary sheet u will not get #value error .
I have made changes in formula also. Check the result

Punnam

Thanks for all your help, Punnam.

One last thing, I want to change the formula to this for larger data in Actual effort sheet:

SUMPRODUCT(('Actual Effort'!$B:$B=$E11)*('Actual Effort'!$E:$E=$F11)*((TEXT('Actual Effort'!$F:$F,"m-YY")=TEXT(Summary!G$3,"m-yy"))*('Actual Effort'!$C:$C)))

Unfortunately (again), I'm getting this error #Value

HI,

I have checked the formula as it is array related it is taking too munch time for calculation and will be very difficult yo use the workbook.
c:c 1048576 Row
So better updated the range manually

2) U can ask help Preparation of VBA Code
Punnam

Hello,
I have a question in regards to this subject, can you please help me?

what is the difference btw Planned/Actual and Actual/Planned?

does each one have a different meaning when calculating?

Thanks

Hello,
I have a question in regards to this subject, can you please help me?

what is the difference btw Planned/Actual and Actual/Planned?

does each one have a different meaning when calculating?

Thanks