formula in numbers

What happens is that the OP formatted his total inches cell like this

In the next cell, he used the plain number 249 and converted it to a string to make feet and inches with the formula he showed.

Unfortunately, Numbers "helpfully" (cough!) carried over the formatting of the source cell and applied it to any part that was still a number, the 20 and the 9.

As I'm writing this the solution became obvious. The numbers have to be made as strings to avoid this formatting to be carried over.

=FIXED(TRUNC(A9÷12),0)& "' "& FIXED(MOD(A9,12),0)& """"

So easy when we understand what happens under the hood.

This appears to work:

=TRUNC(VALUE(A3)÷12)& "' "& MOD(VALUE(A3),12)& """"

I don't understand why you have to give a custom format to your result. Just set it to automatic.

OK, I get it:

Why would you put an = in a cell? Isn't it obvious that it's a total? But the repetition of " is annoying, yes. I wonder why Numbers does that.

You can take take of the last one by shortening the formula:

TRUNC(A3÷12)& "' "& MOD(A3,12)

The middle one, hmmmm...

Hi Cristelo,

I do not understand why you are using a custom format. The concatenate operator (&) will insert an equals (=) symbol at the beginning of the result.

formula in B3: "= "&INT(A3÷12)&"' "&MOD(A3,12)&""""

I used the INT function instead of the TRUNC function. It seemed more logical to me.

Regards,

Ian.

If you insist on your presentation, you'll have to add one or two intermediate steps to wash out the format of column A.

You could also invert the results: plain 249 is the main result, with = 249" and 20' 9" derived from it. It depends on how you get the original value

Hi Cristelo,

Your question took me back to Junior School where we had to work with Base Twelve (12 inches in a foot, 12 pence in a shilling), therefore Carry 12 instead of 10.

I do not understand your overall aim. We can not see what you see, but a screen shot will help.

I see two ways. Which way do you want to go?

1. Table 1 uses separate input columns for Feet and Inches, adds them, and then converts the total to Feet Inches.
2. Table 2 uses a single column for Feet Inches, then pulls the values into separate Feet and Inches columns, converts them to Inches, adds them, then converts the total to Feet Inches.

A screen shot of your data will help us to understand.

Note: I suggest that you use two single quotes ('') instead of a double quote (") as the inches symbol. I had a lot of trouble in Table 2 with (hint, hint) TEXTBETWEEN and a double quote.

Happy Numbering!

Ian.