Maintenance service for cars, problem of formula : missing conditions

Hi everyone,

This is my first post in this forum for excel, I'll try to be the most precise as possible regarding my problem !

I'm working on a project about the periodical maintenance service for cars. Let's take a quick example : every 15 000 kms, you have to change your oil filter in order to be safe on the road... From the point of view of the car seller, this service represents some sales/income because the customer has to pay it ( I don't pay attention to the warranty as well as the dealer margin). Of course, it's not the only piece that needs to be changed or repaired during the life cycle of that car... What I wanted to do is to find a general formula that computes the total sales of this maintenance, for all the pieces (oil filter, engine oil, fuel filter....) and assuming a mean of a regular distance in km per year of the customer. Ok, let's take this example with these variables :
Distance/year = 20 000 km ; A/C filter distance interval = 60 000 kms ; A/C filter selling price = 220 ; Air filter distance interval = 30 000 kms ; Air filter selling price = 340 ; Fuel filter distance interval = 45 000 ; Fuel filter selling price = 550

Here is how the car company should account the sales ( where 1 means one effective sales, so one maintenance and where 0 means no maintenance required) :
at 0 km = 0 ; at 20 000 kms = 0; at 40 000 kms = 1 for the air filter only ( indeed, we reached at least 30 000 kms for the air filter but the interval for the others is not met)
at 60 000 kms = 1 for the A/C filter (logic..) and 1 for the fuel filter ( because 60 > 45) and at that time the air filter doesn't need to be changed because there wasn't 30 000 kms performed since the last maintenance for this item, so 0.
at 80 000 kms = at that time, only the air filter has to be changed ( because 40 + 30 < 80 ) but the others stay unchanged because we have to start over everytime the count after one maintenance occurred. For the fuel filter, the next one will be at 100 000 kms. Indeed, we record the sales at the end of each period (here every 20 000 kms) and the maintenance has to start over for the next ones.

I created a general formula on Excel ( at first, I thought about VBA but it seemed less difficult on Excel) that I thought it was good for every case, every piece and stuff. But I realized that sometimes, it doesn't work properly.

= IF((dist 1 - dist 0) >= dist interval piece,1, IF(cell t-1 = 0, IF(dist t-1 + dist interval piece >= dist t,0,1), IF(MOD(dist t, dist interval piece)=0,1, IF(OR((dist t/ dist average year)>= ROUNDUP (dist interval piece/dist average year), (this last part will be performed for every multiple of the dist interval)....)1,0))). Here is a example from my excel sheet :

=IF(($H$82-$G$82)>=$J$16,1,IF(L87=1,IF(L82+$J$16>=M82,0,1),IF(MOD(M82,$J$16)=0,1,IF(OR((M82/$C$82)>=ROUNDUP($J$16/$C$82,0),(M82/$C$82)>=ROUNDUP(($J$16*2)/$C$82,0),(M82/$C$82)>=ROUNDUP(($J$16*3)/$C$82,0),(M82/$C$82)>=ROUNDUP(($J$16*4)/$C$82,0)),1,0)))).

This formula works for some pieces, and some of them not. I know why, it's about the sign >= before the ROUNDUP that should change according the piece.

I've also tried to do that kind of formula :

=IF($C$82>=$J$16,1,IF(AND((K104=0),(J82+$J$16 >=L82),(L82>=$J$16)),1,0)).

But, still, I can't find the way to establish the general formula that would calculate everything... It's why I'm writing this post, hopefully someone could help me to finish it If I haven't properly explained the formula or my situation, please feel free to ask me more details.

In advance, thank you!

Jeoffrey