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

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

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

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

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

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

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

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.....

--
Wag more, bark less


"Rover" wrote:

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

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

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

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

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?