Strange and Incorrect Excel Formulas Result

0 votes

I want to get the length of a formula result, but it returns incorrectly.

For example, I put 1005 in the cell A1, put the formula =(A1/1000-FLOOR(A1/1000,1))*1000 into B1 and the result is 5, that's correct. But when I put the formula =LEN(B1) into the cell C1, the result is 16? Even worse, the formula =REPT(0,3-LEN(TEXT(B1,"0")))&(B1) or =REPT(0,3-LEN(TEXT((A1/1000-FLOOR(A1/1000,1))*1000,"0")))&((A1/1000-FLOOR(A1/1000,1))*1000) returns 004.99999999999989??🤔

Apr 11 in Database by Edureka
• 13,640 points
68 views

1 answer to this question.

0 votes

Because the value in cell B1 may appear to be 5, but it is actually kept internally as 4.99999999999989 - and that number has 16 characters when evaluated as a string by LEN - =LEN(B1) returns 16. ().

You may observe this for yourself by pressing the "Increase Decimal" button several times:

enter image description here

The display ultimately gets to the point where it has enough decimal places to indicate the underlying value (the LEN function ignores trailing zeroes - again, those are display zeroes not stored zeroes).

You may achieve the same result by typing the following formula into a cell:

=1.005-1

This actually has 19 characters in it, not 16, because we have not multiplied by 1,000:

0.00499999999999989

answered Apr 11 by gaurav
• 22,040 points

Related Questions In Database

0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 13,640 points
784 views
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15 in Database by gaurav
• 22,040 points
655 views
0 votes
1 answer

Hours and minutes difference between two times in Excel

Using the TEXT function in Excel is ...READ MORE

answered Mar 25 in Database by gaurav
• 22,040 points
249 views
0 votes
1 answer

Excel COUNTIF with multiple criteria and both row and column

Count Cells Based On Text Value Using ...READ MORE

answered Mar 25 in Database by gaurav
• 22,040 points
349 views
0 votes
1 answer

How to read and write excel file

When broken down into steps, reading an ...READ MORE

answered Mar 25 in Database by Edureka
• 13,640 points
109 views
0 votes
1 answer

How to read and write excel file

When broken down into steps, reading an ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
82 views
0 votes
1 answer

Get month and year from date cells Excel

The steps to modify the date format ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
99 views
0 votes
1 answer

Generating username from first name and last name in excel

How to automatically merge first and last ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
1,645 views
0 votes
1 answer

Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
556 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP