I'm working on creating a report in Crystal reports that is summarizing customers that were activated and deactivated in the same year. My report is looking something like this:
<table border=1>
<tr>
<td>Customer</td>
<td>Create Date</td>
<td>End Date</td>
<td>Days Active</td>
<td>Months Billed</td>
<td>Invoice Amount</td>
<td>Average Invoice Amount</td>
<td>3 Year Lost Revenue</td>
<td>5 Year Lost Revenue</td>
</tr>
<tr>
<td>Customer 1</td>
<td>11/20/2014</td>
<td>08/05/2015</td>
<td>258</td>
<td>9</td>
<td>$2200.00</td>
<td>$244.44</td>
<td>$6600.00</td>
<td>$12466.67</td>
</tr>
</table>Basically the problem I'm having is the 3 year and 5 year lost revenue are not rounding properly.
I'll explain how I'm calculating everything and maybe someone can explain why those 2 columns are rounding weird.
Ok so customer, create date, and end date are all pulled from a database table.
Days Active = End Date - Created Date
Months Billed = Ceiling(Days Active/30)
For invoice amount I have a formula that basically says:
if isnull(column in the table I'm pulling from) then 0
else running sum total for the column with the Invoices in it
Average Invoice Amount = Invoice Amount / Months Billed
3 Year Lost Revenue = (36*Average Invoice Amount) - Invoice Amount
5 Year Lost Revenue = (60*Average Invoice Amount) - Invoice Amount
The numbers I used in my table for Invoice Amount, Avg Invoice and the 3 and 5 year revenue are actual numbers I'm getting. So the months billed, invoice amount and average invoice amount are all correct.
But as you can see
($244.44 * 36) - $2200 = $6599.84
($244.44 * 60) - $2200 = $12466.40
Both these columns are formatted the same too but somehow they're both rounding differently...any idea what I'm doing to make it round like this?
Thanks!
You likely forgot to round intermediate value of Average Invoice Amount. 2200/9=244.4444444..., but your manual calculation uses just 244.44 - sure results will differ.