Formulas Not Calculating Decimals

Filbert70

New Member
Joined
Nov 26, 2018
Messages
2
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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.
 
Last edited:

Filbert70

New Member
Joined
Nov 26, 2018
Messages
2
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,048
Messages
5,466,253
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top