If/Greater than & less than Formula assistance
 

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

Assuming less than 15,000 is 0%
Enter this in Col C
0
15000
22001
25001
30001
35001
40001
Enter this in Col D
0
0.08%
1.25%
1.50%
1.75%
2%
2.50%

Enter this in B1
=VLOOKUP(A1,C:D,2,TRUE)*A1
and copy down


"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

Hy Kdeyton, try this it works for me
+IF(AND(A1=15000,A1<=22000),A1*0.75,IF(AND(A1=22 001,A1<=25000),A1*1.25,IF(AND(A1=25001,A1<=30000) ,A1*1.5,IF(AND(A1=30001,A1<=35000),A1*1.75,IF(AND (A1=35001,A1<=40000),A1*2,IF(A1=40001,A1*2.5,0)) ))))

You can enter the variables in cells and refer it in the formula instead of
entering the values in the formula in that case if you want to change your
parameters it will automatically change it

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

Assuming all your multiplied values should have been percentages, and your
first one, 0.75, should have been 0.075 (and %, so 0.075%).
--
** John C **


"Eduardo" wrote:

Hy Kdeyton, try this it works for me
+IF(AND(A1=15000,A1<=22000),A1*0.75,IF(AND(A1=22 001,A1<=25000),A1*1.25,IF(AND(A1=25001,A1<=30000) ,A1*1.5,IF(AND(A1=30001,A1<=35000),A1*1.75,IF(AND (A1=35001,A1<=40000),A1*2,IF(A1=40001,A1*2.5,0)) ))))

You can enter the variables in cells and refer it in the formula instead of
entering the values in the formula in that case if you want to change your
parameters it will automatically change it

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

2 more options:
=A1*LOOKUP(A1,{0,0;15000,0.00075;22001,0.0125;2500 1,0.015;30001,0.0175;35001,0.02;40001,0.025})

or

=A1*(0.075%*(A1=15000)+1.175%*(A1=22001)+0.25%*( (A1=25001)+(A1=30001)+(A1=35001))+0.5%*(A1=400 01))

--
** John C **

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

Hi John,
you are right instead of 0.75 should be 0.075

"John C" wrote:

Assuming all your multiplied values should have been percentages, and your
first one, 0.75, should have been 0.075 (and %, so 0.075%).
--
** John C **


"Eduardo" wrote:

Hy Kdeyton, try this it works for me
+IF(AND(A1=15000,A1<=22000),A1*0.75,IF(AND(A1=22 001,A1<=25000),A1*1.25,IF(AND(A1=25001,A1<=30000) ,A1*1.5,IF(AND(A1=30001,A1<=35000),A1*1.75,IF(AND (A1=35001,A1<=40000),A1*2,IF(A1=40001,A1*2.5,0)) ))))

You can enter the variables in cells and refer it in the formula instead of
entering the values in the formula in that case if you want to change your
parameters it will automatically change it

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

That is great! I'm almost there.

What if I need it to show zero if the statement wasn't true?
Looks like this:
47,890.00

0-14999 @ 0%
15000-22000 @ .075% - formula entered here
22001-25000 @ 1.25% - formula entered here
25001-30000 @ 1.50% - formula entered here
30001-35000 @ 1.75% - formula entered here
35001-40000 @ 2.00% - formula entered here
41000 above @ 2.5% - formula entered here
Commissions paid Sum above for range that is calculated

"John C" wrote:

2 more options:
=A1*LOOKUP(A1,{0,0;15000,0.00075;22001,0.0125;2500 1,0.015;30001,0.0175;35001,0.02;40001,0.025})

or

=A1*(0.075%*(A1=15000)+1.175%*(A1=22001)+0.25%*( (A1=25001)+(A1=30001)+(A1=35001))+0.5%*(A1=400 01))

--
** John C **

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

Hi Kdeyton,
if you enter my formula if the value is less than 15000 will show "0" as a
result

"kdeyton" wrote:

That is great! I'm almost there.

What if I need it to show zero if the statement wasn't true?
Looks like this:
47,890.00

0-14999 @ 0%
15000-22000 @ .075% - formula entered here
22001-25000 @ 1.25% - formula entered here
25001-30000 @ 1.50% - formula entered here
30001-35000 @ 1.75% - formula entered here
35001-40000 @ 2.00% - formula entered here
41000 above @ 2.5% - formula entered here
Commissions paid Sum above for range that is calculated

"John C" wrote:

2 more options:
=A1*LOOKUP(A1,{0,0;15000,0.00075;22001,0.0125;2500 1,0.015;30001,0.0175;35001,0.02;40001,0.025})

or

=A1*(0.075%*(A1=15000)+1.175%*(A1=22001)+0.25%*( (A1=25001)+(A1=30001)+(A1=35001))+0.5%*(A1=400 01))

--
** John C **

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

Thank you to both of you.....with your help I was able to customize it and
make it work.

Thank you, Thank you, Thank you!!!!!

"Eduardo" wrote:

Hi Kdeyton,
if you enter my formula if the value is less than 15000 will show "0" as a
result

"kdeyton" wrote:

That is great! I'm almost there.

What if I need it to show zero if the statement wasn't true?
Looks like this:
47,890.00

0-14999 @ 0%
15000-22000 @ .075% - formula entered here
22001-25000 @ 1.25% - formula entered here
25001-30000 @ 1.50% - formula entered here
30001-35000 @ 1.75% - formula entered here
35001-40000 @ 2.00% - formula entered here
41000 above @ 2.5% - formula entered here
Commissions paid Sum above for range that is calculated

"John C" wrote:

2 more options:
=A1*LOOKUP(A1,{0,0;15000,0.00075;22001,0.0125;2500 1,0.015;30001,0.0175;35001,0.02;40001,0.025})

or

=A1*(0.075%*(A1=15000)+1.175%*(A1=22001)+0.25%*( (A1=25001)+(A1=30001)+(A1=35001))+0.5%*(A1=400 01))

--
** John C **

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!

If/Greater than & less than Formula assistance
 

Both of my formulas should return 0 if <15000. Glad you got it working.
--
** John C **

"kdeyton" wrote:

That is great! I'm almost there.

What if I need it to show zero if the statement wasn't true?
Looks like this:
47,890.00

0-14999 @ 0%
15000-22000 @ .075% - formula entered here
22001-25000 @ 1.25% - formula entered here
25001-30000 @ 1.50% - formula entered here
30001-35000 @ 1.75% - formula entered here
35001-40000 @ 2.00% - formula entered here
41000 above @ 2.5% - formula entered here
Commissions paid Sum above for range that is calculated

"John C" wrote:

2 more options:
=A1*LOOKUP(A1,{0,0;15000,0.00075;22001,0.0125;2500 1,0.015;30001,0.0175;35001,0.02;40001,0.025})

or

=A1*(0.075%*(A1=15000)+1.175%*(A1=22001)+0.25%*( (A1=25001)+(A1=30001)+(A1=35001))+0.5%*(A1=400 01))

--
** John C **

"kdeyton" wrote:

I have been trying for hours to enter a formula that will allow me to do the
following:
If cell A1 is Between 15000 and 22000 than A1 times .075%
If cell A1 is Between 22001 and 25000 than A1 times 1.25%
If cell A1 is Between 25001 and 30000 than A1 times 1.5%
If cell A1 is Between 30001 and 35000 than A1 times 1.75%
If cell A1 is Between 35001 and 40000 than A1 times 2%
If cell A1 is 40001 and above than A1 times 2.5%

Please Help, I need to your assistance so I can move on with the rest of my
day!
Thank you in advance!