Nested IF AND FORMULA for Tiered Fee Calculation

[slstewart249]

I am trying to write a nested IF AND Function to produce a result off of a Tiered Fee Table.
A . B . C . D
1 . Condition 1 Condition 2 Result if True . Result if False
2 . 1,000 2,000 1.7% . 0
3 . 2,000 3,000 1.6% . 0
4 . 3,000 . 4,000 . 1.5% . 0
5
6 . # to Be evaluated:
7 1,500
8 . 2,500

The below formula works for w having only two conditions (A2,B2).
If written it so =IF(AND(A7>=A2,A7<=B2),C2,D2)

How can I write a NESTED FORMULA to have multiple conditions ie. Row 2, Row 3, Row 4?

 

[arthurbr]

You could use differential rates with sumproduct

 

[Peter_SSs]

Does this do what you want?
If not & you still need help, please give more details about the exact result(s) want, where and why.

Excel Workbook
	A	B	C
2	1,000	2,000	1.70%
3	2,000	3,000	1.60%
4	3,000	4,000	1.50%
5
6
7	1500	1.70%
8	2,500	1.60%
Lookup

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Condition 1 [/td][td]Condition 2[/td][td] Result if True [/td][td]. Result if False[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1,000​

[/td][td]

2,000​

[/td][td]

1.70%​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

2,000​

[/td][td]

3,000​

[/td][td]

1.60%​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

3,000​

[/td][td]

4,000​

[/td][td]

1.50%​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]6 . # to Be evaluated: [/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

1,500​

[/td][td]

1.70%​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

2,500​

[/td][td]

1.60%​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet43[/td][/tr][/table]

Formula in cell B7:
=SUMPRODUCT((A7<$B$2:$B$4)*(A7>$A$2:$A$4)*$C$2:$C$4)