Problem Using IFS Function

[scott4nier]

Good Afternoon All, I've got a problem and I'm hoping you can help.

I'm developing an estimating template for my business, and our customer agreement has multiple rates.

ie. Hourly Rate = $10/hr
Daily Rate = 20 hours = $200
Weekly Rate = 6 days = 120 hours = $1200
Monthly Rate = 3.5 weeks = 21 days = 420 hours = $4200

When inputting estimated time we'll always use hours. So if we plan on it taking 1 week, we would use 168 hours.

I'm tying to figure out a formula that would allow me to bill according to those parameters, while keeping in mind that it won't always be as easy as a full day, week, month etc.

For example if we worked 36 hours we would need to bill 1 day at the daily rate and then 12 hours at the hourly rate.

Hope I'm making some sense, and I'm hoping here someone has some insight because I've tried messing with it for 2 days now and I'm still not having any luck.

Thank you.

EDIT: I'm adding the file as well. The equipment section I'm messing with starts on line 150.

ROW 152 is an example of where I'm trying to figure out the right formula. Based on 192 hours I need it to calculate using 1 week and 1 day in M152. N152 is what it would come out to based on manual calculations.

[David A Coop]

I am struggling to get my head around your problem. I don't understand the 20 hour per day assumption. Does it include a labour hours for a crew?

I can understand how you get the result in N152, but the rate is the same for the week and the day, so there appears to be no difference between a weekly and daily rate.

I don't understand why you enter the total elapsed hours for a job instead of the days or hours worked in the units used column. For example, the 192 that has been entered is presumably one full week (say, Mon to Sat) plus one day, but what if it was two lots of Mon to Wed plus two days. This is 8 days = 192 hours. I can't envisage a formula which could calculate this correctly!

Clearly, I know nothing about your business, but I think you need more detail to work with than just the number of days multiplied the number of hours in the day.

Regards, David

[scott4nier]

I am struggling to get my head around your problem. I don't understand the 20 hour per day assumption. Does it include a labour hours for a crew?

No, this is for equipment only. Our contract is set up so that we can only bill equipment for 20 hours every 24 hours, 6 days every week (at 20hrs per day) and 3.5 weeks per month, using the previous constraints.

I can understand how you get the result in N152, but the rate is the same for the week and the day, so there appears to be no difference between a weekly and daily rate.

This was a crude attempt. I've been working with different IFS situations, and some IF/AND but I'm struggling to come up with what I need.

I don't understand why you enter the total elapsed hours for a job instead of the days or hours worked in the units used column. For example, the 192 that has been entered is presumably one full week (say, Mon to Sat) plus one day, but what if it was two lots of Mon to Wed plus two days. This is 8 days = 192 hours. I can't envisage a formula which could calculate this correctly!

Ideally what we would input in that units column would be overall project duration based on our gantt chart. If it was to last 11 days for example or 264 hours. I would need it to calculate the first week at 120 hours and then second week at 100 hours so basically 220 x the equipment rate.

It gets more complicated for me when it's a monthly charge or more. The struggle is trying to come up with a way for it to segregate what is charged at the monthly rate, the weekly rate and the daily rate.

[scott4nier]

deleted - figured out what my issue was with this particular question

[David A Coop]

I note that you seem to have solved your issue, so won't pursue it further.

Now that I understand what's happening, I would divide the 192 hours by 168, and pick up the integer to charge the weekly rate and the remainder to charge the daily rate. That is quite simple. Something like this =(INT(E152/168)*120+(MOD(E152/168,1)*7)*20)*K152 - you would need to adjust when you have a reference point for the different rates.

I have no idea how I would deal with the monthly issue unless you assumed that all months have 30 days.

Please take the time to mark your thread as closed if you no longer require help.

Regards,

David