Help for IF and AND functions

[toni2356]

Hi guys. Happy new year!

I have some problems with this table:
i need formula for this mathematical calculation>

<=0 >=0 >=2 -------- cell B1
>=1000 2.5% 4% 6%
>=3500 3.5% 5% 7%
>=5700 4,5% 6% 8%
|
|
|
cell A1

If in cell A1 i write 1000 and in cell B1 i put a number who is >=2 then in cell C1 is this result A1*6% or 1000 - 6%
if in cell A1 i write 4500 and in cell B1 i put 0 then in cell C1 is this result
A1*5% or 4500 - 5 %
if in cell A1 i write 8500 and in cell B1 i put -1 then in cell C1 the result gonna be A1*4,5% or 8500 - 4,5 %

In cell A1 you can write number who is bigger then 1000
in cell B1 you can write numbers who is <=0 (-1,-2...), >=0 and >2

Thank you for your help

i upload a file notpade documet with the table insaid

[teylyn]

Hi toni,

welcome to the forum. Please take a moment to read the forum rules, and then post your next question in a question forum. Tips and Tutorials is not for questions.

moved to Excel Worksheet functions.

[teylyn]

what happens if A1 is less than 1000?
what happens if B1 is exactly 0? you have conflicting information in <=0 and >=0 -- you need to decide.

[teylyn]

See attached. I've decided to go for <0 and >=0 and applied data validation to ensure minimum value in A1 is 1000.

I've built a lookup table in A3:D6 and then use this formula in C1

=A1-(A1*INDEX(B4:D6,MATCH(A1,A4:A6,1),MATCH(B1,B3:D3,1)))

hth

[toni2356]

Thanks

If A1 is <1000 then the result in C1 gonna be 0 or error, but its ok
And for B1 i have an error in tiping sorry
B1 is <0, >=0, >=2

It's work. Thanks man . You rule!

[rwgrietveld]

I think it is OK, but not complete.

Use this instead

Please Login or Register  to view this content.

indeed this can be simplified using names

Please Login or Register  to view this content.