Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Hi all!
I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Let's say the first number is in A2
In B2 enter the formula =IF(A2<=100,(A2+5)*1.03, =(A2+10)*1.03) Copy this down the column (the quickest way is to double click B2's fill handle - the small solid square in lower right cornet when B2 is selected) Another formula that would work is =((A2<=100)*(A2+5) +(A2100)*(A2+10))*1.03 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Lori" wrote in message ... Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Hi,
Assuming your data starts in a1 put this in B1 and drag down as required =IF(A1<=100,(A1+5)*1.03,(A1+10)*1.03) Mike "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
=((A1100)*5+5+A1)*1.03
"Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Thank you thank you thank you! And it's no wonder I had a headache trying to
figure it out :) Um, would you believe I got straight A's in math related courses and was even taking college Algebra when I was in high school :o Thanks again!!! -- Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Oh crud...it's me again. I didn't put this in before.
0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
I should of course have said:
"in an out-of-the-way location, Y1 to Z9". Also, you weren't too clear on the math. I used the constant to multiply *after* adding the cell value to the variable. You could add the variable after multiplying the cell value with the constant: =1.03*A1+LOOKUP(A1,Y1:Z9) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... You mention 9 price breaks but didn't disclose them. Guessing at what they might be for this suggestion, create a datalist in an out-of-the-way location, say Y1 to X9: Y Z 0 5 100.01 10 225.01 15 400.01 20 550.01 25 700.01 30 850.01 35 1000.01 40 1200.01 45 Then, use this formula in B1: =1.03*(A1+LOOKUP(A1,Y$1:Z$9)) And copy down as needed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Lori" wrote in message ... Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Hi again,
And another short version, this time of the IF =1.03*(IF(A1100,2,IF(A1225,3,1))*5+A1) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Hi,
And I think if you want to get this one to work for what I think your trying to do then use =(A1+LOOKUP(A1,{0,100.01,225.01},{5,10,15}))*1.03 -- If this helps, please click the Yes button Cheers, Shane Devenshire "Teethless mama" wrote: =LOOKUP(A1,{0,100.01,225.01},{5,10,15})*1.03 "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
First I want to thank everyone who tried to help.
Second, I apparenlty need my hand held thru this whole process. I tried using the formula =(A1+LOOKUP(A1,{0,100.01,225.01},{5,10,15}))*1.03 but apparenlty don't know where to put it! Here's how my file is set up... It's saved as excel 97-03 workbook even tho i'm running it in excel 2007 It's got a frozen row on top A=manufacturer B=Item number C=to check item (column is actually blank) D=Inventory E=Check cost F=Cost (this is the one I need to calculate from. when I was trying the formula tho, I copied this column to a different sheet and had it in column A without any header) G=Price (this is where I need the calcualtions to end up) I tried going to Formulas-Insert Function. But since I have no idea what I'm doing I didn't know where to go from there :( I understand that I need to insert the correct column letter into the formula but other than that I'm still lost :( Here's what all the formulas (price breaks) will be. 0-50 plus 5 times 1.03 50-100 plus 8 times 1.03 100-225 plus 10 times 1.03 225-400 plus 15 times 1.03 400-575 plus 20 times 1.03 575-700 plus 30 times 1.03 700-1000 plus 40 times 1.03 1000-1550 plus 50 times 1.03 1550 and over times 1.06 (or if it's easier times 1.03 and times 1.03) Thank you in advance for helping me with this!!! -- Thanks :) Lori "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
I don't understand your formula (explanation) for the last value that's over
1550. Could you rephrase it? To see if I understand what you're looking for, let's temporarily say you only have 8 breaks, ending at over 1000. With your costs starting in F2, try this formula in G2: =F2+LOOKUP(F2,{0,50.01,100.01,225.01,400.01,575.01 ,700.01,1000.01;5.15,8.24, 10.3,15.45,20.6,30.9,41.2,51.5}) And copy down as needed. If this performs as you wish (up to the 1000 level), post back with a clarification of your maximum price break calculation, and I'll include it into the formula for you, if you don't think you can do it yourself. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lori" wrote in message ... First I want to thank everyone who tried to help. Second, I apparenlty need my hand held thru this whole process. I tried using the formula =(A1+LOOKUP(A1,{0,100.01,225.01},{5,10,15}))*1.03 but apparenlty don't know where to put it! Here's how my file is set up... It's saved as excel 97-03 workbook even tho i'm running it in excel 2007 It's got a frozen row on top A=manufacturer B=Item number C=to check item (column is actually blank) D=Inventory E=Check cost F=Cost (this is the one I need to calculate from. when I was trying the formula tho, I copied this column to a different sheet and had it in column A without any header) G=Price (this is where I need the calcualtions to end up) I tried going to Formulas-Insert Function. But since I have no idea what I'm doing I didn't know where to go from there :( I understand that I need to insert the correct column letter into the formula but other than that I'm still lost :( Here's what all the formulas (price breaks) will be. 0-50 plus 5 times 1.03 50-100 plus 8 times 1.03 100-225 plus 10 times 1.03 225-400 plus 15 times 1.03 400-575 plus 20 times 1.03 575-700 plus 30 times 1.03 700-1000 plus 40 times 1.03 1000-1550 plus 50 times 1.03 1550 and over times 1.06 (or if it's easier times 1.03 and times 1.03) Thank you in advance for helping me with this!!! -- Thanks :) Lori "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Anything over 1550 has a straight 3% markup. Every other cost has a dollar
amount mark up. The additional 3% is added on after to cover my merchant fees that I pay. Hopefully that will help make sense of the values I need :) So for anything over 1550 it either needs to go up 3% twice, or 6% once. Whichever is an easier formula. Where I'm really confused is where and how to put in the formula. Do I click "formulas" then "add function"? After that it asks for different values and I'm not sure what to put in. Sorry, but I've never worked with this kind of thing in excel and I'm really confused. -- Thanks :) Lori "Ragdyer" wrote: I don't understand your formula (explanation) for the last value that's over 1550. Could you rephrase it? To see if I understand what you're looking for, let's temporarily say you only have 8 breaks, ending at over 1000. With your costs starting in F2, try this formula in G2: =F2+LOOKUP(F2,{0,50.01,100.01,225.01,400.01,575.01 ,700.01,1000.01;5.15,8.24, 10.3,15.45,20.6,30.9,41.2,51.5}) And copy down as needed. If this performs as you wish (up to the 1000 level), post back with a clarification of your maximum price break calculation, and I'll include it into the formula for you, if you don't think you can do it yourself. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lori" wrote in message ... First I want to thank everyone who tried to help. Second, I apparenlty need my hand held thru this whole process. I tried using the formula =(A1+LOOKUP(A1,{0,100.01,225.01},{5,10,15}))*1.03 but apparenlty don't know where to put it! Here's how my file is set up... It's saved as excel 97-03 workbook even tho i'm running it in excel 2007 It's got a frozen row on top A=manufacturer B=Item number C=to check item (column is actually blank) D=Inventory E=Check cost F=Cost (this is the one I need to calculate from. when I was trying the formula tho, I copied this column to a different sheet and had it in column A without any header) G=Price (this is where I need the calcualtions to end up) I tried going to Formulas-Insert Function. But since I have no idea what I'm doing I didn't know where to go from there :( I understand that I need to insert the correct column letter into the formula but other than that I'm still lost :( Here's what all the formulas (price breaks) will be. 0-50 plus 5 times 1.03 50-100 plus 8 times 1.03 100-225 plus 10 times 1.03 225-400 plus 15 times 1.03 400-575 plus 20 times 1.03 575-700 plus 30 times 1.03 700-1000 plus 40 times 1.03 1000-1550 plus 50 times 1.03 1550 and over times 1.06 (or if it's easier times 1.03 and times 1.03) Thank you in advance for helping me with this!!! -- Thanks :) Lori "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Math first.
OK, I get the over 1550 calculation. But I may still have trouble with understanding your other calcs. You stated: <<<"0-50 plus 5 times 1.03" This is ambiguous. Do you add the 5 to the 50 and *then* multiply by 1.03, OR multiply 5 by 1.03, and add that answer to 50? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lori" wrote in message ... Anything over 1550 has a straight 3% markup. Every other cost has a dollar amount mark up. The additional 3% is added on after to cover my merchant fees that I pay. Hopefully that will help make sense of the values I need :) So for anything over 1550 it either needs to go up 3% twice, or 6% once. Whichever is an easier formula. Where I'm really confused is where and how to put in the formula. Do I click "formulas" then "add function"? After that it asks for different values and I'm not sure what to put in. Sorry, but I've never worked with this kind of thing in excel and I'm really confused. -- Thanks :) Lori "Ragdyer" wrote: I don't understand your formula (explanation) for the last value that's over 1550. Could you rephrase it? To see if I understand what you're looking for, let's temporarily say you only have 8 breaks, ending at over 1000. With your costs starting in F2, try this formula in G2: =F2+LOOKUP(F2,{0,50.01,100.01,225.01,400.01,575.01 ,700.01,1000.01;5.15,8.24, 10.3,15.45,20.6,30.9,41.2,51.5}) And copy down as needed. If this performs as you wish (up to the 1000 level), post back with a clarification of your maximum price break calculation, and I'll include it into the formula for you, if you don't think you can do it yourself. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Lori" wrote in message ... First I want to thank everyone who tried to help. Second, I apparenlty need my hand held thru this whole process. I tried using the formula =(A1+LOOKUP(A1,{0,100.01,225.01},{5,10,15}))*1.03 but apparenlty don't know where to put it! Here's how my file is set up... It's saved as excel 97-03 workbook even tho i'm running it in excel 2007 It's got a frozen row on top A=manufacturer B=Item number C=to check item (column is actually blank) D=Inventory E=Check cost F=Cost (this is the one I need to calculate from. when I was trying the formula tho, I copied this column to a different sheet and had it in column A without any header) G=Price (this is where I need the calcualtions to end up) I tried going to Formulas-Insert Function. But since I have no idea what I'm doing I didn't know where to go from there :( I understand that I need to insert the correct column letter into the formula but other than that I'm still lost :( Here's what all the formulas (price breaks) will be. 0-50 plus 5 times 1.03 50-100 plus 8 times 1.03 100-225 plus 10 times 1.03 225-400 plus 15 times 1.03 400-575 plus 20 times 1.03 575-700 plus 30 times 1.03 700-1000 plus 40 times 1.03 1000-1550 plus 50 times 1.03 1550 and over times 1.06 (or if it's easier times 1.03 and times 1.03) Thank you in advance for helping me with this!!! -- Thanks :) Lori "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
Add the 5 to the 50, then multiply by 1.03. The $5 is my profit, and the 3%
is to make sure I cover my merchant account fees. Yeah...that part sucks! I did start a new post to try to clear everything up cuz this one was getting confusing. I do really need to get this right so I don't mess up my entire site with pricing errors :o I'm acutally going to copy/paste to a completely different excel sheet so I'll be working from A1 to the B column. I have almost 10,000 products and can't be making a mess out of that one! -- Thanks :) Lori "Ragdyer" wrote: Math first. OK, I get the over 1550 calculation. But I may still have trouble with understanding your other calcs. You stated: <<<"0-50 plus 5 times 1.03" This is ambiguous. Do you add the 5 to the 50 and *then* multiply by 1.03, OR multiply 5 by 1.03, and add that answer to 50? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lori" wrote in message ... Anything over 1550 has a straight 3% markup. Every other cost has a dollar amount mark up. The additional 3% is added on after to cover my merchant fees that I pay. Hopefully that will help make sense of the values I need :) So for anything over 1550 it either needs to go up 3% twice, or 6% once. Whichever is an easier formula. Where I'm really confused is where and how to put in the formula. Do I click "formulas" then "add function"? After that it asks for different values and I'm not sure what to put in. Sorry, but I've never worked with this kind of thing in excel and I'm really confused. -- Thanks :) Lori "Ragdyer" wrote: I don't understand your formula (explanation) for the last value that's over 1550. Could you rephrase it? To see if I understand what you're looking for, let's temporarily say you only have 8 breaks, ending at over 1000. With your costs starting in F2, try this formula in G2: =F2+LOOKUP(F2,{0,50.01,100.01,225.01,400.01,575.01 ,700.01,1000.01;5.15,8.24, 10.3,15.45,20.6,30.9,41.2,51.5}) And copy down as needed. If this performs as you wish (up to the 1000 level), post back with a clarification of your maximum price break calculation, and I'll include it into the formula for you, if you don't think you can do it yourself. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Lori" wrote in message ... First I want to thank everyone who tried to help. Second, I apparenlty need my hand held thru this whole process. I tried using the formula =(A1+LOOKUP(A1,{0,100.01,225.01},{5,10,15}))*1.03 but apparenlty don't know where to put it! Here's how my file is set up... It's saved as excel 97-03 workbook even tho i'm running it in excel 2007 It's got a frozen row on top A=manufacturer B=Item number C=to check item (column is actually blank) D=Inventory E=Check cost F=Cost (this is the one I need to calculate from. when I was trying the formula tho, I copied this column to a different sheet and had it in column A without any header) G=Price (this is where I need the calcualtions to end up) I tried going to Formulas-Insert Function. But since I have no idea what I'm doing I didn't know where to go from there :( I understand that I need to insert the correct column letter into the formula but other than that I'm still lost :( Here's what all the formulas (price breaks) will be. 0-50 plus 5 times 1.03 50-100 plus 8 times 1.03 100-225 plus 10 times 1.03 225-400 plus 15 times 1.03 400-575 plus 20 times 1.03 575-700 plus 30 times 1.03 700-1000 plus 40 times 1.03 1000-1550 plus 50 times 1.03 1550 and over times 1.06 (or if it's easier times 1.03 and times 1.03) Thank you in advance for helping me with this!!! -- Thanks :) Lori "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up functions and rules
With your additional parameters included, try this in B1, and copy down as
needed: =IF(A1="","",IF(A11550,A1*1.06,(A1+LOOKUP(A1,{0.0 1,10.01,50.01,100.01,225.0 1,400.01,575.01,700.01,1000.01;2,5,8,10,15,20,30,4 0,50}))*1.03)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lori" wrote in message ... Add the 5 to the 50, then multiply by 1.03. The $5 is my profit, and the 3% is to make sure I cover my merchant account fees. Yeah...that part sucks! I did start a new post to try to clear everything up cuz this one was getting confusing. I do really need to get this right so I don't mess up my entire site with pricing errors :o I'm acutally going to copy/paste to a completely different excel sheet so I'll be working from A1 to the B column. I have almost 10,000 products and can't be making a mess out of that one! -- Thanks :) Lori "Ragdyer" wrote: Math first. OK, I get the over 1550 calculation. But I may still have trouble with understanding your other calcs. You stated: <<<"0-50 plus 5 times 1.03" This is ambiguous. Do you add the 5 to the 50 and *then* multiply by 1.03, OR multiply 5 by 1.03, and add that answer to 50? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Lori" wrote in message ... Anything over 1550 has a straight 3% markup. Every other cost has a dollar amount mark up. The additional 3% is added on after to cover my merchant fees that I pay. Hopefully that will help make sense of the values I need :) So for anything over 1550 it either needs to go up 3% twice, or 6% once. Whichever is an easier formula. Where I'm really confused is where and how to put in the formula. Do I click "formulas" then "add function"? After that it asks for different values and I'm not sure what to put in. Sorry, but I've never worked with this kind of thing in excel and I'm really confused. -- Thanks :) Lori "Ragdyer" wrote: I don't understand your formula (explanation) for the last value that's over 1550. Could you rephrase it? To see if I understand what you're looking for, let's temporarily say you only have 8 breaks, ending at over 1000. With your costs starting in F2, try this formula in G2: =F2+LOOKUP(F2,{0,50.01,100.01,225.01,400.01,575.01 ,700.01,1000.01;5.15,8.24, 10.3,15.45,20.6,30.9,41.2,51.5}) And copy down as needed. If this performs as you wish (up to the 1000 level), post back with a clarification of your maximum price break calculation, and I'll include it into the formula for you, if you don't think you can do it yourself. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Lori" wrote in message ... First I want to thank everyone who tried to help. Second, I apparenlty need my hand held thru this whole process. I tried using the formula =(A1+LOOKUP(A1,{0,100.01,225.01},{5,10,15}))*1.03 but apparenlty don't know where to put it! Here's how my file is set up... It's saved as excel 97-03 workbook even tho i'm running it in excel 2007 It's got a frozen row on top A=manufacturer B=Item number C=to check item (column is actually blank) D=Inventory E=Check cost F=Cost (this is the one I need to calculate from. when I was trying the formula tho, I copied this column to a different sheet and had it in column A without any header) G=Price (this is where I need the calcualtions to end up) I tried going to Formulas-Insert Function. But since I have no idea what I'm doing I didn't know where to go from there :( I understand that I need to insert the correct column letter into the formula but other than that I'm still lost :( Here's what all the formulas (price breaks) will be. 0-50 plus 5 times 1.03 50-100 plus 8 times 1.03 100-225 plus 10 times 1.03 225-400 plus 15 times 1.03 400-575 plus 20 times 1.03 575-700 plus 30 times 1.03 700-1000 plus 40 times 1.03 1000-1550 plus 50 times 1.03 1550 and over times 1.06 (or if it's easier times 1.03 and times 1.03) Thank you in advance for helping me with this!!! -- Thanks :) Lori "Lori" wrote: Oh crud...it's me again. I didn't put this in before. 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01-225 in column A. Add $10 and multiply by 1.03 for value in column B. 225.01-400 in column A. Add $15 and multiply by 1.03 for value in column B. I just want to be sure if I do something like put 100.01-225 in the formula you gave me it will work. Also, do I copy/paste the entire formula (there's a total of 9 different price breaks I need to use) down column B? Sorry if I'm sounding stupid, but I really can't mess this up :o -- Thanks again :) Lori "Lori" wrote: Hi all! I have a column with different numers (actually prices). I want to be able to set something up so each number can be given a certain value in the next column. This is hard to explain and I hope I make sense... Column A will have various numbers. I'd like to make a rule that says... 0-100 in column A. Add $5 and multiply by 1.03 to give me a value in column B. 100.01 in column A. Add $10 and multiply by 1.03 to give me a value in column B. Is this possible? I've researched until my head spins and can't figure out how to do it. I'm hoping someone here who know a lot more about Excel 2007 can help me out! -- Thanks :) Lori |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit fromsubroutine/functions | Excel Discussion (Misc queries) | |||
Confused about setting up functions based on variables? | Excel Worksheet Functions | |||
how do I set up a validation rules with two data rules | Excel Worksheet Functions | |||
Setting rules for y scale | Charts and Charting in Excel | |||
Need help setting up a formula using perhaps the logic functions . | Excel Worksheet Functions |