Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MLK MLK is offline
external usenet poster
 
Posts: 81
Default Help please... Simple calculation - with unexpected results

I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Help please... Simple calculation - with unexpected results

I get the answer that you expect (Excel 2000). Check what is in cells
A2 and B2 - if these contain formulae then they may result in values
like 100.2 or .442, but your formatting does not allow you to see the
extra digits. To ensure that your formula returns the correct result,
you could amend it thus:

=ROUND(A2,0)*50*37.5*0.85*ROUND(B2,2)

I've removed the brackets around the numbers as they are not needed.

Hope this helps.

Pete

MLK wrote:
I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Help please... Simple calculation - with unexpected results

There is a difference between what is STORED (for example 0.4396) and what
is DISPLAYED (example 0.44)
You can EITHER
1) use Tools | Options , open Calculations tab and check Precision as
Displayed
(but be aware it will affect other workbook that you open)
OR
2) tailor the formula to 2 decimals as in =ROUND(A2,2)*
50*37.5*0.85*ROUND(B2,2)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MLK" wrote in message
...
I am getting unexpected results from a simple multiplication formula.
Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be
calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Help please... Simple calculation - with unexpected results

On Fri, 28 Jul 2006 15:24:01 -0700, MLK wrote:

I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.


Excel calculates to 15 decimal places. Unless you check Precision as Displayed
under Tools/Options, the formatting will not change that.

Given:

A2: 100.004987921697
B2: 0.44228564109933

you would see 100.00 in A2, 0.44 in B2, and get the results you report in C2.

If you only want to calculate using two decimal places, you must either use
Precision as Displayed (which may have unexpected results elsewhere in your
workbook), or round the factors in A2:B2 to two decimal places.

You can either use the ROUND function within A2 and B2, or use it in C2:

=ROUND(A2,2)*(50*37.5*0.85)*ROUND(B2,2)

depending on what you want.

If you will only want two decimal precision in A2 and B2, then I would do the
rounding there.
--ron
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MLK MLK is offline
external usenet poster
 
Posts: 81
Default Help please... Simple calculation - with unexpected results

Sorry, I forgot to add that .44 is the result of a calculation in Col B. Col
B in the example below is 23/52. This is how I figure the 7 decimals is
coming into play, but not sure how when I'm only displaying 2.

"MLK" wrote:

I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MLK MLK is offline
external usenet poster
 
Posts: 81
Default Help please... Simple calculation - with unexpected results

Yes! The rounding works perfectly! Thanks so much.

"Pete_UK" wrote:

I get the answer that you expect (Excel 2000). Check what is in cells
A2 and B2 - if these contain formulae then they may result in values
like 100.2 or .442, but your formatting does not allow you to see the
extra digits. To ensure that your formula returns the correct result,
you could amend it thus:

=ROUND(A2,0)*50*37.5*0.85*ROUND(B2,2)

I've removed the brackets around the numbers as they are not needed.

Hope this helps.

Pete

MLK wrote:
I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MLK MLK is offline
external usenet poster
 
Posts: 81
Default Help please... Simple calculation - with unexpected results

Thanks for all the info, I hadn't realized the underlying decimal situation.
Never came across it before. Anyways, using Round is the solution.

Everyone has been very helpful. Thanks again.

"MLK" wrote:

I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Help please... Simple calculation - with unexpected results

Glad to be of help - thanks for feeding back.

Pete

MLK wrote:
Yes! The rounding works perfectly! Thanks so much.

"Pete_UK" wrote:

I get the answer that you expect (Excel 2000). Check what is in cells
A2 and B2 - if these contain formulae then they may result in values
like 100.2 or .442, but your formatting does not allow you to see the
extra digits. To ensure that your formula returns the correct result,
you could amend it thus:

=ROUND(A2,0)*50*37.5*0.85*ROUND(B2,2)

I've removed the brackets around the numbers as they are not needed.

Hope this helps.

Pete

MLK wrote:
I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
A simple date calculation question. Seldum Excel Discussion (Misc queries) 2 June 26th 06 04:56 PM
The ispmt function is providing unexpected results Louis Zaffino Excel Worksheet Functions 1 May 3rd 06 03:56 AM
Mortgage calculation after a large extra payment Gary Wachs Excel Discussion (Misc queries) 6 April 16th 06 08:55 AM
Mortgage calculation after a large extra payment Gary Wachs Excel Worksheet Functions 4 April 16th 06 02:01 AM
need explanation on simple time calculation Georgina Excel Discussion (Misc queries) 1 April 2nd 06 02:36 PM


All times are GMT +1. The time now is 03:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"