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

Work up a spreadsheet of what you think it should look like and attach it here. And please explain what you need more clearly.

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

Attached Files

test.xlsx (9.3 KB, 12 views)

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.

Attached Files

test.xlsx (11.5 KB, 11 views)

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

Something like this?

It assumes that the hire date is a Monday.

/Møller

Attached Files

Book2.xlsx (10.3 KB, 9 views)

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

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