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

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

"Rover" 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 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

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

"Brad" wrote:

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

"Rover" 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 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

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

HTH


"Rover" wrote:

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

"Brad" wrote:

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

"Rover" 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 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

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" wrote:

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

HTH


"Rover" wrote:

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

"Brad" wrote:

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

"Rover" 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 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

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

--
Wag more, bark less


"Rover" wrote:

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" wrote:

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

HTH


"Rover" wrote:

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

"Brad" wrote:

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

"Rover" 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 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

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?
--
David Biddulph

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

"Brad" wrote:

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

"Rover" 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 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

On Apr 22, 8:36*am, Rover 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 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(A1750,A1=<1000)=TRUE,A1*.2)))
Jay

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(A1750,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" wrote in Apr 22, 8:36 am, Rover 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=<75 0)=TRUE,A1*.35,IF(AND(A1750,A1=<1000)=TRUE,A1*.2) ))Jay

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

On Tue, 22 Apr 2008 06:36:00 -0700, Rover
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 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

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?

--
Wag more, bark less


"Gord Dibben" wrote:

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

On Tue, 22 Apr 2008 06:36:00 -0700, Rover
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 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

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

I'm not sure what you're asking.


Gord

On Tue, 22 Apr 2008 11:26:01 -0700, Brad wrote:

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?