Re: Formula for Cents In Google Sheets

M_k · ‎May 28, 2019

Hi

I would like to create a formula in Google Sheets, in one whole column.

I have a currency field that I would like to place the decimal in cents i.e:
$0.05

I would also like to include:
$0.01

Right now the format is:

05
(with no decimal place)

I tried to add the decimal as an option in both Airtable and Google Sheets, but it formats it into dollars. I want it in cents, and also dollars, too. I have over 200 cells/records, so I don’t want to have to do this manually, but rather automatically, either by a formula or another method.

I appreciate any help.

Thank you,
Mary

W_Vann_Hall · ‎May 28, 2019

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?

M_k · ‎May 28, 2019

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?

M_k · ‎May 29, 2019

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

W_Vann_Hall · ‎May 29, 2019

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…

M_k · ‎Jun 07, 2019

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

W_Vann_Hall · ‎Jun 07, 2019

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.

M_k · ‎Jul 07, 2019

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

Cameron_Remesz · ‎Feb 05, 2021