IF Statement errors

langumd · Apr 23rd, 2002, 04:22 PM

I am trying to use the following IF statement, which I want to look at a cell & return varying percentage amounts depending on the amount in the cell being looked at.

Ex. if F4 contains 10795, I want F5 to reflect 0.05; if F4 contains 75000, I want F5 to reflect 0.03, etc...

I keep getting an error when I attempt to use this IF statement. Any ideas? Is it the "AND" causing the problem? How else I can make it return the desired amount for the ranges needed?

My complete list of ranges & desired percentages is as follows, but since the IF statement must be limited to 7, I eliminated the 1st set:

1-5000 = 0.07 (or minimum 500)
5001-10000 = 0.06
10001-25000 = 0.05
25001-35000 = 0.04
35001-40000 = 0.035
40001-75000 = 0.03
75001-150000 = 0.025
150001-UP = 0.02

=IF(F4>5000AND<10001,0.06,IF(F4>10000AND<25001,0.05,IF(F4>25000AND<35001,0.04,IF(F4>35000AND<40001,0.035,IF(F4>40000AND<75001,0.03,IF(F4>75000AND<150001,0.025,IF(F4>150000,0.02)))))))

Tom Schreiner · Apr 23rd, 2002, 04:29 PM

Hi
Change your 'And' Syntax
=IF(AND(F4>5000,F4<10001),0.06,IF(AND(F4>10000,F4<25001),0.05,IF(AND(F4>25000,F4<35001),0.04,IF(AND(F4>35000,F4<40001),0.035,IF(AND(F4>40000,F4<75001),0.03,IF(AND(F4>75000,F4<150001,0.025),IF(F4>150000,0.02)))))))
throughout your formula for the correct syntax, I think.
Tom

[ This Message was edited by: TsTom on 2002-04-23 15:36 ]

Aladin Akyurek · Apr 23rd, 2002, 04:40 PM

Quote:

On 2002-04-23 15:22, langumd wrote:
I am trying to use the following IF statement, which I want to look at a cell & return varying percentage amounts depending on the amount in the cell being looked at.

Ex. if F4 contains 10795, I want F5 to reflect 0.05; if F4 contains 75000, I want F5 to reflect 0.03, etc...

I keep getting an error when I attempt to use this IF statement. Any ideas? Is it the "AND" causing the problem? How else I can make it return the desired amount for the ranges needed?

My complete list of ranges & desired percentages is as follows, but since the IF statement must be limited to 7, I eliminated the 1st set:

1-5000 = 0.07 (or minimum 500)
5001-10000 = 0.06
10001-25000 = 0.05
25001-35000 = 0.04
35001-40000 = 0.035
40001-75000 = 0.03
75001-150000 = 0.025
150001-UP = 0.02

=IF(F4>5000AND<10001,0.06,IF(F4>10000AND<25001,0.05,IF(F4>25000AND<35001,0.04,IF(F4>35000AND<40001,0.035,IF(F4>40000AND<75001,0.03,IF(F4>75000AND<150001,0.025,IF(F4>150000,0.02)))))))

You don't need to eliminate any category from your percent table. Just enter it as a 2-column list somewhere (in a worksheet called "Admin", for example).

{1,0.07;
5001,0.06;
10001,0.05;
25001,0.04;
35001,0.035;
40001,0.03;
75001,0.025;
150001,0.02}

and select all of these cells, go the Name Box on the Formula Bar, type PList, and hit enter.

Usage:

=VLOOKUP(A1,PList,2)

where A1 is a number for which you want to retrieve appropriate percentage.

Aladin

[ This Message was edited by: aladin akyurek on 2002-04-24 00:05 ]

Yogi Anand · Apr 23rd, 2002, 07:10 PM

Hi langumd:
Further to suggestions by TsTom and Aladin, here is what might be specific to your usage.
Add the following table in cells A1:B9

Qty Percent
500 0.07
5001 0.06
10001 0.05
25001 0.04
35001 0.035
40001 0.03
75001 0.025
150001 0.02

and
1. Click in cell G4
2. then INSERT|NAME|DEFINE ... enter Percentage in the box for Names_in_workbook
3. in Refers_to: box enter the following formula

=VLOOKUP(F4,$A$1:$B$9,2)

4. Click on OK

Now if you enter 10795 in cell F4 and enter the following formula in cell G4

=Percentage ..... that will result in 0.05

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

Jay Petrulis · Apr 23rd, 2002, 07:26 PM

Hi,

Slightly OT, but what exactly do these percentages represent?

If I take the maximum amount in each range and multiply it by the %, I have the following results:

5000 0.07 350
10000 0.06 600
25000 0.05 1250
35000 0.04 1400
40000 0.035 1400
75000 0.03 2250
150000 0.025 3750
150001 0.02 3000.02

The maximum at 35K and 40K is the same. If this is the total extra charge to a customer, for example, a purchase of 36K is less than a purchase of 35K.

So, these cannot be quantity discounts at certain breakpoints. If these are marginal increases, meaning 36K purchased is 4% for the first 35K and 3.5% for the remaining 1K, then you are into a whole different realm here.

If this is the case, you *must* check Chip Pearson's website and search for "Bracket or Progressive Pricing." It is an excellent tutorial for the progressive pricing model (this also applies to marginal tax rates).

http://www.cpearson.com/excel/pricing.htm

If this is not what is needed, my apologies.

Bye,
Jay

Yogi Anand · Apr 23rd, 2002, 08:03 PM

Good point Jay!
I did not ask what Diane intended to do with the formula ... on your raising this point, I realize I should have because I agree with you the irrationality of single step pricing structure. in my limited experience, I also believe in having a stepped pricing structure, e.g. the price per pice for first 100, and then the price per piece for next 100, and so on ...

Regards!

Yogi Anand

langumd · Apr 24th, 2002, 08:13 AM

These numbers will actually be print totals for a flyer mailing. We have 20 different geographic breakouts which have varying customer totals. Based on these customer totals (& the type of breakout...some get the percentage indicated, some get the percentage indicated + increased by 1 percentage point).

I have pasted a sample of the 3 columns I am working with, customer count (F4:F21), percentage of overs (G4:G21), and Total Number of Overs (H4:H21). The percentages I am trying to input automatically would change according to the customer count; the higher the customer count the less overs I need printed.

In the meantime, I will try some of your suggestions to see if I can get this to work for me. thanks for your help

CUSTOMER.....%........#
COUNT......OVER......OVER
2,169......0.240.....521
26,081.....0.040.....1,043
245,014....0.020.....4,900
11,720.....0.060.....703
32,785.....0.050.....1,639
80,014.....0.035.....2,800
9,279......0.070.....650
141,403....0.025.....3,535
191,338....0.020.....3,827
82,990.....0.025.....2,075
33,460.....0.040.....1,338
28,744.....0.040.....1,150
120,165....0.025.....3,004
43,321.....0.040.....1,733
19,682.....0.060.....1,181
46,545.....0.040.....1,862
8,648......0.070.....605
24,261.....0.060.....1,456

[ This Message was edited by: langumd on 2002-04-24 07:43 ]

[ This Message was edited by: langumd on 2002-04-24 07:48 ]

Apr 23rd, 2002, 07:10 PM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi langumd: Further to suggestions by TsTom and Aladin, here is what might be specific to your usage. Add the following table in cells A1:B9 Qty Percent 500 0.07 5001 0.06 10001 0.05 25001 0.04 35001 0.035 40001 0.03 75001 0.025 150001 0.02 and 1. Click in cell G4 2. then INSERT\|NAME\|DEFINE ... enter Percentage in the box for Names_in_workbook 3. in Refers_to: box enter the following formula =VLOOKUP(F4,$A$1:$B$9,2) 4. Click on OK Now if you enter 10795 in cell F4 and enter the following formula in cell G4 =Percentage ..... that will result in 0.05 HTH Please post back if it works for you ... otherwise explain a little further and let us take it from there! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 23rd, 2002, 04:22 PM	#1
langumd New Member Join Date: Apr 2002 Location: TX Posts: 11	I am trying to use the following IF statement, which I want to look at a cell & return varying percentage amounts depending on the amount in the cell being looked at. Ex. if F4 contains 10795, I want F5 to reflect 0.05; if F4 contains 75000, I want F5 to reflect 0.03, etc... I keep getting an error when I attempt to use this IF statement. Any ideas? Is it the "AND" causing the problem? How else I can make it return the desired amount for the ranges needed? My complete list of ranges & desired percentages is as follows, but since the IF statement must be limited to 7, I eliminated the 1st set: 1-5000 = 0.07 (or minimum 500) 5001-10000 = 0.06 10001-25000 = 0.05 25001-35000 = 0.04 35001-40000 = 0.035 40001-75000 = 0.03 75001-150000 = 0.025 150001-UP = 0.02 =IF(F4>5000AND<10001,0.06,IF(F4>10000AND<25001,0.05,IF(F4>25000AND<35001,0.04,IF(F4>35000AND<40001,0.035,IF(F4>40000AND<75001,0.03,IF(F4>75000AND<150001,0.025,IF(F4>150000,0.02)))))))

Apr 23rd, 2002, 04:29 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Change your 'And' Syntax =IF(AND(F4>5000,F4<10001),0.06,IF(AND(F4>10000,F4<25001),0.05,IF(AND(F4>25000,F4<35001),0.04,IF(AND(F4>35000,F4<40001),0.035,IF(AND(F4>40000,F4<75001),0.03,IF(AND(F4>75000,F4<150001,0.025),IF(F4>150000,0.02))))))) throughout your formula for the correct syntax, I think. Tom [ This Message was edited by: TsTom on 2002-04-23 15:36 ]

Apr 23rd, 2002, 07:26 PM	#5
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, Slightly OT, but what exactly do these percentages represent? If I take the maximum amount in each range and multiply it by the %, I have the following results: 5000 0.07 350 10000 0.06 600 25000 0.05 1250 35000 0.04 1400 40000 0.035 1400 75000 0.03 2250 150000 0.025 3750 150001 0.02 3000.02 The maximum at 35K and 40K is the same. If this is the total extra charge to a customer, for example, a purchase of 36K is less than a purchase of 35K. So, these cannot be quantity discounts at certain breakpoints. If these are marginal increases, meaning 36K purchased is 4% for the first 35K and 3.5% for the remaining 1K, then you are into a whole different realm here. If this is the case, you must check Chip Pearson's website and search for "Bracket or Progressive Pricing." It is an excellent tutorial for the progressive pricing model (this also applies to marginal tax rates). http://www.cpearson.com/excel/pricing.htm If this is not what is needed, my apologies. Bye, Jay

Apr 23rd, 2002, 08:03 PM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Good point Jay! I did not ask what Diane intended to do with the formula ... on your raising this point, I realize I should have because I agree with you the irrationality of single step pricing structure. in my limited experience, I also believe in having a stepped pricing structure, e.g. the price per pice for first 100, and then the price per piece for next 100, and so on ... Regards! Yogi Anand

Apr 24th, 2002, 08:13 AM	#7
langumd New Member Join Date: Apr 2002 Location: TX Posts: 11	These numbers will actually be print totals for a flyer mailing. We have 20 different geographic breakouts which have varying customer totals. Based on these customer totals (& the type of breakout...some get the percentage indicated, some get the percentage indicated + increased by 1 percentage point). I have pasted a sample of the 3 columns I am working with, customer count (F4:F21), percentage of overs (G4:G21), and Total Number of Overs (H4:H21). The percentages I am trying to input automatically would change according to the customer count; the higher the customer count the less overs I need printed. In the meantime, I will try some of your suggestions to see if I can get this to work for me. thanks for your help CUSTOMER.....%........# COUNT......OVER......OVER 2,169......0.240.....521 26,081.....0.040.....1,043 245,014....0.020.....4,900 11,720.....0.060.....703 32,785.....0.050.....1,639 80,014.....0.035.....2,800 9,279......0.070.....650 141,403....0.025.....3,535 191,338....0.020.....3,827 82,990.....0.025.....2,075 33,460.....0.040.....1,338 28,744.....0.040.....1,150 120,165....0.025.....3,004 43,321.....0.040.....1,733 19,682.....0.060.....1,181 46,545.....0.040.....1,862 8,648......0.070.....605 24,261.....0.060.....1,456 [ This Message was edited by: langumd on 2002-04-24 07:43 ] [ This Message was edited by: langumd on 2002-04-24 07:48 ]