Nested IF Statement

Hey everyone. I am working on a resource model for my company. Essentially it is a list of contractors in the company with their daily rates and start and end dates. I have attached a sample spreadsheet.

As you can see, all the information has already been populated with the cost of each contractor per month (formula used - daily rate*18.83 [days worked per month]). It is also dynamic so if for some reason a contractor end date is changed it needs to be reflected on the cost per month columns (thanks mobidv).

Now the only issue is that the formula will calculate the cost for a contractor for a full 2 months even if they have only worked for 1.5 months (example of row 4). So basically the formula needs to change so if someone has not worked for the full month, it needs to be reflected in the cost so they are billed only for the number of weeks they worked for the month. Each month is based on 18.83 work days.

Any help is greatly appreciated.

Thanks.

Perhaps:

Please Login or Register  to view this content.

copied down and across


Eomonth() needs analysis toolpak installed.

Unfortunately, the way this company works, each month is based on 18.83 work days once all holidays have been taken into account (total number of holidays subtracted from 365 and divided by 12). When I first saw this I said to my manager that pretty much assumes that all holidays are spread even over a one year period which is extremely inaccurate. I was told that it was the company policy so I had to work with it.

If you have any other suggestions I can try draft something up and present it to senior management and hope they will change their minds. I was actually wondering if there is a way to highlight all public holidays, bank holidays, etc in an Excel calendar like you can do on MS Project. If that were possible then I may have a chance of making that work.

Edit - Thanks NBVC, that formula has worked. Very much appreciated.

NB - Considering this is a stupid way to setup a resource model, I will try and use a different setup and present it to senior management. To do this, I will need to mark down all UK public holidays so I can use the networkdays formula to accurately forecasts costs. Is there a way to setup a holiday calendar like in MS Project?

Originally Posted by daddylonglegs

If you list all your holiday dates somewhere on the worksheet, e.g. in Z1:Z100then you could use this formula in J3 copied across and down

=MAX(0,NETWORKDAYS(MAX(J$2,$G3),MIN(EOMONTH(J$2,0),$H3),$Z$1:$Z$100))*$E3*$F3

I'm assuming that Monday to Friday will be working days and that Saturday and Sunday aren't worked. This would calculate based on the actual number of working days in each month. 18.83 still seems like a low number, that would equate to 35 holidays per year.

I have just tried that and it is only returning Value!. I have attached a spreadsheet for your reference. Refer to 'Pilot Version' worksheet.

Originally Posted by daddylonglegs

Won't that give identical figures for J4 and K4, I thought that the idea was to only give approx half month's cost to January because work finished on the 15th?

I note also that J1 shows a figure of 13.83, is that a typo?

No, it is definitely returning the correct value. Have a look at the attached spreadsheet under Company Method worksheet.

The reason its showing 13.83 on in J1 is to account for programme shutdown which will only be happening this December.

Originally Posted by daddylonglegs

Won't that give identical figures for J4 and K4, I thought that the idea was to only give approx half month's cost to January because work finished on the 15th?

I note also that J1 shows a figure of 13.83, is that a typo?

Yes, correct. We should make colume J relative when referencing J1... as below.

=IF($H3>J$2,$E3*$F3*J$1,0)*($H3-$G3)/(EOMONTH($H3,0)-(EOMONTH($G3,-1)+1))

Originally Posted by daddylonglegs

......but the K4 figure still calculates as if that was a full month rather than a half month, try changing H4 to 3rd January, isn't the K4 figure too high for only 3 days worked?

without taking into account specific holidays,, I'd suggest this formula in J3 copied across and down

=$E3*$F3*J$1*MEDIAN(0,1,($H3-J$2+1)/(EOMONTH(J$2,0)-J$2+1))

The #VALUE! error with my earlier suggestion is caused because some of your dates wren't recognised as true dates within the holiday range. If you correct that, i.e. enter dates in the format your regional settings requires, then the formula should work as expected

I assume you mean change to Jan 3, 2009...that would make 33 days (or 25 Working days).

Thanks a million for that daddylonglegs. Both formulas are now working. Ill be using the formula you suggested initially (=MAX(0,NETWORKDAYS(MAX(J$2,$G3),MIN(EOMONTH(J$2,0),$H3),$Z$1:$Z$100))*$E3*$F3) as a pilot version which I will be submitting for approval. This will be the easiest way to work instead of assuming an even 18.83 workdays per month.

Just one last favour to ask. For my own education, are you able to explain the logic behind both formulas in case I need to use it again on another occassion. That way Ill know how to use the formulas and apply logic instead of bugging you guys and hopefully will be able to help others out too.

Thanks again.

Edit - How do we change our forum username? I was hoping to have it changed to FM1 if possible.

Thanks for the explanation mate. Very much appreciated.

Edit - Uploaded the final version of the spreadsheet with the formulas in case someone else might need it for their reference.

Edit - please disregard. Stuff with my actual excel program.