Help with formula, IF, AND, OR, etc.

[Guest]

I'm trying to make a formula that will calculate a markup on goods sold.
Items below $1 are marked up 500% (*6), $1.01-$3 are 400% (*5), $3.01 - $5.00
are 300% (*4), etc.

How can I create a formula that will look at our cost for an item in cell B1
and return the correct standard markup in another cell according to the
original cost?

I had started out with:

=OR(IF(B1<1, B1*6, 0)),IF(AND(B1>.99, B1<3.01), B1*5,0),IF(AND(B1>3.00,
B1<5.01),B1*4,0))

But it didn't work for me.

TIA

 

[Guest]

What happens to items that are > $5? Marked up by 200% (*3)? Here is a
formula you can use to your specifications so far, assuming that:

=A1*IF(A1<=1,6,IF(A1<=3,5,IF(A1<=5,4,3)))

 

[Guest]

Thank you David. I'm not clear on your formula. There are actually 10
levels of margins, with the highest one being for anything over $19.00. I
was hoping that when I got an answer I would be able to fill in the rest, but
I'm not getting your pattern.

Here are the general markup rates, if that would help.

<$1.00 = 500%
$1.01 - $3.00 = 400%(*5)
$3.01 - $5.00 = 300%(*4)
$5.01 - $7.00 = 200%(*3)
$7.01 - $9.00 = 100%(*2)
$9.01 - $11.00 = 90%(*1.9)
$11.01 - $13.00 = 80%(*1.8)
$13.01 - $15.00 = 60% (*1.6)
$15.01 - $17.00 = 50%(*1.5)
$17.01 - $19.00 = 40%(*1.4)
$19.01 - Up = 32%(*1.32)

 

[Bob Phillips]

Nancy,

Try this

=A1*VLOOKUP(A1,{0,6;1,5;3,4;5,3;7,2;9,1.9;11,1.8;13,1.6;15,1.5;17,1.4;19,1.3
2},2)

 

[Guest]

Here is a breakdown of my formula. I assumed your data was in A1 first of all:

=A1*IF(A1<=1,6,IF(A1<=3,5,IF(A1<=5,4,3)))

So you have your price in A1, and you are going to multiply it by either
6,5,4, or 3 depending on the price (this was before I knew all of your markup
rates). So breaking down the nested IF statements above we have:

If A1 is less than or equal to 1 then multiply by 6 else:
If A1 is less than or equal to 3 then multiply by 5 else:
If A1 is less than or equal to 5 then multiply by 4 else:
multiply by 3

Because eash subsequent If statement is nested as the ELSE condition, you
don't need to check for the "greater than" condition in your markup rates,
because it will automatically be true if it got to that spot in the formula.
Make sense?

Now, on to solving your problem. Excel will only handle 6 nested if
statements, so that won't work. You can use VLOOKUP() though for your
problem. Do this, enter the following in A1:A11

0
1.01
3.01
5.01
7.01
9.01
11.01
13.01
15.01
17.01
19.01

And enter this in B1:B11

6
5
4
3
2
1.9
1.8
1.6
1.5
1.4
1.32

And assume your price is in C1, use this formula:

=C1*VLOOKUP(C1,$A$1:$B$11,2,1)

Hopefully that makes enough sense that you can change the references to fit
your data. Otherwise post back.

 

[Guest]

I tried that in my sheet. A1 had a value of $0.50 and the result came back
as 0.00.

Thanks anyway...

 

[Bob Phillips]

It gives me 3!

--
HTH

RP

I tried that in my sheet. A1 had a value of $0.50 and the result came back
as 0.00.

Thanks anyway...