Cell Format: Fractions vs Number

[Fujirich]

Seems like forever since I been working on an Excel project that brought me here to ask a question, but here goes...

I have a problem with cells changing their number format after the first entry.

I have two columns set up for the user to input a measurement, and then immediately to the right of each, the next cell is a validated list of measurement types: in, ft, mm, cm.

The idea is that the user enters the dimensions of the item and then picks the measurement type. Maybe it's 31 1/2 in x 40 9/16 in if using imperial measures. But they might also use metric. In that case, it might be 77.47 cm x 103.03 cm. Both the numbers entered and the measurement type are then used to figure out the area of the item in question. Simple!

But here's the problem: if the user first entered a fraction, Excel changes the cell format from general to fraction. If the user goes back and decides they want to now enter a measurement in metric format, the cell format remains in fractions, making entry problematic (anything after a decimal will be converted to a fraction).

I'm not trying to convert the entered number upon changing the measurement type (nothing that fancy). Just trying to figure out how to handle the formatting of the cell with the measurement number so that it can accept both type of entries and not get forced to one style.

Any thoughts?

 

[cstimart]

Not sure if you use VBA/macros, but have you thought about using an InputBox to get the information from the user, then formatting the resulting number in the cell(s) accordingly?

 

[Fujirich]

Actually, I had not thought about that approach.

It's a good idea. It might be more involved than I was hoping for on this fairly simple project, but perhaps a good course of action. Thanks!

I'm hoping something better exists, but that might be my solution.

 

[Joyson]

Could you help me, plz. I need an Excel formula that will allow me to figure out a fraction from the total. It should count cells E.g.
cell A1 1
cell A2 0
cell A3 1
cell A4 1
cell A5 3/4
total should be 3 out of 4 or 3/4

thank you very much!

 

[cstimart]

Could you help me, plz. I need an Excel formula that will allow me to figure out a fraction from the total. It should count cells E.g.
cell A1 1
cell A2 0
cell A3 1
cell A4 1
cell A5 3/4
total should be 3 out of 4 or 3/4

thank you very much!

=Sum(A1:A4) & "/" & Count(A1:A4)

should to the trick, then just format the cell for fractions.

 

[Joyson]

=Sum(A1:A4) & "/" & Count(A1:A4)

should to the trick, then just format the cell for fractions.

Thank you very much! it works perfect!