Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
Hey folks - sorry to barge in. I am a new user of excel and am trying to create a 6500 product spreadsheet for a website i am building. What i want to do is as follows: I have a column with my retail price. I want to create a column with my postage price. I want the postage price to be proportionate to the retail price. Example, if retail is less than £5 postage is £2.99. If retail price is more than £5 but less than £10, postage price is £3.99, if retail price is more than £10 but less than £15, postage price is £5.99 and so on and so forth. Question is, how to i put this in a formula. Tried lots of ways but keep messing it up. I would really really appreciate someone helping me out!! Thanks in advance Stewart -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
Not trying to be argumentative, but your sample data does not support your
desire for a "proportionate" scaling. For example, price of 9.99 could cost 3.99 to ship, and cost of 10.01 wouls cost 5.99 to ship, while cost of exactly 10 would be free to ship. Please give either a percentage of the cost (with a minimum if you desire) or a schedule of postage ranges for as far out as you wish them to go...... thanks, Vaya con Dios, Chuck, CABGx3 "stewartlogan" wrote: Hey folks - sorry to barge in. I am a new user of excel and am trying to create a 6500 product spreadsheet for a website i am building. What i want to do is as follows: I have a column with my retail price. I want to create a column with my postage price. I want the postage price to be proportionate to the retail price. Example, if retail is less than £5 postage is £2.99. If retail price is more than £5 but less than £10, postage price is £3.99, if retail price is more than £10 but less than £15, postage price is £5.99 and so on and so forth. Question is, how to i put this in a formula. Tried lots of ways but keep messing it up. I would really really appreciate someone helping me out!! Thanks in advance Stewart -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
I agree with Chuck - your definitions are a bit lax. However, you can
use a simple lookup table to give you what you want. For example, set up a table like this: 0.00 2.99 5.00 3.99 10.00 5.99 etc Assume that it occupies cells L1 to M3 and that your retail price is in column A. In cell B1 you can enter the following formula: =VLOOKUP(A1,$L$1:$M$3,2,1) and copy down your 6,500 rows. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
sorry to be so vague - the figures were simply plucked from the sky - thought once i new how to do the function i could work on the figures. -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
That's ok, it's just harder to figure out what you want that way..........it
still could be either Pete's formula, or maybe something like this........ =MAX(2.99,2.99+(A1-5)*0.2) Vaya con Dios, Chuck, CABGx3 "stewartlogan" wrote: sorry to be so vague - the figures were simply plucked from the sky - thought once i new how to do the function i could work on the figures. -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
I really could do with some specific help with this so can i tell you exactly what i want to do in the hope that someone can bail me out. I have my retail price in column A2 to A6500 I have my postage cost in column B2 TO B6500 What i need is a formula to calculate the postage for each individual retail price based on the following criteria. If retail = £5 or below, post = £2.99 If retail = £5.01 to £9.99, post = £3.99 If retail = £10 to £14.99, post = £4.99 If retail = £15 to £19.99, post = £5.99 If retail = £20 to £24.99, post = £7.99 If retail = £25 to £30.00, post = £9.99 If retail = £30 or above, post = £12.99 What i need to know is how i caluclate the above as a formula that i can cut and paste down 6500 different products. Would genuinely appreciate some help -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
thanks for your reply Chuck - really appreciate your effort. Problem is, i havent got a clue what you mean can someone please explain in simple terms for me - like i say, ive never used excel before -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
it's amazing! what is the use in stoop down to explain to someone in the excelforum who never used excel ???!!!! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
If you read my earlier posting you would have your answer, but lets's
go through it again. Set up a table in cells L1 to M7 comprising: 0 2.99 5.01 3.99 10 4.99 15 5.99 20 7.99 25 9.99 30 12.99 This is essentially defining the bands for your postage charge, i.e. everything from £0 up to £5.00 will be £2.99, from £5.01 to £9.99 will be £3.99 etc. Then in B2 enter the formula: =VLOOKUP(A2,$L$1:$M$7,2,1) Format this cell as currency with 2 dp and £ at the beginning. Copy the formula down to B6500 - the easiest way to do this with your retail price in column A is to click on B2 then double-click the "fill handle", which is the small black square at the bottom right corner of the selected cell. That's all there is to it. Hope this helps. Pete |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
Put this formula in B2 and copy and paste it down column B as far as you have
data in column A.........a quick way to do that is to , after it's entered, just left click on B2 and then double left-click on the little black square in the lower right hand corner of B2......the formula will autocopy down column B =IF(A2=30,12.99,IF(A2=25,9.99,IF(A2=20,7.99,IF( A2=15,5.99,IF(A2=10,4.99,IF(A2=5,3.99,2.99))))) ) Note, this is a long formula, and should all be entered in B2 in one long line......be careful of email word-wrap that may carry it over to two lines.......carefully hand type it in B2 if necessary. hth Vaya con Dios, Chuck, CABGx3 "stewartlogan" wrote: thanks for your reply Chuck - really appreciate your effort. Problem is, i havent got a clue what you mean can someone please explain in simple terms for me - like i say, ive never used excel before -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
This requires a series of nested IF functions. It would start with this
(assuming "retail" is in cell C2): =IF(C2<£5.00),£2.99,IF(C2£4.99<£10.00),£3.99,etc, etc. Be sure to put enough )))) marks at the end of the formula. The Excel help function is very useful on this; press the F1 key and in the Answer Wizard type "Functions." Then select "IF worksheet function" for more information. Also note that if postage for the first increment is less than £5.00, the next argument must start with a retail price of £4.99, not £5.00. Otherwise the formula won't know how to treat a retail price of exactly £5.00. "stewartlogan" wrote in message news:stewartlogan.22yzre_1139502041.4543@excelforu m-nospam.com... Hey folks - sorry to barge in. I am a new user of excel and am trying to create a 6500 product spreadsheet for a website i am building. What i want to do is as follows: I have a column with my retail price. I want to create a column with my postage price. I want the postage price to be proportionate to the retail price. Example, if retail is less than £5 postage is £2.99. If retail price is more than £5 but less than £10, postage price is £3.99, if retail price is more than £10 but less than £15, postage price is £5.99 and so on and so forth. Question is, how to i put this in a formula. Tried lots of ways but keep messing it up. I would really really appreciate someone helping me out!! Thanks in advance Stewart -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
well set Pete! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
Or maybe this one, if you prefer......
=LOOKUP(A2,{0,4.99,9.99,14.99,19.99,24.99,29.99},{ 2.99,3.99,4.99,5.99,7.99,9.99,12.99}) Vaya con Dios, Chuck, CABGx3 "CLR" wrote: Put this formula in B2 and copy and paste it down column B as far as you have data in column A.........a quick way to do that is to , after it's entered, just left click on B2 and then double left-click on the little black square in the lower right hand corner of B2......the formula will autocopy down column B =IF(A2=30,12.99,IF(A2=25,9.99,IF(A2=20,7.99,IF( A2=15,5.99,IF(A2=10,4.99,IF(A2=5,3.99,2.99))))) ) Note, this is a long formula, and should all be entered in B2 in one long line......be careful of email word-wrap that may carry it over to two lines.......carefully hand type it in B2 if necessary. hth Vaya con Dios, Chuck, CABGx3 "stewartlogan" wrote: thanks for your reply Chuck - really appreciate your effort. Problem is, i havent got a clue what you mean can someone please explain in simple terms for me - like i say, ive never used excel before -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
thanks very much for all the helpful responses. got it working.:) -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can some one help me with a formula please?
Happy to help, and thanks for the feedback.............
Vaya con Dios, Chuck, CABGx3 "stewartlogan" wrote: thanks very much for all the helpful responses. got it working.:) -- stewartlogan ------------------------------------------------------------------------ stewartlogan's Profile: http://www.excelforum.com/member.php...o&userid=31371 View this thread: http://www.excelforum.com/showthread...hreadid=510637 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |