Skip to main content
Solved

Difference formula

  • August 31, 2022
  • 5 replies
  • 35 views
D
Forum|alt.badge.img+5

I’m hoping the community can help me out. I think that my formula is a bit off.

Here’s the logic I’m trying to do in the Difference field:

  1. If Baseline Value is empty, leave Difference empty
  2. If Baseline Value is 0, use the value in Target value
  3. If Baseline Value is NOT empty, calculate Target value minus Baseline value divided by Baseline value so I can get the percentage difference


As you can see, if Baseline Value is 0 or empty, there’s an error

My current formula:

IF({Baseline} != BLANK(),
IF({Baseline value} != BLANK(),
  (INT({Target value}-{Baseline value})/{Baseline value}),
  IF({Baseline value} = 0,
    INT({Target value}),
  BLANK()
  )
)

Best answer by kuovonne

IF(
  {Baseline value} & "",
  IF(
    {Baseline value},
    ({Target value} - {Baseline value}) / {Baseline value},
    {Target value}
)

5 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • September 1, 2022

Hey @Diane_Martinez!

Give this a shot:

IF(
    {Baseline},
    IF(
        {Baseline value} = 0,
        INT({Target value}),
        INT(({Target value} - {Baseline value}) / {Baseline value})
    )
)

I’m in the middle of working on your other post from earlier, so I’m online.
Let me know if you have any issues, or questions, or are curious to see what else you might want to do.

Edit:

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • September 1, 2022
IF(
  {Baseline value} & "",
  IF(
    {Baseline value},
    ({Target value} - {Baseline value}) / {Baseline value},
    {Target value}
)
D
Forum|alt.badge.img+5
  • Diane_Martinez
  • Author
  • Participating Frequently
  • September 1, 2022

Hey @Diane_Martinez!

Give this a shot:

IF(
    {Baseline},
    IF(
        {Baseline value} = 0,
        INT({Target value}),
        INT(({Target value} - {Baseline value}) / {Baseline value})
    )
)

I’m in the middle of working on your other post from earlier, so I’m online.
Let me know if you have any issues, or questions, or are curious to see what else you might want to do.

Edit:


Sorry, that came back with having errors

D
Forum|alt.badge.img+5
  • Diane_Martinez
  • Author
  • Participating Frequently
  • September 1, 2022
IF(
  {Baseline value} & "",
  IF(
    {Baseline value},
    ({Target value} - {Baseline value}) / {Baseline value},
    {Target value}
)

you absolutely rock @kuovonne
many many thanks

kuovonne
Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • September 1, 2022

Sorry, that came back with having errors


Just so that I can update my original reply with an edit, I was curious if your table has the {Baseline} field, or if it was supposed to be the {Baseline value} field.

IF({Baseline} != BLANK(),

When I tested it yesterday and configured it with the {Baseline} field, I returned this behavior:

Terms & ConditionsAccessibility statement