Dependency error in a formula column

Platform discussions
1

I have a very simple formula here.

IF(AND({TotalTime}<=1.5,{TotalTime}>1),((1.5*{Rate})+{Extra Cost}),
IF(AND({TotalTime}<=2,{TotalTime}>1.5),((2*{Rate})+{Extra Cost}),
IF(AND({TotalTime}<=2.5,{TotalTime}>2),((2.5*{Rate})+{Extra Cost}),
IF(AND({TotalTime}<=3,{TotalTime}>2.5),((3*{Rate})+{Extra Cost}),
IF(AND({TotalTime}<=3.5,{TotalTime}>3),((3.5*{Rate})+{Extra Cost}),
IF(AND({TotalTime}<=4,{TotalTime}>3.5),((4*{Rate})+{Extra Cost}),
IF(AND({TotalTime}<=4.5,{TotalTime}>4),((4.5*{Rate})+{Extra Cost}),0)))))))

The whole idea of the formula is to round up the decimal number inputted in the “TotalTime” field to the next half or whole number and multiple the rounded number by another number (Rate) then add “Extra Cost” if such is applicable. It’s simple and it works.

The problem comes when I try to add more than 7 IF conditions, For example, the next one:
IF(AND({TotalTime}<=5,{TotalTime}>4.5),((5*{Rate})+{Extra Cost})
IF(AND({TotalTime}<=5.5,{TotalTime}>5),((5.5*{Rate})+{Extra Cost}) and so on…

It returns me the following error: “TotalTime” creates a circular dependency.

I don’t get why this is a thing after the 7th IF condition and why it creates a circular dependency.

Have anyone occurred such an error?

1 Like
2

Hey @vas
Try converting your formula into a SWITCH control.

SWITCH(
  TRUE,
  AND({TotalTime} <= 1.5, {TotalTime} > 1),
  1.5 * {Rate} + {Extra Cost},
  AND({TotalTime} <= 2, {TotalTime} > 1.5),
  2 * {Rate} + {Extra Cost},
  AND({TotalTime} <= 2.5, {TotalTime} > 2),
  2.5 * {Rate} + {Extra Cost},
  AND({TotalTime} <= 3, {TotalTime} > 2.5),
  3 * {Rate} + {Extra Cost},
  AND({TotalTime} <= 3.5, {TotalTime} > 3),
  3.5 * {Rate} + {Extra Cost},
  AND({TotalTime} <= 4, {TotalTime} > 3.5),
  4 * {Rate} + {Extra Cost},
  AND({TotalTime} <= 4.5, {TotalTime} > 4),
  4.5 * {Rate} + {Extra Cost},
  0
)

#elegantprogramming

source

3 Likes
3

Or even more elegant.

IF(
  {TotalTime} > 1,
  ROUNDUP({TotalTime} * 2, 0) / 2 * {Rate} + {Extra Cost},
  0
)

source-1

5 Likes
4

This returns “TotalTime” creates a circular dependency. Just as my solution.

5

Your second elegant solution is rounding the numbers to the lower one, not the higher one. :confused:

6

My second solution uses the rounded down double and divides it again to get the 1.5, 2, 2.5, 3, 3.5, … figure you use in the multiplication.

7

Did you try it? It’s weird because there’s no circular reference in the formula.
It would be if you assigned formula result to {TotalTime}.

8

My idea is to round up the number to the higher one.
For example, if the output is 1.6, to round it up to 2 (not 1.5)
If the output is 2.2, to round it up to 2.5 (the higher half or whole number)

Yeah, I did try it your first solution. I’m assigning the formula to a completely new one, Not to {TotalTime}.
Again the problem occurs when I add the follow up of your first solution:
AND({TotalTime} <= 5, {TotalTime} > 4.5),
5 * {Rate} + {Extra Cost},
AND({TotalTime} <= 5.5, {TotalTime} > 5),
5.5 * {Rate} + {Extra Cost},
etc.

9

Just use ROUNDUP instead of ROUNDDOWN in my formula, sorry for the misunderstanding.

1 Like
10

You are a genius mate!
Thank you!!!

1 Like
11

This is awesome! Glad that y’all could find a solution :slight_smile:

2 Likes