show " character after the formla calulates?

Posted by Jim on February 02, 2002 3:34 PM

Hi group,

Is it possible to show the " character in the calculated
result. i.e, =Sum(C7*12+D7)+P7*O7+T7*S7+W7 will
return 44, can it be shown as 44"

Thanks for any thoughts on this

Jim

Posted by Tom Urtis on February 02, 2002 3:41 PM

See if this suffices, it is actually two apostrophes together to simulate the appearance of ", with the two apostrophes surrounded by quotes for the purposes of the formula.

=SUM(C7*12+D7)+P7*O7+T7*S7+W7&"''"

Any help?

Posted by Jim on February 02, 2002 3:45 PM

another brilliant mind at work! Thanks,Tom (NT)

Posted by Jim on February 02, 2002 3:55 PM

Oops, returns 44.5" not 44 1/2" Any thoughts? (NT)

Posted by Tom Urtis on February 02, 2002 4:01 PM

Re: Oops, returns 44.5" not 44 1/2" Any thoughts? (NT)

Please give an example of what values you have in each of the related cells, and how you are attempting to format the final result.

Tom U.

Posted by Jim on February 02, 2002 4:12 PM

an example

Hi Tom,

entry to C7 will always be a whole number
entry to D7 can be a whole number or a fraction
entry to W7 can be a whole number or fraction

in my formula that i posted W7 was empty, which
it sometimes will be, but when i entered 3 1/2"
is when i notice the 44.5" not the desired 44 1/2"

Thanks,
Jim

Posted by Tom Urtis on February 02, 2002 4:59 PM

Re: an example

Jim,

Sorry, but I'm drawing a blank on this. When you see a fraction in a cell it is really stored as a decimal value (notice the corresponding value in the formula bar). It concatenates with text as a decimal, from what I can tell.

I learn something new on this board every day, and how to make a number continue to appear as a fraction while concatenated with text in the same cell will be what both of us learn today, or when someone who knows can help answer this. I bet we could whip up a UDF in VBA, but I am interested in knowing if this can be solved without VBA.

Anyone know?

Tom Urtis

Posted by Jim on February 02, 2002 5:07 PM

Re: an example

Hi Tom,

There are so many sharp people that post here,if
it's doable and if someone sees the q, i'm sure
well both learn something.

Thanks again,

Jim

Posted by Tom Urtis on February 02, 2002 10:11 PM

OK, I think this is your answer

Thanks to assistance from Dave Peterson, here are two solutions:

You can achieve this with the formula
=TEXT(SUM(C7*12+D7)+P7*O7+T7*S7+W7,"# ?/?")&"''"
which will treat the entire cell as text.

Or, if you want to use the formula number for future calculations, custom format the formula cell as
# ?/?"''"

Hopefully this does what you are asking.

Tom Urtis

Posted by Tom urtis on February 02, 2002 10:52 PM

Also see 19841.html

You can achieve this with the formula