Light bulbs replacement : formula

[Greg_76]

Hello everyone,

This is a very practical exercise that I am trying to do since a little while but without any success.

I defined 2 informations for light bulbs :
- The 1st time it is likely to burn and be replaced : For example after 2.5 years or after 0.3 year
- The life time of a bulb : For example : 4.5 years or 0.4 years

I would like to define for each coming year how many times the bulb will be replaced : 0, 1, 2, 3, ... times.

So I expect something like this :

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1st replacement[/TD]
[TD="align: center"]Life time[/TD]
[TD="align: center"]Year 1[/TD]
[TD="align: center"]Year 2[/TD]
[TD="align: center"]Year 3[/TD]
[TD="align: center"]Year 4[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]2.5[/TD]
[TD="align: center"]4.5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]...[/TD]
[/TR]
</tbody>[/TABLE]


The question is what formula can I use to calculate the values in Year 1, Year 2, etc ?

Many thanks in advance for your help !

Best regards from Brussels.

Gregory

 

[BenMiller]

Can you explain the difference between the two numbers? Isn't the lifetime of a bulb the amount of time until it burns out?

 

[Greg_76]

Hello Ben,

The first number is taking into account the fact that the lamp may be already burning since a long time so it can burn out soon. It is the time it will take for the next burn out, starting from now. This is usually smaller than the life time, but can be equal if you just replaced the lamp.

So when you know you have a brand new lamp, this value is the same as the life time. But if you are doing this for 1000 lamps, some of them will burn very soon, and some other much later.

At the end, I am calculating the cost and payback time of installing new lamps. When the first value is totally unknown, I simply assume it is 1/2 of the life time, as an average. But if I know that all the lamps were recently changed, I would take a bigger value.

This can also help making tests like "what if all the lamps burn out next month ?" and "what if 95% of the lamps are still good in 2 years ?"

I hope this answer your question.

Best regards,

Gregory

 

[BenMiller]

Ok, I got the first part.

With your Lifetime in column B (starting from B2) and your years numbers in row 1 starting at C1, here's a formula:

In C2 (and drag down):

=ROUNDDOWN(COLUMN(A1)/$B2,0)

In D2 (and drag down and across):

=ROUNDDOWN(COLUMN(B1)/$B2,0)-SUM($C2:C2)

This is for the Lifetime numbers. Still trying to work out how to first incorporate the 1st replacement amount into it...

 

[Greg_76]

I had this part as well. Indeed the 1st replacement makes it complicated.

If I had to calculate it only for one lamp, this is how I would proceede :

Repl = 0,1,2,3,4,...
PeriodRepl = $A$4+A7*$B$4
YearRepl = ROUNDUP(B7,0)
Year = 1,2,3,4,... (here the values in row 2 could be used)
Result = COUNTIF($C$7:$C$27,D8)

Here the array YearRepl has only 20 values and as you can see, after 8 years the results are missing.
I tested it for the worst lamps that we can have and figured out that the array YearRepl should idealy have 200 values to have at least 20 years calculated correctly in all possible cases.

The first two formulas can be combined = ROUNDUP($A$4+A7*$B$4,0)
The question is how we can combine this with the COUNTIF formula ?
Would there be a solution with a GETPIVOTDATA formula maybe ?

Thanks again for your help !
[TABLE="width: 585"]
<TBODY>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1st replacement[/TD]
[TD]Life time[/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[TD]Year 4[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2.5[/TD]
[TD]4.5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.3[/TD]
[TD]0.4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Repl[/TD]
[TD]PeriodRepl[/TD]
[TD]YearRepl[/TD]
[TD]Year[/TD]
[TD]Result[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]0.3[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]0.7[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]1.1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]1.9[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5[/TD]
[TD]2.3[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]6[/TD]
[TD]2.7[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]7[/TD]
[TD]3.1[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]8[/TD]
[TD]3.5[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]9[/TD]
[TD]3.9[/TD]
[TD]4[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]10[/TD]
[TD]4.3[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]11[/TD]
[TD]4.7[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]12[/TD]
[TD]5.1[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]13[/TD]
[TD]5.5[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]14[/TD]
[TD]5.9[/TD]
[TD]6[/TD]
[TD]14[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]15[/TD]
[TD]6.3[/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]16[/TD]
[TD]6.7[/TD]
[TD]7[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]17[/TD]
[TD]7.1[/TD]
[TD]8[/TD]
[TD]17[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]18[/TD]
[TD]7.5[/TD]
[TD]8[/TD]
[TD]18[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]19[/TD]
[TD]7.9[/TD]
[TD]8[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]20[/TD]
[TD]8.3[/TD]
[TD]9[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=5></COLGROUP>[/TABLE]

 

[Greg_76]

Hello,
I haven't found an answer yet so if someone has any idea it will be greatly appreciated !
Many thanks
Gregory

 

[Greg_76]

I'm desperately looking for a solution. Help pleaaase !
Thanks !

 

[Marcol]

See if this calculates the first part of your problem

Drag D2 Across for as many years as you need, then Down as required.
Drag T2 Down to match.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1st replacement	Life time	LeaveColumn C blank	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Year 11	Year 12	Year 13	Year 14	Year 15	Year 16	Totals
2	2.5	4.5		0	0	1	0	0	0	1	0	0	0	0	1	0	0	0	1	4
3	0.3	0.4		2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	40
Sheet1