0

I have the following formula, that works:

=IF(G1<33000,(G1*0.35)-D8,IF(G1>33000,(33000*0.35)-D8))

I am trying to add, that if it returns a negative number, it will display 0. I think this is the formula:

=IF(G1<33000,(G1*0.35)-D8<0,0, G1*0.35)-D8, IF(G1>33000,(33000*0.35)-D8)<0,0, G1>33000,(33000*0.35)-D8)

When I put that formula in excel, I get the following error: "You've entered too many arguments for this formula."

Improve this question

3 Answers 3

Reset to default
3

Here's another approach for you. BTW, you don't provide for G1=33000 in your formula.

You can accomplish this with a single IF. Start with your first case:

=IF(G1<33000,G1*0.35-D8,33000*0.35-D8)

If G1 is greater than or equal to 33000, the value is capped at 33000. The IF test evaluates whether G1<33000. If true, it uses the first formula. If false, it uses the second. So the one test covers both cases.

Now you want to expand it so that the calculated result doesn't go below zero. You can accomplish that with the MAX function:

=MAX(0,IF(G1<33000,G1*0.35-D8,33000*0.35-D8))

If the result is less than zero, MAX will use the larger value of zero.

You can apply the same logic to eliminate the IF test entirely:

=MAX(0,MIN(G1,33000)*0.35-D8)

If G1 exceeds 33000, MIN will use the lesser value of 33000.

Improve this answer
1

Perhaps,

=IF(G1<33000,IF((G1*0.35)-D8<0,0,(G1*0.35)-D8),(33000*0.35)-D8)

It seems to me that you have misordered a couple arguments and misplaced parentheses in the one you provided. An IF statement can only have 3 arguments, not 4 (as with the 1st one).

Improve this answer
2
  • You are a lifesaver! That worked! Thank you!!
    – smbisceg
    Nov 25, 2017 at 4:33
  • @smbisceg, notice that I made a fix concerning the first IF statement's third argument. I made an explanation on another post, as well as fixer1234 whom pointed it out (concerning your original formula).
    – El8dN8
    Nov 25, 2017 at 11:40
0

My Formula is little different than what @El8TedN8te has suggested because he has just missed one crucial issue.

Which is, how the Formula would react if the Value is EQUALS to 33000. Since both <33000 and >33000 has been tested? But not the =33000, which is very much possible.

So, better you use this one,,

=IF(G1<33000,IF((G1*0.35)-D8<0,0,(G1*0.35)-D8),IF(G1>=33000,(G1*0.35)-D8))

Hope this help you. I've posted the solution after all 3 possibilities has been tested by me.

Improve this answer
2
  • Nice catch! Although the OP had made that unnecessary component of even having an IF statement for the last argument anyways (as @fixer1234 had explained in his post correct the OP's pre-formula), since G1<33000 failed that means G1 must be >= 33000 already (because the last argument deals the condition failure), thus the last IF is not even needed and can just be (33000*0.35)-D8). I am going to make the edit.
    – El8dN8
    Nov 25, 2017 at 11:33
  • Even I thought also to remove the "Second IF", but I've experienced many times in past that few people voted my answer Negative just because I'd altered the Formula which I found Logically required. Few readers don't like improvisations. Any way keep asking,.
    – Rajesh Sinha
    Nov 25, 2017 at 11:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .