IF Statement errors

[langumd]

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)))))))

 

[Legacy 98055]

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]

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]

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]

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]

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]

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