0

I have a cell with data validation. The source of the validation is a long range of decimals in 0.0078125 (1/128) increments.

The cell containing the validation is formatted to display fractions # ???/???

The validation drop down displays decimals 0.000000

How do I make the drop down list format the decimals as fractions?

How do I format the drop down list to display fractions?

Edit to add clarification images.

Formatted cell displaying fractions:

Formatted cell and menu not displaying fractions when drop down menu activated:

I would like to have these displayed as fractions.


0

To display fractions in the dropdown, you need to convert the decimal/formatted value to a text value.

For example:

=text(A1:A5,"# ???/???")

If the resulting dropdown value is to be used in a calculation, then you need to convert it back to a number.

For example:

=left(B10,search("/",B10)-1)/right(B10,len(B10)-search("/",B10))


SAMPLE

Dropdown


3
  • That does the trick, thank you!
    – Anthony
    Jul 13 at 16:56
  • I noticed mixed fractions tend to have additional spaces in their formatting: #[space][space]???/???[space] This becomes evident when passing the value to a function and converting to decimal. I stripped out the additional spaces using regex, but do you know why they appear and how to prevent it?
    – Anthony
    Jul 14 at 16:27
  • I'm Learning more each day. To address the extra spacing in my fractions (comment above), I learned that # ?/??? is an acceptable format string and will display the numerator w/o prepending any padding. 1 1/128 vs 1 1/128.
    – Anthony
    Jul 16 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.