Calculate Service Value According to Time

[deciog]

Gentlemen

I'm having difficulty putting together the formula to calculate the value by time of use of a particular service

Example: The first 30 seconds is charged at the rate of 0.20 and for every 6 seconds you are charged 0.20 rate

In the attached worksheet I show my formula as far as I can calculate, but I am not reaching all the correct values

Formulas only, not Macro

Thanks in advance

Decio

 

[Chihiro]

Your F column shows odd result...

31 sec calculated as...
=IF(E3>30,0.2+ROUNDUP((E3-30)/6,0)*0.2,0.2)
=0.2 + 0.2
= 0.4

However, for 60 second in F5. Using same formula, it should be...
=0.2 + 5 * 0.2
= 1.2

But it shows 1.00 as correct result... (though 60 seconds in E54 shows correctly as 1.20).

If F column is the correct value, there must be some other hidden criteria.
Or the values in C column when imported is rounded (ex: 59 Sec 999 milliseconds is rounded up to 60 sec) which causes result to be off by +/- 0.2 in some cases.

 

[vletm]

deciog
Something same as above ...
Are Your results correct?

 

[deciog]

Chihiro, vletm

Apologies, the correct value is 1.20 in the change to hit the results I missed the value

Obliged for observation

In the spreadsheet I put the two solutions that I thank, more examples, note that in lines 3, 4, 6, 7, 8, 9, 10, and 14 the values are not correct, this is my big problem

Personal excuses if I showed wrong examples again, a lot in the head.

Decio

 

[Chihiro]

But your result still does not make sense to me...

Take E3 for an example...
3682 - 30 = 3652
3652/6 ≈ 608
0.2 + 608 * 0.2 = 121.8
There's remaining 4 sec into next 6 sec, so additional 0.2
So the answer should be 122.0, but you have 121.80 as result...

As I've stated, there must be some logic/condition change if 121.80 is correct for F3.

 

[deciog]

Chihiro

You're right, there are seconds left, so your formula is correct.
I did not look this

Thank you very much

Decio