# Excel Formula

Discussion in 'Business Applications' started by Moby01, Dec 29, 2012.

Not open for further replies.

Joined:
Dec 29, 2012
Messages:
2
My question is how do I input a formula into Office 2010 Excel that calculates set commission amounts (\$100,\$150,\$200,&\$250) for various dollar sales amounts?
I have been able to work it this far:
=IF(D4>2999,D4<4000)*("\$200.00")
The part I need to know is how do I expand this formula to include the next level of commission pay off \$250.00
How close am I?
=IF(D4>2999,D4<4000)*("\$200.00") (D4>=4000)*("\$250.00")
I am not sure how I join or add the brackets together.
I appreciate you help.

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,988
First Name:
Wayne
use nested IFs

so

IF( test , true , false )

now to nest in the false section (you can also nest in true)

IF( test, true, IF(test, true, false) )

if you want to give the details , i can set up

not sure why thats works

I would also look at the structure of the data and you may avoid using the between

but for between use

AND( D4>2999,D4<4000)
as the test

IF( D4 > 4000, "4001+ commision calc", if(d4> 2999, "3000+ commision calc", if( d4>1000, "1001+ commision calc", "value for below or equal 1000")

if you want to include greater or =
>=

the tests are carried out in the order written
so if its false for >4000 - then the next test shows below 4001 but greater than 2999

Joined:
Dec 29, 2012
Messages:
2
Thank you for your advice it helped me out in exactly the right way.
This was the first time for me on any forum, I enjoyed this experience.
I also like the various subjects that are discussed on TechGuy.
Regards

Moby01

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,988
First Name:
Wayne
your welcome thanks for letting us know
You can mark your own threads solved using the button at the top of the page of the thread in the upper left corner.

As Seen On