CODEWITHSUNDEEP
excelexcel-formula
Pramod Pandit
Pramod Pandit

Reputation: 121

What must be changed in given formula to change feet & inches to meters for Non-Decimal numbers

The given formula works as expected for decimal numbers ie If my input is 8.3 (8 foot 3 inches) then the formula below works but it does not works for a Non-Decimal numbers.For eg:If my input is 4 only or even 4.0 then the VALUE error appears. What changes should be made on the formula such that it works for non decimal numbers too.

=(LEFT(B5,FIND(".",B5)-1)*12+SUBSTITUTE(MID(B5,FIND(".",B5)+1,LEN(B5)),"""",""))/39.3701

Upvotes: 1

Views: 92

Answers (2)

RetiredGeek
RetiredGeek

Reputation: 3168

Per Ron Rosenfeld above entering your values as 8.01 and 8.10 respectively. enter image description here

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

If you are entering the values as numbers, and are placing a leading zero in front of the single digit inches (e.g 8.03) then a formula to convert to meters:

=CONVERT(DOLLARDE(B5,12),"ft","m")

If you are entering them as text strings (and without the " sign), you can use:

=MAX(IF((LEN(B7) - FIND(".",B7&"."))={-1,2}, CONVERT(DOLLARDE(B7,12),"ft","m")),IFERROR(CONVERT(DOLLARDE(INT(B7) &".0" & MID(B7,FIND(".",B7)+1,1),12),"ft","m"),0))

There is no method of which I am aware that you can enter 8.1 as a number and have Excel know if you mean 8 ft 1 in or 8 ft 10 in

Upvotes: 1

Related Questions