Results 1 to 2 of 2

Thread: If with other equations

  1. #1

    If with other equations

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am pretty new at excel so I hope this is a basic question. I work in an engineering department where we are given a "Max Span" and calculate a "Max Overhang". The Max Overhang=(Max Span/3). So if we are given a Max Span of 8' the Max Overhang is 2'-8". Now to my knowledge excel doesn't have the ability to have Feet and Inches as your unit choice. So instead of 2'-8" excel spits out 2.66666. First is there a way to change the cell to display "2.6666" to "2'-8""?

    If that is not possible I am trying to have the Feet come out in one cell and the Inches come out in another.

    Her is my set up:
    Inputs: Outputs:
    Max Span 8 Feet 2
    Max Overhang 2.667 Inches 8

    I created a formula that completes the above function: =(RIGHT(F5,LEN(F5)-FIND(".",F5)+1))

    However, it only works if there is decimal, otherwise it says "#VALUE!". So for example is my Max Overhang was 6 feet my function looks like this:

    Inputs: Outputs:
    Max Span 6 Feet 2
    Max Overhang 2 Inches #VALUE!

    In the above example is should say "0" for inches.

    To do this I am trying to write an IF function but I am having trouble writing the formula. I want to say IF the max overhang contains a decimal, multiply the numbers to right of the decimal by 12, if the value does not have a decimal this cell equals zero.

    Hopefully my question was not too confusing.

  2. #2
    This is based on max overhang decimal calculation being in cell F5 . This formula will convert your decimal # to feet and inches.

    =INT(MROUND(F5,1/96))&"' "&TEXT(12*MOD(MROUND(F5,1/96),1),"# #/#")&CHAR(34)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts