Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / November 2007

Tip: Looking for answers? Try searching our database.

Incorrect calculation when using data from an already calculated cell

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ArgarLargar@gmail.com - 06 Nov 2007 18:11 GMT
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 - 06 Nov 2007 18:25 GMT
Round where desired.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>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
[quoted text clipped - 28 lines]
>
> Thanks in advance, Argar Largar
Niek Otten - 06 Nov 2007 18:42 GMT
<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.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Round where desired.
|
[quoted text clipped - 30 lines]
| >
| > Thanks in advance, Argar Largar
ArgarLargar@gmail.com - 07 Nov 2007 09:33 GMT
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.
ArgarLargar@gmail.com - 07 Nov 2007 09:32 GMT
Thanks Don.  I'm using a new laptop and did not set precision display
in advanced options.

Thx.

> Round where desired.
>
[quoted text clipped - 3 lines]
> SalesAid Software
> dguille...@austin.rr.com<ArgarLar...@gmail.com> wrote in message
Bernard Liengme - 07 Nov 2007 13:23 GMT
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
Signature

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.
[quoted text clipped - 8 lines]
>> SalesAid Software
>> dguille...@austin.rr.com<ArgarLar...@gmail.com> wrote in message
Niek Otten - 07 Nov 2007 13:42 GMT
< 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

Signature

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
[quoted text clipped - 13 lines]
| >> SalesAid Software
| >> dguille...@austin.rr.com<ArgarLar...@gmail.com> wrote in message

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.