Forum Discussion

Durbin's avatar
Durbin
Copper Contributor
Jun 13, 2025

Custom number formatting help

Hello, I need some help/advice for displaying a value. 

 

Im scaling large recipe amounts, and at some point it'll become cumbersome to do '112 tbsps' and I'd prefer it read '7cups' after a threshold. Ive got a lot happening in an office script that I've made and Ideally I'd like to avoid further complicating the core formula of these cells. So I was looking at number formatting as a possible solution.

However, Im struggling to figure out if its possible. Right now I can obviously check for the thresholds, but the formatting itself doesn't seem to do any of the actual math. 

For example

[>=32] #/16  "cup(s)";# ?/? "tbsp"

just returns  1792/16 cup(s). Rather than divide the value, it scales it up by 16 which is... confusing. 

Could someone tell me what I'm missing? Or am I looking in the wrong direction here trying to use the number formatting and instead I should work in the CONVERT function into my cell formula? I'm hoping to avoid that, so I thought I'd ask for help. 

Thanks!

4 Replies

  • Custom number formatting, as any other formatting, doesn't change the value in the cell. If it is 112, fraction format correctly shows that value as 1792/16.

    Yes, with convert function like

    =IF( A1 >= 32, CONVERT( A1, "tbs", "cup"), A1 )

    where is another issue if you'd like to keep number in the cell, not texts. It's not clear how to define logic for custom number format for the result, e.g. if result is 2 it's not clear, that's spoons or cups.

    • Durbin's avatar
      Durbin
      Copper Contributor

      It seems as though that is my only way. And your explanation makes sense for the scaling. The reason I wanted to use the Number Format was so that the cell would stay the same value (112). There is a unit reference earlier in the recipe. The recipes scale into the thousands so its easier/better to keep the value consistent. For the sake of the user printing it out though I was hoping to display the next unit up ( qts to gallons, tbsp to cups, etc. ). 

       

      I appreciate the help and explanation! 

      Thanks

  • How about this:

     

    =IF(A1>=32, A1/16 & " cup(s)", A1 & " tbsp")

     

    • Durbin's avatar
      Durbin
      Copper Contributor

      Hi! I appreciate the help.

      Unfortunately this isn't quite what I am looking for. I was hoping a custom Number Format might be applicable. That way the display is masked but the values stay their original units. I've played with things further and I don't think its going to be possible without altering the formula itself. and converting back and forth mathematically similar to what you are getting at. I was just hoping that since the format was able to scale numbers upwards (the #/8 or ?/?) that it'd be able to also be able to divide them and let me convert with a mask. 

      Ah well, it was worth a shot! Thanks again for the help. 

Resources