IF THEN formula stumped

**tig1142** · 12-29-2014, 08:24 PM

Hi all,

I'm trying to come up with a formula that has three conditions. I tried the IF/Then formula, but it says I have too many arguments. I'm looking for this:

If the contract amount is under 50k that the following occurs PM(C2) 40% or more gets 5% of dollar amount (B2), PM30-39 gets 3.5, PM20-29 gets 2.5 less than less than 20 is 0. However, if a contract amount is over 50K than a PM of 20% gets a 5% rate.. so the formula I have is

=IF(A2>=50000,if(C2>=20%,B2*0.025,IF(C2<=20%,B2*0),if(C2>=20%,B2*.05,0)))

Contract amount = A2
Dollar amount = B2
Percentage = C2

Any help would be appreciated!!

**teylyn** · 12-30-2014, 02:34 AM

Hello,

the logic you describe can be created with nested IF functions like this:

Please Login or Register  to view this content.

Or you can use a Lookup function like this:

Please Login or Register  to view this content.

What's wrong with your formula:

Your formula is missing a closing bracket on the second IF statement, which causes the error message of "too many arguments". Remove one closing bracket from the end of the formula. This gives a starting point if you want to pursue your approach, but you are still missing a lot of conditions.

=IF(A2>=50000,if(C2>=20%,B2*0.025,IF(C2<=20%,B2*0)),if(C2>=20%,B2*.05,0))

cheers, teylyn

**excelfandoo** · 12-30-2014, 02:48 AM

Please find below the formula based on my understanding:

=IF(A2<50000,IF(C2>=40%,B2*5%,IF(AND(C2>=30%,C2<=39%),B2*3.5%,IF(AND(C2>=20%,C2<=29%),B2*2.5%,0))),IF(AND(C2<20%,A2>=50000),B2*5%,))

I understand that you have not covered many other conditions, while explaining the problem. please let me know if i need to make this formula even comprehensive.

**teylyn** · 12-30-2014, 02:57 AM

excelfandoo, the And() functions are not required when you use the right order. If C2 is > 40, then this, otherwise, if C2 is > 30 then something else. Specifying that it also must be <= 39 is doubling up. It will already be less than 40, otherwise the step would never be applied.

**excelfandoo** · 12-30-2014, 03:04 AM

My bad. Thank you teylyn for pointing it out.

IF THEN formula stumped

LinkBack

Thread Tools

Rate This Thread

Display

IF THEN formula stumped

Re: IF THEN formula stumped

Re: IF THEN formula stumped

Re: IF THEN formula stumped

Re: IF THEN formula stumped

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Stumped on calculating a formula

[SOLVED] Stumped on an IF Formula

[SOLVED] Stumped with Formula Array returning cell adjacent to formula dataset

Stumped on Formula

[SOLVED] Completely stumped for formula

Bookmarks

Bookmarks

Posting Permissions