Formula for Cents In Google Sheets

Um, {Cents}/100 = {Dollars}?

I’m not quite sure where the format mismatch is occurring. If you need to pass it as a text string to Sheets — ‘'$0.05'’ — then there’s code in my pretty-print routines you can snag. Or if you already have the cents’ values stored as a two-character numeric string (which ‘05’ would imply), you could simply concatenate: '$0.'&{Cents}. That might be enough to kick Sheets into storing it as a currency value…

Or am I barking up a totally wrong tree?

Hi @W_Vann_Hall

Thank you for your reply.

No, you are not barking up the wrong tree.

It’s the formula that I need, not the mathematical version or text format.

I will try the formula and hope for the best.

It’s great to see you back.

Take care.

Thank you for your help.

Mary

P.S. Will this formula work in one column in Google Sheets?

Hi @W_Vann_Hall

I wondered if there might be a formula for the cents, similar for GS, but for Airtable. I thought I would try either one or the other approach.

Thank you,
Mary

Mary -

Sorry – that was the Airtable formula; I thought maybe adding a calculated text field to the base and exporting it rather than your current {Cents} field to Google Sheets might cause it to interpret the currency value in cents rather than dollars…

Hi @W_Vann_Hall

Thank you for your help with the cents formula.

I set up the formula field, but I am noticing that the output doesn’t quite look right. I have attached a screenshot to illustrate the situation better. Although, I think I should have also mentioned that the formula needs to cover dollars, too.

Formula:
‘$0.’&{FACE VALUE}’

Thank you,
Mary

OK, it seems like your {Cents} field is (a) numeric and (b) can be higher than 99. In that case, try this:

IF(
    {Cents},
    '$'&
      INT(
          {Cents}/100
          )&
      '.'&
      REPT(
          '0',
          2-LEN(
              MOD(
                  {Cents},
                  100
                  )&''
              )
          )&
      MOD(
          {Cents},
          100
          )
    )

If you need to get fancier (not for your current base, but later on), there’s always this.

Hi @W_Vann_Hall

I think I gave a follow-up to the cents formula too soon.

I created new records in Airtable using Integromat, from GS. I noticed that there was an added zero to the currency, in the formula field, in Airtable. My other field is a currency field. Could this formula work with a currency field and an adjustment to the formula or change the currency field to text and also, change the formula? Although, I would preferr to keep the currency field, since there are other sources that I will use data from, to create/update records in Airtable. It’s just this source of data that the currency is not formatted properly.

I should also mention that when I enter the currency, without decimals, directly into Airtable, the currency is formatted correctly. It’s when I have the data imported, through Integromat, using the currency without added decimals (source data), that I get the added zeros. Could it have to do with the currency format in GS? The GS format is at zero decimals and the cell format is automatic. That’s the only difference that I could think of, that’s different.

The currency field for Airtable is set at 2 decimal places with a dollar currency symbol and it allows for negative numbers.

I have attached screenshots to better explain the issue. Disregard the left side screen in the third screenshot.

Thank you for your help and patience.

Mary