# excel 2003 IF AND functions choose?

Discussion in 'Business Applications' started by CaliMac10, Jun 25, 2010.

Not open for further replies.

Joined:
Jun 25, 2010
Messages:
1
Hi im trying to allocate costs for a large number of customers where they are charged by lot size. here is some of sq.ft data and rate data from the Sheet2 tab

Sq. Ft.
7560
7200
14842
11277
7216
8860
15211
16506
30999
23723
7626
20915
10530

Sq. Ft.Fixed
<10000 \$3.15
<15000 \$3.20
<20000 \$4.26
<25000 \$4.33
<30000 \$5.38
<35000 \$5.42
<40000 \$6.48

so 9000 sq.ft. returns \$3.15, 23000 sq.ft. returns \$4.33 etc...

I have the list in a seperate tab with everyones given square footage. I have used the IF AND function to determine if that customer is in one particular range, say between 10,000 and 15,000, but i am lost at how to combine formulas so that excel can choose which range the given customer is in so that it can return the correct fixed amount.

these are my functions, but how do i combine these so that it can be in one column instead of required 7 different columns for every sq.ft. range and rate formula?

=IF(D3<Sheet2!\$A\$2,Sheet2!\$B\$2,0)
=IF(AND(D3>Sheet2!\$A\$2,D3<Sheet2!\$A\$3),Sheet2!\$B\$3)
=IF(AND(D3>Sheet2!\$A\$3,D3<Sheet2!\$A\$4),Sheet2!\$B\$4)
=IF(AND(D3>Sheet2!\$A\$4,D3<Sheet2!\$A\$5),Sheet2!\$B\$5)
=IF(AND(D3>Sheet2!\$A\$5,D3<Sheet2!\$A\$6),Sheet2!\$B\$6)
=IF(AND(D3>Sheet2!\$A\$6,D3<Sheet2!\$A\$7),Sheet2!\$B\$7)
=IF(AND(D3>Sheet2!\$A\$7,D3<Sheet2!\$A\$8),Sheet2!\$B\$8)

Joined:
Nov 28, 2005
Messages:
60,881
First Name:
Chuck
Hi CaliMac10, and welcome to TSG.

You might want to use the VLOOKUP function in Excel to do what you want.

3. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
I tend to agree with cwwozniak , especially if the values might change at times - much easier to change in a list than in a formula. However, this might work for you:
=IF(D3<Sheet2!\$A\$2,Sheet2!\$B\$2,IF(AND(D3>Sheet2!\$A\$2,D3<Sheet2!\$A\$3),Sheet2!\$B\$3,IF(AND(D3>Sheet2!\$A\$3,D3<Sheet2!\$A\$4),Sheet2!\$B\$4,IF(AND(D3>Sheet2!\$A\$4,D3<Sheet2!\$A\$5),Sheet2!\$B\$5,IF(AND(D3>Sheet2!\$A\$5,D3<Sheet2!\$A\$6),Sheet2!\$B\$6,IF(AND(D3>Sheet2!\$A\$6,D3<Sheet2!\$A\$7),Sheet2!\$B\$7,IF(AND(D3>Sheet2!\$A\$7,D3<Sheet2!\$A\$8),Sheet2!\$B\$8)))))))

As Seen On