Help completing formula

[KCALDERON5894]

I am pretty new to formulas and I'm hoping someone can help me... I am trying to create a formula that assigns a rate of 1% for revenues between $350,000- $750,000 2% for $750,000 - $1,449,000 and 3% for $1,500,000 - $2,500,000. I did =IF(AND(G14>350000, G14<750000)1.00), (AND(G14>750001, G14<1449000)2.00), (AND(G14>15000000, G14<25000000)3.00) but its not working.

 

[AhoyNC]

See if this does what you want. I added a 0% for values below $350,000 (which you may not need).

Excel Workbook
	F	G	H	I
13
14		768000	0.02
15
Sheet1

 

[hippiehacker]

=IF(AND(G14>350000;G14<750000);1;IF(AND(G14>750000; G14<1449999);2;IF(AND(G14>1500000; G14<2500000);3;"no match")))

 

[Aladin Akyurek]

I am pretty new to formulas and I'm hoping someone can help me... I am trying to create a formula that assigns a rate of 1% for revenues between $350,000- $750,000 2% for $750,000 - $1,449,000 and 3% for $1,500,000 - $2,500,000. I did =IF(AND(G14>350000, G14<750000)1.00), (AND(G14>750001, G14<1449000)2.00), (AND(G14>15000000, G14<25000000)3.00) but its not working.

=MATCH(G14,{0,350000,750000,1500000},1)-1

would also work.

 

[AhoyNC]

Aladin,
Since OP is looking for a percent wouldn't they need to multiple your formula by .01?

 

[Aladin Akyurek]

Aladin,
Since OP is looking for a percent wouldn't they need to multiple your formula by .01?

I wasn't paying attention to that aspect. And yes, adding *0.01 to the formula is the right idea. Thanks.

 

[KCALDERON5894]

Thanks So much! it worked! I modified it to =IF(C5<1000001,C15,IF(C5<2000001,C17,IF(C5<3000001,C18,C19))) except when I tried to add more cells it said " you've entered too many arguments for this function" any way around this?

 

[Aladin Akyurek]

Thanks So much! it worked! I modified it to =IF(C5<1000001,C15,IF(C5<2000001,C17,IF(C5<3000001,C18,C19))) except when I tried to add more cells it said " you've entered too many arguments for this function" any way around this?

Did you consider the MATCH. formula at all?