Incorrect calculation when using data from an already calculated cell

[ArgarLargar]

I am trying to do a simple Pro Rata calculation. $185 is paid for a
year of service. The service is cancelled during the year and money
is owed back to the customer. I am getting an error on what should be
a simple calculation.

Cell A18 is Number with 2 decimals 185.00
Cell A22 is Date 6/7/2007 as the start of the year of service
Cell A23 is Date 6/7/2008 as the end of the year of service
Cell A24 is Date 9/17/2007 which is when the service was cancelled.

Cell A25 is Number with 0 decimals 366. This is cell A23 - A22 to
calculate the number of days in the year of service. 2008 is a leap
year so this is 366.

Cell A27 is Number with 0 decimals 102. This is cell A24 - A22 to
calculate the number of days the service was used (6/7/2007 to
9/17/2007).

Cell A28 is Number with 0 decimals 264. This is cell A23 - A24 to
calculate the number of days the service was not used (9/17/2007 to
6/7/2008).

Cell A30 is Number with 3 decimals 0.721. This is A28/A25 to
calculate the pro rata amount of the unused service.

Cell A36 is Number with 2 decimals 133.44. This is the incorrect
result of A30 * A18. The correct answer should be 133.39. I should
see 133.39 if my calculation is A30*A18 but I see 133.44 instead.

Any ideas?

Thanks in advance, Argar Largar

 

[Don Guillett]

Round where desired.

 

[Niek Otten]

<Cell A30 is Number with 3 decimals 0.721. This is A28/A25 to calculate the pro rata amount of the unused service.>

This doesn't seem right.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Round where desired.
|
| --
| Don Guillett
| Microsoft MVP Excel
| SalesAid Software
| (e-mail address removed)
| | >I am trying to do a simple Pro Rata calculation. $185 is paid for a
| > year of service. The service is cancelled during the year and money
| > is owed back to the customer. I am getting an error on what should be
| > a simple calculation.
| >
| > Cell A18 is Number with 2 decimals 185.00
| > Cell A22 is Date 6/7/2007 as the start of the year of service
| > Cell A23 is Date 6/7/2008 as the end of the year of service
| > Cell A24 is Date 9/17/2007 which is when the service was cancelled.
| >
| > Cell A25 is Number with 0 decimals 366. This is cell A23 - A22 to
| > calculate the number of days in the year of service. 2008 is a leap
| > year so this is 366.
| >
| > Cell A27 is Number with 0 decimals 102. This is cell A24 - A22 to
| > calculate the number of days the service was used (6/7/2007 to
| > 9/17/2007).
| >
| > Cell A28 is Number with 0 decimals 264. This is cell A23 - A24 to
| > calculate the number of days the service was not used (9/17/2007 to
| > 6/7/2008).
| >
| > Cell A30 is Number with 3 decimals 0.721. This is A28/A25 to
| > calculate the pro rata amount of the unused service.
| >
| > Cell A36 is Number with 2 decimals 133.44. This is the incorrect
| > result of A30 * A18. The correct answer should be 133.39. I should
| > see 133.39 if my calculation is A30*A18 but I see 133.44 instead.
| >
| > Any ideas?
| >
| > Thanks in advance, Argar Largar
| >
|

 

[ArgarLargar]

Thanks Don. I'm using a new laptop and did not set precision display
in advanced options.

Thx.

 

[ArgarLargar]

Don,

Thanks for the tip. I'm using a new laptop and had not selected "Set
precision as displayed" on the Advanced options. I changed this and I
am seeing the expected results.

 

[Bernard Liengme]

Don did not suggest Set Precision as Displayed but rather the use of the
ROUND function. Many people have warned of the dangers of using the Set
Precision method since it applies to all open workbooks. But if you have
just a single use of Excel you should be OK
best wishes

 

[Niek Otten]

< it applies to all open workbooks.>

I don't think that is the case, Bernard

I have two workbooks open in Excel. I click Exit in the File menu. Excel asks if it should save changes for both workbooks.
I assume that means they are open in the same instance of Excel.
Still checking precision as displayed in one of the workbooks has no effect on the other


--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Don did not suggest Set Precision as Displayed but rather the use of the
| ROUND function. Many people have warned of the dangers of using the Set
| Precision method since it applies to all open workbooks. But if you have
| just a single use of Excel you should be OK
| best wishes
| --
| Bernard V Liengme
| Microsoft Excel MVP
| www.stfx.ca/people/bliengme
| remove caps from email
|
| | > Thanks Don. I'm using a new laptop and did not set precision display
| > in advanced options.
| >
| > Thx.
| >
| >> Round where desired.
| >>
| >> --
| >> Don Guillett
| >> Microsoft MVP Excel
| >> SalesAid Software
| >
| >
|
|