# Excel Formula Question

May 28, 2021 1:04 PM Subscribe

I am trying to set up a spreadsheet that shows various outcomes based on a formula I can speak or write, but cannot get my "=IF" and "AND" functions to fully calculate. I get a message "Too many criteria in formula".
I can do this calculation by hand pretty easily, but I want Excel to do it so I can change the parameters without having to recalculate by hand.

I also think I am overcomplicating the formula. What I want to calculate is allocation of profits. Here is what I can say in plain (hopefully) English: If the P&L is less than zero (losses) then 100% of the allocation goes to Group B. If the P&L is between $0 and $120,000 then 100% of the allocation goes to Group A (0% to Group B). If the P&L is over $120,000, Group B gets 50% of the profits up until they have reached a 40% return on their investment. The rest goes to Group A.

I have set up what I call an assumption area with the numbers that can change (investment, profit percentage, amount of high water ($120,000 in my example above)) and three columns, Total Profits, Group A allocation, and Group B allocation. If I can come up with a formula for either group A or Group B, then the other Group is a simple subtraction of the known or calculated group from the total P&L column. I think. I have tried using IF and AND formulas but have run into issues when I get Group B to their maximum payout (40% of investment).

I am getting an error that says You've entered too many arguments for this function. I think I am trying 4 when the limit is 3?

I am willing to do this using multiple cells if need be.

Or maybe there is another function to use or there is a simpler way to write the same formula?

I also think I am overcomplicating the formula. What I want to calculate is allocation of profits. Here is what I can say in plain (hopefully) English: If the P&L is less than zero (losses) then 100% of the allocation goes to Group B. If the P&L is between $0 and $120,000 then 100% of the allocation goes to Group A (0% to Group B). If the P&L is over $120,000, Group B gets 50% of the profits up until they have reached a 40% return on their investment. The rest goes to Group A.

I have set up what I call an assumption area with the numbers that can change (investment, profit percentage, amount of high water ($120,000 in my example above)) and three columns, Total Profits, Group A allocation, and Group B allocation. If I can come up with a formula for either group A or Group B, then the other Group is a simple subtraction of the known or calculated group from the total P&L column. I think. I have tried using IF and AND formulas but have run into issues when I get Group B to their maximum payout (40% of investment).

I am getting an error that says You've entered too many arguments for this function. I think I am trying 4 when the limit is 3?

I am willing to do this using multiple cells if need be.

Or maybe there is another function to use or there is a simpler way to write the same formula?

Response by poster: I keep on discarding them when I get the error message. Here is one that works for Group A up until it reaches the max payout to group B.

"=IF(+E21<0>$C$4,E21<2>

Column E is a list of potential Profits or losses. $C$4 is the $120,000 over which Group B starts to participate again. $C$5 is the percentage at which Group B participates (50%) until they max out. The 216000.00001 number is just a P&L plug right now that I calculated by hand would be the point at which Group B maxed out.

For this version of my formula, over the 216k total P&L it returns "False" rather than a number.

posted by AugustWest at 1:23 PM on May 28, 2021

"=IF(+E21<0>$C$4,E21<2>

Column E is a list of potential Profits or losses. $C$4 is the $120,000 over which Group B starts to participate again. $C$5 is the percentage at which Group B participates (50%) until they max out. The 216000.00001 number is just a P&L plug right now that I calculated by hand would be the point at which Group B maxed out.

For this version of my formula, over the 216k total P&L it returns "False" rather than a number.

posted by AugustWest at 1:23 PM on May 28, 2021

Response by poster: I cannot get the formula to display properly in this window. I assume it is a HTML error?

posted by AugustWest at 1:25 PM on May 28, 2021

posted by AugustWest at 1:25 PM on May 28, 2021

Are you using IF or IFS? I think if you have multiple conditions you need IFS.

posted by platypus of the universe at 1:27 PM on May 28, 2021

posted by platypus of the universe at 1:27 PM on May 28, 2021

Best answer: I might break the P&L into four slices - loss, first 120000 of profit, extra profit up to B’s 40%, profit above that.

