Can some one help me with a formula please?

[stewartlogan]

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

 

[Guest]

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

 

[Pete]

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

 

[stewartlogan]

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.

 

[Guest]

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]

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]

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

 

[via135]

it's amazing!

what is the use in stoop down to explain to someone in the excelforum
who never used excel ???!!!!



-via135

 

[Pete]

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

 

[Guest]

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

 

[Richard O. Neville]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[via135]

well set Pete!

-via135

 

[Guest]

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

 

[stewartlogan]

thanks very much for all the helpful responses. got it working.

 

[Guest]

Happy to help, and thanks for the feedback.............

Vaya con Dios,
Chuck, CABGx3