Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A simple date calculation question. | Excel Discussion (Misc queries) | |||
The ispmt function is providing unexpected results | Excel Worksheet Functions | |||
Mortgage calculation after a large extra payment | Excel Discussion (Misc queries) | |||
Mortgage calculation after a large extra payment | Excel Worksheet Functions | |||
need explanation on simple time calculation | Excel Discussion (Misc queries) |