Then use those cells to calculate A and B allocation per slice, and add up the slices.

Makes it easier to see what’s changing when you change the parameters, too.

posted by clew at 1:27 PM on May 28, 2021

Then use those cells to calculate A and B allocation per slice, and add up the slices.

Makes it easier to see what’s changing when you change the parameters, too.

posted by clew at 1:27 PM on May 28, 2021

Response by poster: =IF(+E21<0>$C$4,E21<216000.00001),(((E21-$C$4)*$C$5)+$C$4))))

posted by AugustWest at 1:27 PM on May 28, 2021

posted by AugustWest at 1:27 PM on May 28, 2021

Response by poster: clew, I will try that. I think it will work, I was just hoping to do it all at once, but I am not married to the idea of one formula.

posted by AugustWest at 1:29 PM on May 28, 2021

posted by AugustWest at 1:29 PM on May 28, 2021

Best answer: The path to short from working is easier than the path to short-and-working from a start!

posted by clew at 1:31 PM on May 28, 2021 [7 favorites]

posted by clew at 1:31 PM on May 28, 2021 [7 favorites]

Your syntax is wrong and your operator is wrong. you don't need the close paren after 216000.00001. The operator for "is not equal" is y<>x not "y{x}" (excuse the weird brackets - html for obvs reason didn't like what I was trying to do)

I'm also assuming

