Strange error, when calculating by formula

[shalmaxb]

I get a strange calculating error, when I multiply two fields.

I use the calculation to calculate the price for cost, based on time used and hour rate. I want to be able to calculate the cost based on the actual minutes, which were necessary to do the work.

I have these fields:

hourRate currency field (manual input) tried also with a float to exclude error because of currency, same error
minutes integer (manual input) tried also with float, same error
hours float (calculated)

result currency (calculated) tried also with float, same error

no rounding, float has two digits decimal.

my formula:

1. Calculate the hours as decimal based on minutes

hours = minutes/60;

2. Multiply the hours value with the hourRate

result = hours * hourRate;

The result is correct, if hours is full hour (1, 2 etc.). As soon as minutes calculate an hour with decimal fractions, the result is exactly 0,13 more than should be, so if the result calculated correctly would be 79,04 it results in 79,17.
I already tried with number\format but that function has no effect at all in this case.

Edit:
Tested in the demo version of espoCRM, same error. Nothing in error-log.

[rabii]

Hey shalmaxb,

I have had same experience before and this is normal output, the reason why is that you calculate the result of all hours based on same rate which is wrong. what you need to do is to have another variable that would hold the minuteRate (currency) and then you need to calculate hours out of the minutes and calculate the rest of the minutes left, after that you can calculate the rates and group the result, something as below should work, play around with this in a sandbox and then apply if you are happy:

$hourRate = 42.8;

// calculate minuteRate based on the hour rate
$minuteRate = number\round($hourRate/60 , 2);

// Given this is the number of minutes worked
$minutes = 345;

// calculate the exact number of hours
$hours = number\floor($minutes/60);

// calculate the remaining number of minutes
$remainingMinutes = $minutes % 60;

// calculate final result
$result = ($hours * $hourRate) + ($remainingMinutes * $minuteRate);

output\printLine(string\concatenate('Hour rate:  ', '£ ', $hourRate));
output\printLine(string\concatenate('Minute rate:  ', '£ ', $minuteRate));
output\printLine(' ');
output\printLine(string\concatenate('Calculated hours:  ', $hours));
output\printLine(string\concatenate('Remaining minutes:  ', $remainingMinutes));
output\printLine(' ');
output\printLine(string\concatenate('Hours cost:  ', '£ ', ($hours * $hourRate)));
output\printLine(string\concatenate('Minutes cost:  ', '£ ', ($remainingMinutes * $minuteRate)));
output\printLine(' ');
output\printLine(string\concatenate('Total value:   ', '£ ', $result));​ 

Hope this makes sense, i have tried different variation of numbers and always the result is correct.

Cheers

[shalmaxb]

Well, I researched further.

When I do a calculation by formula I always confirm that by using an external calculator. So the calculator gave me a result by 0,13 digits less.

Meanwhile I know why. espoCRM calculates completely correctly. The error appears, because I limited the decimals to two, when calculating the hours based on minutes. What I did not get was, that in fact this value will not be calculated internally by only two decimals, but by more (I tested with 8 decimals). That reveals, that dividing the minutes by 60 to get the hour, the result may differ quite a bit. Here an example:

125 minutes result in 2,08 hours displayed with two decimals. With 8 decimals it will be 2,08333333. Even not displaying all these decimals, espoCRM calculates correctly with all these.
As I entered in my external calculator only 2,08 hours when multiplicating with the hour rate, obviously the result would differ. I took the (not so exact) result from the calculator as granted and thought, espoCRM would do something wrong. As soon as I entered in the external calculator also 2,08333333, the result would be the same as from espoCRM.

Conclusion: espoCRM does not calculate wrong, even the calculator does not, when feeded the right numbers.

The only issue would be, if a customer takes the printed hours of 2,08 and multiplicates (as I did before), he will get a differing result and I will have to explain that. Or I must modify the formula in a way, that in the end espoCRM discards the six repeated decimals to calculate with two decimals actually.

Edit:
I solved that last issue by introducing another field "Quantity", where I copy the calculated hours rounded to actual two decimals, wich in the above example leads to 2,08, which I the use multiplicating with hour rate to calculate the price for the service:

quantity = number\round(hours, 2);