IF question, multiple criteria with comparison operators defined in cells

[TheoMa]

I am trying to figure out how to "code" this problem using a formula

I have 1 input value in A1 ex 333,000

Want to check it against this matrix

Columng A[TABLE="width: 100"]
<TBODY>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]<=
[/TD]
[TD]200,000
[/TD]
[TD]+10%
[/TD]
[/TR]
[TR]
[TD]<=
[/TD]
[TD]300,000
[/TD]
[TD]+5%
[/TD]
[/TR]
[TR]
[TD]>=
[/TD]
[TD]400,000
[/TD]
[TD]-5%
[/TD]
[/TR]
[TR]
[TD]>=
[/TD]
[TD]600,000
[/TD]
[TD]-10%
[/TD]
[/TR]
</TBODY>[/TABLE]



Think of it as a volume discount/penalty applied to the unit price if the volume is above or below certain volume points. I want to be able to change the comparison sign in different cases

I tried IF(a1&a3&b3,c3,"") and it does not evaluate...

Thanks for looking into this
Theo

 

[AlphaFrog]

I tried IF(a1&a3&b3,c3,"") and it does not evaluate...

This will evaluate one line in your matrix.
=IF(COUNTIF(A1,A3&B3),C3,"")

You may want to consider using a VLOOKUP formula instead and change the cutoff values in the matrix instead of having different comparisons in column A

Example: =VLOOKUP(A1,B3:C7,2,TRUE)
<br />

Book1
	B	C
3	0	10%
4	200,001	5%
5	300,001	0%
6	400,000	-5%
7	600,000	-10%
Sheet1

<br /><br />

 

[radfordc]

I have added a new column to do calc with, using number instead of %.
Assume data to check is in cell a1 enter this formula in cell a3
=IF(A1>=B10,A1*D10,IF(A1>=B9,A1*D9,IF(A1<=B7,A1*D7,A1*D8)))
Just uses a nested if statment

a b c d
7 <= 200,000 10% 1.1
8 <= 300,000 5% 1.05
9 >= 400,000 -5% 0.95
10 >= 600,000 -10% 0.9