Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula, IF, AND, OR, etc.
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(B13.00, B1<5.01),B1*4,0)) But it didn't work for me. TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula, IF, AND, OR, etc.
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))) -- Regards, Dave "Nancy" wrote: 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(B13.00, B1<5.01),B1*4,0)) But it didn't work for me. TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula, IF, AND, OR, etc.
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) "David Billigmeier" wrote: 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))) -- Regards, Dave "Nancy" wrote: 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(B13.00, B1<5.01),B1*4,0)) But it didn't work for me. TIA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula, IF, AND, OR, etc.
Nancy,
Try this =A1*VLOOKUP(A1,{0,6;1,5;3,4;5,3;7,2;9,1.9;11,1.8;1 3,1.6;15,1.5;17,1.4;19,1.3 2},2) -- HTH RP "Nancy" wrote in message ... 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) "David Billigmeier" wrote: 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))) -- Regards, Dave "Nancy" wrote: 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(B13.00, B1<5.01),B1*4,0)) But it didn't work for me. TIA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula, IF, AND, OR, etc.
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. -- Regards, Dave "Nancy" wrote: 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) "David Billigmeier" wrote: 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))) -- Regards, Dave "Nancy" wrote: 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(B13.00, B1<5.01),B1*4,0)) But it didn't work for me. TIA |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula, IF, AND, OR, etc.
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" wrote: Nancy, Try this =A1*VLOOKUP(A1,{0,6;1,5;3,4;5,3;7,2;9,1.9;11,1.8;1 3,1.6;15,1.5;17,1.4;19,1.3 2},2) -- HTH RP "Nancy" wrote in message ... 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) "David Billigmeier" wrote: 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))) -- Regards, Dave "Nancy" wrote: 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(B13.00, B1<5.01),B1*4,0)) But it didn't work for me. TIA |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula, IF, AND, OR, etc.
It gives me 3!
-- HTH RP "Nancy" wrote in message ... 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" wrote: Nancy, Try this =A1*VLOOKUP(A1,{0,6;1,5;3,4;5,3;7,2;9,1.9;11,1.8;1 3,1.6;15,1.5;17,1.4;19,1.3 2},2) -- HTH RP "Nancy" wrote in message ... 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) "David Billigmeier" wrote: 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))) -- Regards, Dave "Nancy" wrote: 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(B13.00, B1<5.01),B1*4,0)) But it didn't work for me. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|