Solved: Help to apply contract penalties

[rmrosa]

Hi,

in attach you can find in the blue area my doubt.

The problem is to calculate the downtime of the equiment taking in account that the first 225 minutes do not count for the penalty calculation.

please help me!!! It's very Important!!!

regards
RR

[Bob Phillips]

Your formula seems to calculate the 1:25 answer okay, so what is wrong with it?

I don't get what determines whether a penalty time goes to 1% or 2% PENALTY.

[Bob Phillips]

Looking at it some more, I am getting more confused. Why are you creating a running total of times.

It would be best to tell us how many hours should be 1%, how many 2%, and what the Euro cost of that is.

[rmrosa]

Hi,
If the equipment is down between 6:00 and 22:00, the rule is: for each 30 minutes there is a penalty of 1.666.67€ (55.56€ for each minute).
If the equipment is down between 22:00 and 06:00, the rule is: for each 30 minutes there is a penalty of 833.33€ (27.78€ for each minute).
You have to erase the formulas in the blue area and make a formula that gives us the correct values, taking in account that will be more than 1000 lines in my sheet. Of course that in the sample is just have a few records, so I made a simple formula by hand…and it's working...
Like I said, the problem is the 225 minutes that are included in the contract … just after 225 minutes (3:45h) of downtime I can start to calculate penaltys…
Thanks
RR

[Bob Phillips]

Yes but if there is 180m between 6:00 and 22:00 and another 180 between 22:00 and 6:00, does that mean that neither is counted because it is less than 225, or if not, how is the 225 apportioned?

Computers want absolutely precise instructions.

[rmrosa]

Now I understand your question… I didn't explain me well... sorry!!!
The 225 minutes is accumulated, it’s valid for any time of the day, it means that if you have 225 minutes of downtime between 6:00 and 22:00… a record of a downtime at 22:01 will start to count for penalty’s…
The 225 minutes do not take in account any period.... that's why is more dificult for me to make an formula...
I hope this help you...
Thanks

[Bob Phillips]

Nope, still not there yet RR.

As I said before, using your example workbook, it would be best to tell us how many hours should be 1%, how many 2%, and what the Euro cost of that is.

[rmrosa]

In the best scenario the downtime should be zero, because I want the equipment always working… My inputs are the downtimes (Colum B and C) and the outputs should be the penalty’s that I can apply… In the blue area, I just want the number of hours that I can apply the penalty for each interval…
I’m not understanding what is the relevance to know [FONT='Verdana','sans-serif']”(…)how many hours should be 1%, how many 2%)(…)”…[/FONT]
[FONT='Verdana','sans-serif']Never the less, about the cost, the sheet has it… but I already told: [/FONT]
If the equipment is down between 6:00 and 22:00, the rule is: for each 30 minutes there is a penalty of 1.666.67€ (55.56€ for each minute). [FONT='Verdana','sans-serif']
[/FONT]If the equipment is down between 22:00 and 06:00, the rule is: for each 30 minutes there is a penalty of 833.33€ (27.78€ for each minute).
Thanks and regards
PS: I will upload the file again... maybe there is a problem with the file...

[rmrosa]

Helps to say that the 225 minutes are monthly??? It's not 225 minutes for each downatime, it's 225 minutes aloowed in the total of the month....

[Bob Phillips]

I don't know, you aren't telling me anything, so I will take one guess at it

C14: =MAX(0,SUM(D5:D11)-"03:45")*24*K7
D14: =MAX(0,SUM(E5:E11)-"03:45")*24*K8

[rmrosa]

One last try... I'm giving to much work...

What I want is just a formula that calculates automatically the blue area...

Please see my new attach

Thanks
RR

[Bob Phillips]

You are still confusing the hell out of me.

In this latest workbook, you have an accumulated downtime of 14:10. Take of th 225 mins of contract time gives a total penalty time of 10:25. Yet your example shows 2:55 for 2% and 10:25 for 1%, a total penalty time of 13:20.

It just does not compute.

[Bob Phillips]

Here's another shot, totally ignoring your suggested values

G5: =IF(SUM($F4,D5)>--"03:45",MIN(SUM(D$5:D5),$F4+D5-"03:45"),0)
H5: =MAX(0,F5-"03:45"-G5)

and copy down.

[rbrhodes]

Wow. Well I tried but can't get a dependale formula yet.

Is a VBA solution acceptable?

[rbrhodes]

Hello RR,

[EDITED]

Took another look and I believe I got it. Needs Col E and Col G inserted as 'helper' Columns. The formulas can be copied down the Cols.

Note: If these are 'unsightly' then just hide the Columns. (You could move them that that entails adjusting all the cell references in the formulas...)

Col E formulas keep a running total of 2% downtimes and Col F tracks 1% hours. This is simplification for the formulas as it's one less calculation they must do.

Cell I5 is a simple formula that exists in that cell ONLY. It simply checks if the first entry has filled the minimum. Cell I6 is a more complex formula that needs to be copied down all rows. It checks if the minimum has been filled by any entries before itself and calculates accordingly.


Cell J5 is a simple formula that exists in that cell ONLY. It simply checks if the first entry in Col I or whether it has filled the minimum. Cell J6 needs to be copied down all rows.


Note: Round(Cell_Value, 5) is needed for Excel's inevitable time calculation errors (because .000000000000000000017000 is > 0)


I tried every scenario I could think of as I bult and tested this (many hrs!) and this version appears to be dependable. Let me know if you can break it (using valid dates/times as I know it will choke on bad dates/times).

** Fixed a few things after I posted... Attached Version 2 is latest.

[rmrosa]

HI,

thanks a lot for your effort... But... I cannot open your attach... I get the message:

"To view attachments your post count must be 0 or greater. Your post count is 0 momentarily"... Do you know what is the problem? Can you help me?

RR

[rbrhodes]

I think that happens if you aren't logged in, just viewing...

[rmrosa]

Hello!!!!

It Works!!!! I tried several scenarios and seems perfect!!!

If i find some error I will back to the forum...

THANK!!!

RR

[rbrhodes]

Cool