=IF(+E21<0>$C$4,E21<2>

is your entire function - if there is another if then clause in there you need another If or I guess these days the cool kids are doing "IFS" but I'm old.

posted by JPD at 1:53 PM on May 28, 2021

I'm also assuming

=IF(+E21<0>$C$4,E21<2>

is your entire function - if there is another if then clause in there you need another If or I guess these days the cool kids are doing "IFS" but I'm old.

posted by JPD at 1:53 PM on May 28, 2021

Best answer: You need nested IF statements. IF will only take three arguments, but one of the arguments can be another complete IF statement.

In my sheet,

A2 is the P&L

F2 is the initial investment

F4 is the profit share percentage as a decimal (0.5)

F6 is the high water ($120000)

F8 is the max ROI as a decimal (0.4)

Group B can be broken into three ranges.

* P&L negative = All Goes to B

* P&L above high water = B gets the smaller of 140% of their investment or 50% of the profit over high water.

* P&L less than High water = B gets 0

This assumes that you intended Group B to share only the portion over $120000. As written in your question, at $120,001, Group B immediately takes 50% off the top, and group A comes out with far less than they did at $120,000.

posted by yuwtze at 1:58 PM on May 28, 2021

In my sheet,

A2 is the P&L

F2 is the initial investment

F4 is the profit share percentage as a decimal (0.5)

F6 is the high water ($120000)

F8 is the max ROI as a decimal (0.4)

=IF(A2<0,A2,IF(A2>$F$6,MIN((A2-$F$6)*$F$4,(1+$F$8)*$F$2),0)) IF1 T1 F1 IF2 T2 F2You see how the false condition for the first IF is another whole IF?

Group B can be broken into three ranges.

* P&L negative = All Goes to B

* P&L above high water = B gets the smaller of 140% of their investment or 50% of the profit over high water.

* P&L less than High water = B gets 0

This assumes that you intended Group B to share only the portion over $120000. As written in your question, at $120,001, Group B immediately takes 50% off the top, and group A comes out with far less than they did at $120,000.

posted by yuwtze at 1:58 PM on May 28, 2021

I find with long formulas, I get each part working correctly and then stuff them all back into the IF formula.

Essentially, a bunch of nested if statements where if the first IF is negative, you put in another IF

posted by Ftsqg at 2:01 PM on May 28, 2021

Essentially, a bunch of nested if statements where if the first IF is negative, you put in another IF

posted by Ftsqg at 2:01 PM on May 28, 2021

Best answer: Also, to display properly here, you need to replace > with > and replace < with < so that they don't get treated as HTML.

posted by yuwtze at 2:10 PM on May 28, 2021

posted by yuwtze at 2:10 PM on May 28, 2021

Best answer: All you really need to do is replace < with < - the MeFi editor will do the > replacement by itself. I've never understood why it bothers, since < is the

Oh, and you also need to write & anywhere you want an & since that's the lead-in for an HTML entity code (of which & and < are examples).

The trailing semicolons are part of the entity code and it won't work without them.

Other useful entity codes: ½ gives you ½, ° gives you a degree ° mark.

posted by flabdablet at 2:43 PM on May 28, 2021

*only*possible lead-in to an HTML tag.Oh, and you also need to write & anywhere you want an & since that's the lead-in for an HTML entity code (of which & and < are examples).

The trailing semicolons are part of the entity code and it won't work without them.

Other useful entity codes: ½ gives you ½, ° gives you a degree ° mark.

posted by flabdablet at 2:43 PM on May 28, 2021

...and

posted by flabdablet at 2:50 PM on May 28, 2021

*right*after explaining about the trailing semicolon being necessary, I go and leave it out of ½ and ° and they don't work - d'oh!posted by flabdablet at 2:50 PM on May 28, 2021

I agree with the nested IFs but you may need two sets: one in the calculation of Group B's allocation column and another in Group A's allocation column. yuwtze's formula looks correct for Group B. Similar nested IFs need to be used for Group A.

posted by TimHare at 10:05 PM on May 28, 2021

posted by TimHare at 10:05 PM on May 28, 2021

Response by poster: Thank you all for responding with help. I was able to do it to the satisfaction of the person I sent it to by using multiple columns of IFs. Instead of nesting them which I had been trying, I broke them down and used the False to refer to the next column over which had another IF function. If I have time, I might try to put the components back into one function. Put Humpty Dumpty back together again...

yuwtze, I tried, on another sheet, to do it exactly as you suggested. It did not work, but you were on the right path. There was more to the calc than what you wrote in that there was a max return. However, what you wrote helped me break it down more so thank you.

Thanks also for the HTML tips. I am not even close to knowing HTML. What kicks me is that I have been using first Lotus 1-2-3 on a 386 machine in the mid 80s to all sorts of versions of Excel and Sheets. Still could not get it. I am proud to say I still know most of my back-slash Lotus commands.

Thank you again one and all.

posted by AugustWest at 12:11 AM on May 29, 2021

yuwtze, I tried, on another sheet, to do it exactly as you suggested. It did not work, but you were on the right path. There was more to the calc than what you wrote in that there was a max return. However, what you wrote helped me break it down more so thank you.

Thanks also for the HTML tips. I am not even close to knowing HTML. What kicks me is that I have been using first Lotus 1-2-3 on a 386 machine in the mid 80s to all sorts of versions of Excel and Sheets. Still could not get it. I am proud to say I still know most of my back-slash Lotus commands.

Thank you again one and all.

posted by AugustWest at 12:11 AM on May 29, 2021

*Thanks also for the HTML tips*

If you're ever in any doubt about whether something you've entered in the MeFi text editor has had the desired effect, the Live Preview box does a mostly accurate job of showing you how what you've entered is going to look. It's certainly good enough to show you when formulas or code containing < or & characters have taken damage and need the < or & replacements.

However, the view generated by the actual Preview button is definitive and will show you

*exactly*how your text will appear once posted.

Using the Preview button also regenerates the text in the entry box, where you will find that any > you've entered will have been harmlessly converted to its > HTML entity code.

posted by flabdablet at 2:26 AM on May 29, 2021

*If I have time, I might try to put the components back into one function.*

If the way you have it now is easier for you to think about, it's probably going to be easier for the next person who needs to touch it to think about as well. Deeply nested formulas are a pretty common source of mistakes in spreadsheets and sometimes a bunch of hidden columns with intermediate results makes tracking errors down

*much*faster.

posted by flabdablet at 2:31 AM on May 29, 2021 [4 favorites]

« Older These feet are made for blisters | What do I do with this unwanted 3rd-Gen Amazon... Newer »

This thread is closed to new comments.

posted by sagc at 1:16 PM on May 28, 2021