Formula

[Rover]

I need some help, as I have not able to come up with a formula. Any
assistance would be greatly appreciated...

So lets say you have a value in cell A1...I need a formula in cell B1
calculating based on the value of cell A1 with the folowing conditions...If
the value of A1 is less than 500, I need to multiply it by 40%; if the value
of A1 is between 500 and 750, I need to multiply it by 35%; if the value is
between 750 and 1000 need to multiply it by 30% and if its greater than 1000,
then multiply it by 20%...

Thanks for the Help...Fred

 

[Brad]

here is one way
=if(a1<500,a1*.4,if(a1<750,a1*.35,if(a1<1000,a1*.3,a1*.2)))

 

[jlclyde]

I need some help, as I have not able to come up with a formula. Any
assistance would be greatly appreciated...

So lets say you have a value in cell A1...I need a formula in cell B1
calculating based on the value of cell A1 with the folowing conditions...If
the value of A1  is less than 500, I need to multiply it by 40%; if the value
of A1 is between 500 and 750, I need to multiply it by 35%; if the value is
between 750 and 1000 need to multiply it by 30% and if its greater than 1000,
then multiply it by 20%...

Thanks for the Help...Fred

You may want to play around with the >= and <=. Less than or equal
2. If there is 750 in 2 of the conditions it coudl throw some weird
results. So you should chose if it is in the first condition or the
second. Here is a formual I came up with to do what you had
proposed.
=IF(A1<500,A1*.4,IF(AND(A1=>500,A1=<750)=TRUE,A1*.
35,IF(AND(A1>750,A1=<1000)=TRUE,A1*.2)))
Jay

 

[Rover]

Thanks Brad...How do I calculate for greater than and smaller than...ie: if
its greater than 500 but less than 750...

 

[Brad]

The if statement provided takes care of the greather than (or equal to) 500
and less than 750.

HTH

 

[David Biddulph]

What you asked for (and got) was for A1<500 to give 40%. The 35% then
applies for A1>=500 and <750.
[If you want the A1 to be for >500, rather than >=500, you can change the
40% criterion to <=500 instead of <500, but if you want a different result
to apply for A1=500 (i.e. not the same as for <500 or for >500), then you'll
need to specify that result.]

Have you tried the formula you were given?

 

[Rover]

Is there another way of doing it? The reason I ask is because I inserted the
formula and set-up a 'check' to make sure the numbers were right, but some
were not...

 

[Brad]

Can you provide an example where this doesn't work and I can help you more...

I tested it and it does work - my results

Original Modified
Number number multiplier
200 80 0.4
400 160 0.4
600 210 0.35
800 240 0.3
1000 200 0.2
1200 240 0.2

 

[Rover]

Thanks very much...both formulas work...I tried the first suggestion three
times before it would work, not really sure why...the second also worked when
i changed the criteria to the numbers to was using...thanks again...

 

[Brad]

You're very welcome, sometimes getting the syntax correct can be difficult.
One thing that you could do is to answer yes to the question that reads
something like "Did this post answer the question?"

I'd appreciate it.....

 

[Gord Dibben]

Alternative to the "IF"'s

=LOOKUP(A1,{0,501,751,1001},{0.4,0.35,0.3,0.2})*A1


Gord Dibben MS Excel MVP

 

[Brad]

Gord,

Question for you, using your formula - if the values aren't integers and
could be negative would you do this?
=LOOKUP(C9,{-9.99999999999999E+99,500,750,1000},{0.4,0.35,0.3,0.2})*C9

or would do something else?

 

[David Biddulph]

A few unnecessary segments in your formula, jclyde, as well as syntax errors
and missing outcome.

You don't need the =TRUE in
IF(AND(A1=>500,A1=<750)=TRUE,...
as you could get away with
IF(AND(A1=>500,A1=<750),...

Also you don't need the A1=>500 test, as you've already tested for A1<500.
Note also that the syntax would be >=500, not =>500.

Similarly in later parts of the formula.

Also you missed out the OP's requested 30% result for 750 to 1000.

Instead of
=IF(A1<500,A1*.4,IF(AND(A1=>500,A1=<750)=TRUE,A1*.35,IF(AND(A1>750,A1=<1000)=TRUE,A1*.2)))try =IF(A1<500,A1*40%,IF(A1<=750,A1*35%,IF(A1<=1000,A1*30%,A1*20%)))--David Biddulph"jlclyde" <[email protected]> wrote in messageApr 22, 8:36 am, Rover <[email protected]> wrote:> I need some help, as I have not able to come up with a formula. Any> assistance would be greatly appreciated...>> So lets say you have a value in cell A1...I need a formula in cell B1> calculating based on the value of cell A1 with the folowingconditions...If> the value of A1 is less than 500, I need to multiply it by 40%; if thevalue> of A1 is between 500 and 750, I need to multiply it by 35%; if the valueis> between 750 and 1000 need to multiply it by 30% and if its greater than1000,> then multiply it by 20%...>> Thanks for the Help...FredYou may want to play around with the >= and <=. Less than or equal2. If there is 750 in 2 of the conditions it coudl throw some weirdresults. So you should chose if it is in the first condition or thesecond. Here is a formual I came up with to do what you hadproposed.=IF(A1<500,A1*.4,IF(AND(A1=>500,A1=<750)=TRUE,A1*.35,IF(AND(A1>750,A1=<1000)=TRUE,A1*.2)))Jay

 

[Gord Dibben]

Do you have a few examples of values that aren't integers and are negative.

I'm not sure what you're asking.


Gord