Formulas for a maintenance table

[VatternKraftan]

Hello,

I'm trying to create a table for maintanence where accumulated hours of an article or the expected lifetime of an article should trigger a cost to be shown.
I'm however unable to achive this and have tried multiple different formulas but I ndo not get it to take into consideration the last time the article was repaired.
Can anyone help me?

ExampleSheet.xlsx

[AliGW]

Administrative Note:

Welcome to the forum.

Is your forum profile showing the oldest Excel PRODUCT that you need this to work for? 2402 is the version, but what is the product? 365?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[AliGW]

Sorry - I do not understand what is required. Please talk us through the exact calculation needed for one example - what, where and why?

[VatternKraftan]

I think i've changed it now. Yes O365.

[VatternKraftan]

I want either the Accumulated run hours OR the "years of replacement" to trigger the cost to show up in the table.
However, i have the problem that the formulas needs to take into consideration the last time the article was changed. Maybe it needed to be changed due to runned hours at year 3 (row 23), then it should not be changed year 4 (as it is now).
Instead the formulas should count accumulated run hours and years from the last replacement.

[AliGW]

Could you please simplify your sample workbook? Instead of non-working formulae, mock up manually what you need for ONE article. Highlight relevant cells and annotate the workbook clearly, showing what relates to what and why.

Remember this is all clear to you: it's your data and you know it well. To me, it might as well be hieroglyphs.

[VatternKraftan]

I think i've clarified it a bit now.

ExampleSheet (1).xlsx

[AliGW]

Not completely. For example:

This row should be changed at 20000 hours (6th year).

Why? On what basis? Imagine that you are explaining this to a 10-year-old. Your data means nothing to me - you need to interpret it for me so that I can understand it well enough to help.

The formulas are currently not working as they should "restart" the calculations after a replacement of the article.

What does 'after the replacement of the article' actually mean? What does it refer to? Again, I am not familiar with your field of expertise.

[VatternKraftan]

I'm sorry for the fuzzy instructions.

The limit hours are shown in column E. The accumulated hours the unit have done is shown in row 8

When the article have reached it end of life (either thourgh years or run hours) it should be replaced. If this is done on lets say year 4. A new piece is fitted with the same life expectancy. Hence the lifetime should be calculated from the last "replacement" (the last time a cost is shown in that row).

[AliGW]

Sorry - too fuzzy for me. I am sure someone will understand, though.

[VatternKraftan]

IHello,

I've ried once again to simplify my example. Is it more understandable now?

ExampleSheet.xlsx

[AliGW]

I'll have a look in a while. I'll put out a call for help.

[JeteMc]

I don't understand why article 2 should not be replaced in both years 2 and 3. It seems that it should be replaced at 5,000 hours which is during year 2, and again at 10,000 hours which is during year 3.
If that is the case then the following formula should work:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bebo021999]

I understand your point like this: I buy a Tesla car from Elon Musk, with a warranty condition of: 2 years or 100,000 km, whichever comes first.
This means that if after 2 years, even if the ODO shows only 5,000 km, the warranty will expire. Or, if after only 1 year the ODO shows 100,000 km, the warranty will also expire.
As in your example 'Article 3', 20,000 hours or 8 years, whichever comes first, the result is 7 years because in the 7th year, the total is 25,550 hours, which is greater than 20,000
Is this what you mean?

[VatternKraftan]

Article 2 is limited by either 2 years or 5000 hours. At the top (row 8), you can see the accumulated hours run at the end of each year. If article 2 is changed at 2 years (and 7300 hours) it should now be changed in year 4, when it once again reached 7300 hours (14600 / 2). So this article is matching up its yearly lifespan to its hourly lifespan. As it is replaced in year 2, it should not be replaced in year 3.

[VatternKraftan]

@bebo021999

Yes! This is exaclty what i mean. But as the part is replaced, a new "lifespan" starts. Hence, i need to know the last time the article was changed and calculate the "Yearly Lifespand And/or the hourly lifespan" of the product before another change is triggered. For example article 1 which is changed due to hourly lifespan at year 3 should not be changed again until 4 years or 9000 hours is reached calculated from year 3 (i.e. not change year 4).

[JeteMc]

Based on post #15, it seems to me that instead of being replaced every 5000 hours or 2 years "whichever comes first" (as car warranties are written), article 2 is replaced during/at the end of the year in which it has been used for 5000 hours, which is year 2 when it has been used for 7300 hours.
Using the formula from post #13, put 7300 into cell E12 and article 2 will replace as expected. Furthermore put 10950 and 21900 into cells E11 and E13 respectively and articles 1 and 3 will replace as expected.
Let us know if you have any questions.

[windknife]

Perhaps,
Clean all expected results firs

Please Login or Register  to view this content.

[josephteh]

Maybe, in L11: =IF(MOD(L$7,MIN($D11,ROUNDUP($E11/$D$4,0)))=0,$G11,""), copy across and down.

[VatternKraftan]

Perhaps,
Clean all expected results firs

Please Login or Register  to view this content.

This seems to do the trick. But i'm not quite sure how to apply it to my main sheet.
I can see that the formulas are "greyed out" in the other cells except L11. How is this applied?

[AliGW]

You need to remove ALL manually mocked-up results first, then apply the formla to L11. It then spills to fill the space - do not attempt to drag copy it! You can only edit it in cell L11 (or the origin cell - top left of the spill range).

The ranges in parameters a and b are the only things that you would need to edit if you are using it in a different workbook with different layout.

[VatternKraftan]

Thank you very much! That solved the calculations! Now i ran into another problem.
I have blank rows inbetween sections in my main sheet. What happens now is that the Spill does not skip the rows, i.e. it becomes offset after a few rows.
Screenshot 2024-08-26 083017.png

[AliGW]

This is why sample data provided should be realistic.

Please provide a new sample set of data. Spill formulae cannot skip blank rows unless they can be matched up with something on that sheet. This might require a full re-write of the formula ...

[VatternKraftan]

I guess I simplified the data to much. See the exemple sheet for updated layout.

ExampleSheet.xlsx

[windknife]

Try this,

Please Login or Register  to view this content.

[VatternKraftan]

Thank you very much everyone!

[windknife]

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.