Excel Formula Question

May 28, 2021 1:04 PM

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?

posted by AugustWest to Computers & Internet (19 answers total)

Can you set this up in Google Sheets, or share the formula you're trying to get to work in Excel?
posted by sagc at 1:16 PM on May 28, 2021

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

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

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

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

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

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

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]

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

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)

=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                                F2

You 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'm pretty sure you should do this as nested ifs BTW
posted by JPD at 1:59 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

Best answer: Also, to display properly here, you need to replace > with &gt; and replace < with &lt; so that they don't get treated as HTML.
posted by yuwtze at 2:10 PM on May 28, 2021

Best answer: All you really need to do is replace < with &lt; - the MeFi editor will do the > replacement by itself. I've never understood why it bothers, since < is the only possible lead-in to an HTML tag.

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

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

Other useful entity codes: &frac12; gives you &frac12, &deg; gives you a degree &deg mark.
posted by flabdablet at 2:43 PM on May 28, 2021

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

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

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 &lt; or &amp; 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 &gt; 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]