Having a problem working with fractions, decimals, and dates. Trying to subtract two cells (formatted as decimals) from one cell formatted as a Pop-Up Menu using fractions. Numbers is defaulting to a date format when I need a decimal or fraction., H

Question

Level 1

5 points

Having a problem working with fractions, decimals, and dates. Trying to subtract two cells (formatted as decimals) from one cell formatted as a Pop-Up Menu using fractions. Numbers is defaulting to a date format when I need a decimal or fraction., H

Trying to subtract two cells formatted as numbers (decimals) from one cell formatted as a Pop-Up Menu using fractions. The only results that are correct are where I have selected a two digit numerator over a two digit denominator, such as 11/16. If I choose a single digit numerator over a two digit denominator (9/16) or both single digit (7/8), I get a blank cell. I get the same result when trying to Index and Match the same Pop-Up cell to it's decimal equivalent. Any help?

Posted on May 25, 2018 8:02 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,276 points

May 25, 2018 8:41 AM in response to Glazier1627

I can't reproduce that problem here:

To populate the pop-up menu, I first formatted A2:A4 as 'Fraction' and entered the fraction. Then I selected A2:A4 and chose the 'Pop-up Menu' data format.

Formula in C2, filled down, is:

=A2−B2

SG

Reply

Answer 2

Badunit

Level 7

25,796 points

May 28, 2018 6:57 PM in response to SGIII

I created a popup the old fashioned way and all my "fractions" are treated as dates, just like the OP. For example, 1/4 - 1 = 1/3. I created one the way you did (format cells as fraction, select them, format as popup) and they are treated like fractions. The two popups look identical but work totally differently.

I then created a popup the old way but instead of entering fractions like 1/4 I entered them as 1 / 4 (i.e., spaced out). It removed the spaces and turned them into fractions, which work in a table as fractions.

Reply

Answer 3

Glazier1627 Author

Level 1

5 points

May 28, 2018 7:14 PM in response to Badunit

Thanks, Badunit. Spacing the characters out solved the problem - partly, anyway. It solved the problem in the primary set of cells where I could control the input, like using a Pop-Up Menu. However, the problem still persists when I can't control how the answer is displayed, for example, subtracting two cells to get an answer in fraction form. Pre-formatting the cells didn't do it either.

Using fractions in a spreadsheet seems like such a simple thing but has cost me a tremendous amount of time trying to figure out how to use or get around using them. Very frustrating. I'm not a programmer, just a glass business owner trying to make, what I think, is a fairly simple spreadsheet but this has turned into an impossible task so far. Thanks for your help - any more ideas will be appreciated.

Reply

Answer 4

SGIII

Level 8

35,276 points

May 29, 2018 4:02 AM in response to Glazier1627

I’n curious, did you actually try the simple step described above of first formatting the cells as Fraction and then creating the Pop-Up Menu? That works well here. No need for extra spaces or other workarounds.

SG

Reply

Answer 5

Badunit

Level 7

25,796 points

May 29, 2018 4:12 AM in response to Glazier1627

I am not seeing this second problem. I can format answers as fractions once the pop up thinks it contains fractions (not dates). Maybe if you provide a specific example, showing that part of your table that is not working, I can try to replicate it here.

Reply