Help

Greater than But Less than Formula

Topic Labels: Formulas
541 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Viado
4 - Data Explorer
4 - Data Explorer

I have a field with the number of days, and the total fee. I wanted to create a formula field where I could add this logic below. Is this possible to compute?

 

One month or less (≤ 30 days)

10% of the Fee

2-3 Months (31-90 days)

20% of the Fee

4-6 Months (91-180 days)

50% of the Fee

7-8 Months (181-240 days)

60% of Tof the Fee

9-10 Months (241-300 days)

75% of of the Fee

2 Replies 2
Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

Hi Viado! While I'm sure there's a more streamlined way to do this, I just made this out as a long nested IF(). In this example, I've assumed "50" as the fee value, although yoi could replace this with a field instead for variable fees. Hope this helps!

IF(
  {Count Days} <= 30,
  0.1*50,
  IF(
    AND(
      {Count Days} <= 90,
      {Count Days} >= 31
    ),
    0.2*50,
    IF(
      AND(
        {Count Days} <= 180,
        {Count Days} >= 91
      ),
      0.5*50,
      IF(
        AND(
          {Count Days} <= 240,
          {Count Days} >= 181
        ),
        0.6*50,
        IF(
          AND(
            {Count Days} <= 300,
            {Count Days} >= 241
          ),
          0.75*50,
          IF(
            {Count Days} >= 301,
            1*50
          )
        )
      )
    )
  )
)
Sho
11 - Venus
11 - Venus

Hi @Viado and @Josh_Colina 

If they were nested, there would be no need for the >= line.

IF({Days} <= 30,
  0.1*{Total Fee},
  IF({Days} <= 90,
    0.2*{Total Fee},
    IF({Days} <= 180,
      0.5*{Total Fee},
      IF({Days} <= 240,
        0.6*{Total Fee},
        IF({Days} <= 300,
          0.75*{Total Fee},
          {Total Fee}
        )
      )
    )
  )
)

 It's better to have a simple formula!