I have a rather ordinary Excel table that consists of a series of values (columns) attributed to various “clients” (lines) over a year. In column I there’s a sum of the values per client for the first 6 months and in column Q, the same for the last 6 months.
Column T consists of binary answers given by each client (yes/no).
Column U has an IF function. If the answer in T is “no”, it returns the average between the I and Q columns. If the answer in T is “yes”, only the Q sum is taken into account.
Column V also consists of binary answers that depend on each client’s record (+/- in order to avoid confusion).
All of this works fine.
The fun starts when I try to nest a ROUNDUP or ROUNDDOWN function inside an IF function in column W. What I want is :
If the value in V is “+”, U must be rounded up to the higher integer (8.1 becomes 9).
If the value in V is “-”, U must be rounded down to the lower integer (8.9 becomes 8)
More specifically, the problem seems to come from the way I input the ROUNDUP/DOWN functions, because when I type something like =IF(V3=“+”;4,3), I get the expected results (+ => 4 / - => 3). But that’s not what I want.
I’ve tried a dozen variations either with
IF+(ROUNDUP/DOWN)
or
IF+(ROUNDUP); IF+(ROUNDDOWN)
and every permutations of commas, semi-columns, brackets and parentheses I could think of. I’ve also checked a couple of websites dedicated to related problems, but to no avail.
How should I do this ?
Disclaimer : I know that some of the notations I’ve used above are not correct. I’m a bit pressed for time, so I tried to explain the problem as accurately as I could while only giving an idea of the sort of (non)-solutions I came up with.
What you need is some understanding, not just rote poking symbols into the edit box until something happens.
You can’t use IF() to select between two functions to apply to the same arguments. But you CAN use IF() to select between two results, each computed with a different function. Which is what @mcgato supplied for you.
I read about some similar situations and tried to extrapolate the solutions provided to my particular problem. Granted, there was a fair degree of improvisation in what I did, but it’s not as if I pulled formulas out of nowhere.
The Excel formula editor is also a PITA to use to get the right answer. It’s a tiny box with gray type on a gray background. The error message for syntax errors are nearly useless.
There are ways it could be redesigned to make it much easier for people to get the results they seek. But so far MS has chosen to favor familiarity over improvement.
I’m pretty good with this stuff and I often end up cursing at the formula editor. It’s not you; it’s it.
That’s the standard way to use IF functions for this. It might help to have a thorough understanding of how IF functions work. They have a condition to be tested, an instruction if the test returns true, and an instruction if the test returns false. In your case you want to test if V2=“+”. If it does you want to round U2 up, if it doesn’t you want to round U2 down.
Conceptualising this as a formula gives:
=IF(V2=“+”, round U2 up, round U2 down)
Then you just need to substitute “round U2 up” and “round U2 down” for the actual ROUNDUP and ROUNDDOWN formulas.
Something else to try when having trouble with IF functions is to separate the formulas out into their own cells. So in X2 put =ROUNDUP(U2,0) and in Y2 put =ROUNDDOWN(U2,0). Then you can have an IF function that just calls those cells.
=IF(V3=“+”, X2, Y2)
Once you’re satisfied that works, you can replace the cell calls from the IF function with the ROUNDUP/DOWN formulas themselves and delete the intermediate cells you used.
By the way, unless something has changed with Excel since I last used it, you either use commas or semicolons depending on your region settings, but you don’t mix them together.