Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula combining Convert and Round (2000 SR-1)

    Hello all Loungers

    This is a question from one of my work colleagues
    For example, the sum for the total of square feet in a column of figures is calculated by the function sum and the value is 150.
    The Sum formula is in A22
    In A22, he would like value to be converted and displayed as both square feet and square metres. Also the cell needs to show which is the ft and which is the mtr by showing added text.

    I suggested that he put in another cell the following formula

    =A22&" Sq ft"&"/"&(CONVERT(SQRT(A22),"ft","m")^2)&" Sq Mtr" which gives 150 Sq ft/13.935456 Sq Mtr

    So far so good except, that he would would like to round the Sq Mtrs to show 2 decimal places rather than 6 (Sigh and eyes glazing over). I am not sure how to proceed from here.

    And is there still a way of having the formula in the original cell of A22?

    <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula combining Convert and Round (2000 SR-1)

    This formula (in another call than A22) will do what he wants:<pre>=A22&" Sq ft"&"/"&TEXT(CONVERT(SQRT(A22),"ft","m")^2,"0.00")&" Sq Mtr"</pre>

    To have a formula in A22 itself, both occurrences of A22 in this formula should be replaced by the formula that currently is in cell A22 (without the equals sign =, though.) For example, if A22 now contains =SUM(A1:A20), the formula to replace it would become<pre>=SUM(A1:A20)&" Sq ft"&"/"&TEXT(CONVERT(SQRT(SUM(A1:A20)),"ft","m")^2,"0.00 ")&" Sq Mtr"</pre>

    Not very pretty, I fear.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula combining Convert and Round (2000 SR-1)

    WOW, you're right it isn't pretty, but still WOW

    Thank you

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula combining Convert and Round (2000 SR-1)

    Personally, I prefer (but that is the scientist in me):
    <pre>=A22&" foot

Posting Permissions

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