How to use edit formula based on maximum limit

[Aggsneh]

Hi all,

Please refer to the attached file. I have calculated the fees based on a formula for 23 rows which totals to 46000. As a next step, I need to maximize it to 34000. My ask is, if total exceeds 34000, then fees should be zero.

Early response would be helpful.

Thanks

[Blake]

Hi @Aggsneh 

You'll probably want to summarize your fees first and then use a formula tool to write a conditional statement for your max fees. 

Thanks, let me know if this is what you're looking for. 

[atcodedog05]

Hi @Aggsneh 

You can do it by using summarize tool to get total fees and use below formula

IF [Total Fee]>34000 THEN 0 ELSE [Total Fee] ENDIF

Output:

Workflow:

Hope this what you are looking for.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Blake · Accepted solution]

Here is an alternative that uses a running total, formula, and multirow formula to keep your original data structure (kind of). 

[Blake]

@atcodedog05 - I could be wrong but I doubt that @Aggsneh wants to forego all fees if they reach 34000, I think they probably want to cap the total fees at that and make all future fees = 0. I would love it if I could get to a certain billable amount and have my entire amount due cancelled! 

[atcodedog05]

Hi @Blake 

I agree with you. That thought did come to my mind i was waiting for @Aggsneh to shed more light on it.

[atcodedog05]

Hi @Aggsneh 

Here is a slightly different approach.

Workflow:

Hope this helps 🙂

[Aggsneh]

Thanks so much Blake. 

This is what I requested.

[Aggsneh]

Thanks for helping me out.