#1
|
|||
|
|||
Formula to work out equipment hire based on a minimum hire period.
Hi there,
I working with equipment requirements and minimum time that one can hire, in this case on a 4-weekly cycle. For example in week 1 I need one piece and in weeks 2 to 4 I need 0, then I need 1 for the first 4 weeks. Then if I need 2 in week 5 and 1 in week 6, then I need 2 from week 5 to week 8. Is it possible to do a formula that works that out if my weeks are in columns and then my equipment types in rows like in the example below? Many thanks for the help. W1 W2 W3 W4 W5 W6 W7 W8 W9 W10 W11 equipment by week 0 1 0 0 0 1 2 3 0 0 1 |
#2
|
|||
|
|||
Work up a spreadsheet of what you think it should look like and attach it here. And please explain what you need more clearly.
|
#3
|
|||
|
|||
Formula to work out equipment hire based on a minimum hire period.
Hi gebobs, thanks for the reply.
In the spreadsheet attached you can see the number of equipment required by week. In week 27 I need to 1, and week 28 I need 10. However, once a piece of equipment is hired, then I need to keep it for 4 weeks. So the equipment I hire in week 27 I can only send it back in week 31, but in week 28 I have to hire another 9, which I need to keep for 4 weeks....and so on. I don't know how to do it or if it can be done with formulas without using a macro. Any help is much appreciated. Thanks |
#4
|
|||
|
|||
So all the equipment in the second row the same i.e. the hire in Week 27 be used among the ten hired in Week 28. Correct?
What is it you are trying to do? Are you trying to optimize hires? Let's look at the second row: Week 27: hire 1 Week 28: hire 9 more Week 29: 2 surplus Week 30: 2 surplus Week 31: hire in week 27 ends, 2 surplus Week 32: hire 9 Week 33: - Week 34: one surplus Week 35: one surplus Week 36: hire 10 Week 37: 3 surplus Week 38: 6 surplus Week 39: 10 surplus See attached for visual. It doesn't really do anything. Just trying to get a better feel for what's going on. |
#5
|
|||
|
|||
That's about it, every time I hire a piece of equipment I need to keep it for 4 weeks, as simple as that, but I couldn't work it out with formulas.
Many thanks |
#6
|
|||
|
|||
Something like this?
It assumes that the hire date is a Monday. /Møller |
#7
|
|||
|
|||
Hi c991257,
Not quite that as I calculate the equipment required on a weekly basis. From that I then need to keep the equipment for 4 weeks. Thanks anyway |
#8
|
|||
|
|||
So you determine how much equipment you need on a week by week basis, and then you want to calculate if you have too much or too little based on the 4-week minimum rental period? If you have too little equipment you'll need to hire more and if you have too much you are basically p***ing out money?
/Møller |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need formula to determine minimum quantity needed per price | mbking | Excel | 2 | 02-10-2015 01:46 PM |
Count the common time period (month) between two date period of time | Barni | Excel | 6 | 08-15-2014 07:52 AM |
Trying to create a formula to calc if I add x new accounts, I will have to hire y tech/support ppl | glen123 | Excel | 7 | 07-28-2014 07:29 AM |
How do I calculate planned work for a given time period | bumpui | Project | 1 | 06-04-2014 06:37 AM |
Managing equipment and person operating equipment | ProfessorGilligan1 | Project | 1 | 04-12-2012 12:29 PM |