Formulas Not Calculating Decimals

[Filbert70]

I'm using Excel 2010 and saving my worksheet in Workbook Excel-Macro Enabled Workbook or Excel Workbook. Numbers with decimals will add/subtract,multiply and divide. However, formulas using Min/Max, IF, And, Median etc; do not return correct results, in this case "Y","N". Numbers with decimals default to "N". All cells are formatted as numbers with 3 decimal places left of the whole number. Auto Calculate is on. Help would be greatly appreciated.

 

[joeu2004]

It would be helpful if you provided a concrete example that demonstrates the problems.

But I wonder if part of the problem is: formatting alone changes the appearance, but usually not the actual value [1]. Your expectations of "correct" results might be due to misled by appearances.

Temporarily format cells so that they display 15 significant digits; that might be more or less than 15 decimal places. For example, 1.23456789012345 v. 1234.56789012345. But it would not hurt to display the most number of decimal places that is required for the smallest value.

If you provide a concrete example, be sure to post values with 15 significant digits.


-----
[1] The exceptiion is when the option "Precision as displayed" is set. I do not recommend setting that option. But if you, be sure to save a copy of the file first, because PAD sometimes changes the precision of constants permanently.

 

[Filbert70]

Thank for your response. Unfortunately changing format to 15 significant digits did not resolve the problem. I hope this example will help. Range: 143.264 - 145.689 Formula: =IF(H12=MEDIAN(K5:O5),"Y","N") where H12 (144.395) K5 (143.264) and O5 (145.689) are the cell values. The formula returns the correct result "Y" if the cell values are whole numbers and "N" for numbers with any decimals.

 

[joeu2004]

Formatting to display 15 significant digits was not intended to "resolve" the problem. It was intended to reveal the problem; at least, provide some insight. Maybe. The point is: perhaps what appears to be 143.264 is actually 143.2635 or 143.26449. Don't read that literally. That is just an example; wild speculation.

In any case, you do not provide sufficient information for me to help you, as I requested. K5:O5 is only 5 values. Why not provide them all? And be sure to show them (including H12) with 13 or more decimal places.

Why do you think the median should be 144.395? MEDIAN(143,146) = 144.5, not 144.395. MEDIAN(143,145) = 144, not 144.395. MEDIAN(143.264,145.689) = 144.4765, not 144.395. So surely you do not mean that they are the only values in the cells K5:O5.

Enter =MEDIAN(K5:O5) into a cell. What does it return? (With 13 or more decimal places.) Or use the Formulas > Evaluate Formula feature to see those values (maybe).

(Unfortunately, sometimes the Evaluate Formula feature does not show all the intermediate steps and values.)

 

[joeu2004]

PS.... I wonder if you are confusing AVERAGE with MEDIAN. Even so, you have not provided sufficient information to explain why you expect even the average(?) to be 144.395, whether with integers or with non-integers.

 

[joeu2004]

Formula: =IF(H12=MEDIAN(K5:O5),"Y","N") where H12 (144.395) K5 (143.264) and O5 (145.689) are the cell values. The formula returns the correct result "Y" if the cell values are whole numbers and "N" for numbers with any decimals.

If you want my help, please provide an example of whole numbers in K5:O5 with which MEDIAN(K5:O5) = 144.395, as well as an example of non-whole numbers in K5:O5 with which you expect MEDIAN(K5:O5) = 144.395, but it is not. That is two examples.

The only way that I can get MEDIAN(K5:O5) = 144.395 is if one value is 144.395, not a whole number, and two values are less and two values are more, be they whole numbers or not.