xl 2010 Date repeater

[Lingg]

I've got a list of machines parts. Next to them are the mean time before failure in days. Next to that is the last failure that part experienced.

Part..........................MTBF Last Failure 9/22/2011 9/29/2011 10/6/2011
FL 1 Apron.................1308 11/19/2010
FL 1 Bearing................654 9/13/2010
FL 1 Chain.................1308 1/20/2010
FL 1 Contacts............1308 10/11/2008
FL 1 Coupling.............1308 2/1/2011
FL 1 Disch Screw........1308 12/21/2010
FL 1 Drag....................163 1/12/2011
FL 1 E&I.....................436 4/26/2010
FL 1 Eccentric.............327 4/6/2011
FL 1 Expansion Joint.....327 7/14/2010
FL 1 Fan.....................187 2/18/2011

I have dates stretched at the top for several years. I want to know when each part will break multiple times.

What I have now is

=IF(AND("Last Failure"+"MTBF">"Date1","Last Failure"+"MTBF"<Date2),X,"")
and it drags on for several different date values.

However that will only account for the first failure. Is it possible to make an If statement that has unlimited ands, like for any integer*MTBF. Or if there is a way make a VB function that would be very helpful.

Please ask questions if this is not explained clearly.
Thanks

 

[Norie]

What is it you are trying to calculate for each machine?

The next date a failure is expected based on the MTBF?

 

[Lingg]

What is it you are trying to calculate for each machine?

The next date a failure is expected based on the MTBF?

Each machine has an average time before it fails (MTBF) and there is the last time it failed. Adding the MTBF to the last time it failed should give the date of the next failure. I've started on cell D1 with today's date. Cell E1 =D1+7 (So next week) I've dragged this across the spreadsheet to have every week for the next 6 years.

I want it to feed off of the dates I've spread across the spreadsheet and the possible next failure date to show the next time a machine will fail. leaving in that cell an "X" or a link to a cell that shows the cost to replace the machine.

I've done all of this the only problem is that it stops after "next failure date". I'd want it to show the next date after that and so on (so the "next failure date" + MTBF")

Hope that clarifies things up for you.

 

[barry houdini]

Try this formula in E2 copied across (and down)

=IF(MOD(D$1+6-$C2,$B2)<7,"x","")

that should give you an "x" if an expected failure falls in that week (the week defined as 7 days starting at D1)

 

[Lingg]

That worked wonderfully, Thank you very much!