How do I create an If/or formula with more then two answers?
 

I am really close to an accurate conditional formula, but it seems to be
missing the middle part of the formula when put into action. The scenerio is
this:
Column "C" are all prices on my spreadsheet.
I need shipping and handling of .6 multiplied to each price plus three
different margins depending on price. (anything over $2.00 is to have a 30%
margin, anything between $1.50 and $2.00 is to have a 35% margin, and
everything below $1.50 is to have a 40% margin) SO, MY FORMULA LOOKS LIKE
THIS:
=IF(C62,C6*.6/.70,IF(OR(C6<2,C61.5,C6*.6/.65,C6*.6/.60))

This formula seems to pick up all prices over $2.00 and all prices below
$1.50, but if the price in column "C" is between $1.50 and $2.00 it doesn't
pick up the middle of this formula to give me the margin of 35%?

Can someone figure out what I need to change to get this middle argument to
work?

I really appreciate it,

Lisa

How do I create an If/or formula with more then two answers?
 

The formula doesn't pick up the middle part because you don't have the
correct parentheses set up. Take a look at the parentheses you have on the
right. The right-most one finishes the second IF() statement. The one the
second furthest to the right finishes the OR() statement. But your first
IF(), all the way on the left, does not have a corresponding closing
parenthetical.

Put the final closing parenthetical on the right hand side of the formula,
and see where that gets you.

Dave
--
Brevity is the soul of wit.


"LisaLisaKK" wrote:

I am really close to an accurate conditional formula, but it seems to be
missing the middle part of the formula when put into action. The scenerio is
this:
Column "C" are all prices on my spreadsheet.
I need shipping and handling of .6 multiplied to each price plus three
different margins depending on price. (anything over $2.00 is to have a 30%
margin, anything between $1.50 and $2.00 is to have a 35% margin, and
everything below $1.50 is to have a 40% margin) SO, MY FORMULA LOOKS LIKE
THIS:
=IF(C62,C6*.6/.70,IF(OR(C6<2,C61.5,C6*.6/.65,C6*.6/.60))

This formula seems to pick up all prices over $2.00 and all prices below
$1.50, but if the price in column "C" is between $1.50 and $2.00 it doesn't
pick up the middle of this formula to give me the margin of 35%?

Can someone figure out what I need to change to get this middle argument to
work?

I really appreciate it,

Lisa

How do I create an If/or formula with more then two answers?
 

You need to change the OR to AND, and you've also missed a bracket.
Change your formula to:

=IF(C62,C6*.6/.70,IF(AND(C6<=2,C61.5),C6*.6/.65,C6*.6/.60))

Hope this helps.

Pete


LisaLisaKK wrote:
I am really close to an accurate conditional formula, but it seems to be
missing the middle part of the formula when put into action. The scenerio is
this:
Column "C" are all prices on my spreadsheet.
I need shipping and handling of .6 multiplied to each price plus three
different margins depending on price. (anything over $2.00 is to have a 30%
margin, anything between $1.50 and $2.00 is to have a 35% margin, and
everything below $1.50 is to have a 40% margin) SO, MY FORMULA LOOKS LIKE
THIS:
=IF(C62,C6*.6/.70,IF(OR(C6<2,C61.5,C6*.6/.65,C6*.6/.60))

This formula seems to pick up all prices over $2.00 and all prices below
$1.50, but if the price in column "C" is between $1.50 and $2.00 it doesn't
pick up the middle of this formula to give me the margin of 35%?

Can someone figure out what I need to change to get this middle argument to
work?

I really appreciate it,

Lisa

How do I create an If/or formula with more then two answers?
 

"LisaLisaKK" wrote in message
...
I am really close to an accurate conditional formula, but it seems to be
missing the middle part of the formula when put into action. The scenerio
is
this:
Column "C" are all prices on my spreadsheet.
I need shipping and handling of .6 multiplied to each price plus three
different margins depending on price. (anything over $2.00 is to have a
30%
margin, anything between $1.50 and $2.00 is to have a 35% margin, and
everything below $1.50 is to have a 40% margin) SO, MY FORMULA LOOKS LIKE
THIS:
=IF(C62,C6*.6/.70,IF(OR(C6<2,C61.5,C6*.6/.65,C6*.6/.60))

This formula seems to pick up all prices over $2.00 and all prices below
$1.50, but if the price in column "C" is between $1.50 and $2.00 it
doesn't
pick up the middle of this formula to give me the margin of 35%?

Can someone figure out what I need to change to get this middle argument
to
work?

Apart from anything else, aren't you missing the closing parenthesis for the
arguments of your OR function?

Just correcting that would give you
=IF(C62,C6*0.6/0.7,IF(OR(C6<2,C61.5),C6*0.6/0.65,C6*0.6/0.6))

But I think you probably want
=IF(C62,C6*0.6/0.7,IF(C6<1.5,C6*0.6/0.6,C6*0.6/0.65))

You may also need to check again what you want to do if the input is equal
to 1.50 or equal to 2.00; I've assumed that you've regarded those as
"between".
--
David Biddulph

How do I create an If/or formula with more then two answers?
 

Dave,
When I add the additional parantheses, I get this message:

"You've entered too many functions for this argument"

I need to find out what I am missing from the middle part of this formula?

I know it is something very basic.

I thank you for trying to help,

Lisa


"Dave F" wrote:

The formula doesn't pick up the middle part because you don't have the
correct parentheses set up. Take a look at the parentheses you have on the
right. The right-most one finishes the second IF() statement. The one the
second furthest to the right finishes the OR() statement. But your first
IF(), all the way on the left, does not have a corresponding closing
parenthetical.

Put the final closing parenthetical on the right hand side of the formula,
and see where that gets you.

Dave
--
Brevity is the soul of wit.


"LisaLisaKK" wrote:

I am really close to an accurate conditional formula, but it seems to be
missing the middle part of the formula when put into action. The scenerio is
this:
Column "C" are all prices on my spreadsheet.
I need shipping and handling of .6 multiplied to each price plus three
different margins depending on price. (anything over $2.00 is to have a 30%
margin, anything between $1.50 and $2.00 is to have a 35% margin, and
everything below $1.50 is to have a 40% margin) SO, MY FORMULA LOOKS LIKE
THIS:
=IF(C62,C6*.6/.70,IF(OR(C6<2,C61.5,C6*.6/.65,C6*.6/.60))

This formula seems to pick up all prices over $2.00 and all prices below
$1.50, but if the price in column "C" is between $1.50 and $2.00 it doesn't
pick up the middle of this formula to give me the margin of 35%?

Can someone figure out what I need to change to get this middle argument to
work?

I really appreciate it,

Lisa