# Dependency error in a formula column

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

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

4 Likes

Or even more elegant.

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

5 Likes

This returns â€śTotalTimeâ€ť creates a circular dependency. Just as my solution.

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

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.

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}.

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}.
AND({TotalTime} <= 5, {TotalTime} > 4.5),
5 * {Rate} + {Extra Cost},
AND({TotalTime} <= 5.5, {TotalTime} > 5),
5.5 * {Rate} + {Extra Cost},
etc.

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

1 Like

You are a genius mate!
Thank you!!!

1 Like

This is awesome! Glad that yâ€™all could find a solution

2 Likes