Easy one... but not for me :S

C

ChrisMattock

KO I have two columns, the first column (A) has some figures. The second
column (B) has one of three scenarios depending on the first column...

If A <10,000 then B will be 0
If A >10,000 but <100,000 B will be 300
If A > 100,000 then B = 3% of A

I'm sure this is easy, but I can't do it, I would greatly appreciate
any help you guys can give me.
 
R

Ron Rosenfeld

KO I have two columns, the first column (A) has some figures. The second
column (B) has one of three scenarios depending on the first column...

If A <10,000 then B will be 0
If A >10,000 but <100,000 B will be 300
If A > 100,000 then B = 3% of A

I'm sure this is easy, but I can't do it, I would greatly appreciate
any help you guys can give me.
Click to expand...


=IF(A1<10000,0,IF(A1<100000,300,A1*0.03))

Note that you did not define what you want to happen if A1 is exactly equal to
10,000 and/or 100,000. If my assumptions are incorrect as to what should
happen at these points, change the equality symbol accordingly.


--ron
 
C

ChrisMattock

Thanks for checking this post out, but I have worked it out now by
nesting If statements as follows...

=IF(A2<10000,0,IF(A2<100000,300,A2*0.03))
 
C

ChrisMattock

Ron is right, this does not yield the correct results.

The formula I mentioned in my second post is perfect, the way I phrased
the question was badly done though I admit... here is how it should be
phrased.

If A is less than 10,000 then B will be 0
If A greater than or equal to 10,000 but less than 100,000 B will be
300
If A greater than or equal to 100,000 then B = 3% of A

Therefore this IS the right formula for my needs...

=IF(A2<10000,0,IF(A2<100000,300,A2*0.03))

Thanks for all your input though guys.
 
A

Arvi Laanemets

Hi

OK, I didn't check it. Then
=(A1>=10000)*MAX(300;(A1>=100000)*A1*0.03)

But are you sure, you want such leap in percentages
A=99999 then B=300
A=100000 then B=3000
???

Arvi Laanemets


"ChrisMattock" <[email protected]>
wrote in message
news:[email protected]...
 
H

Harlan Grove

Arvi Laanemets wrote...
OK, I didn't check it. Then
=(A1>=10000)*MAX(300;(A1>=100000)*A1*0.03)
Click to expand...

If you're going to respond in English language newsgroups, use the
common English language argument separator, comma, NOT semicolon.
But are you sure, you want such leap in percentages
A=99999 then B=300
A=100000 then B=3000
???
Click to expand...

If the OP does want this level of percentage discontinuity, that'd be a
very good reason to avoid using MAX in this particular calculation.
It's possible to use only one IF call,

=IF(x<100000,(x>=10000)*300,x*0.03)

and it's possible to use no function calls at all,

=(x>=10000)*(x<100000)*300+(x>=100000)*x*0.03
 
Top