Formulas

R

Richard Sheoo

Please can any one help me?

I need to be able to work out a total price but the price will change as the
quantity increases.

For example 1 @ £1.99 5 @ £1.85 10 @ £1.75

Our customers may order say 6 units How do I tell excel to calulate this
properly?

Kind regards
Richard
 
B

Bob Phillips

=num_sold*LOOKUP(num_sold,{0,5,10},{1.99,1.85,1.75})

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

jeffbert

First, you have to set up a table with the fluctuating costs.

Qty Cost
1 1.99
5 1.85
10 1.75

Then, use the vlookup function but make sure set the last argument of the
function to "TRUE". If 4 units are ordered, the vlookup will return 1.99, if
5 are ordered, it will return 1.85.

Jeff
 
D

Duke Carey

So, if they order 6 do they get the 5 unit price for each of the 6?

If so, then A1:B3 contains
1 1.99
5 1.85
10 1.75

the quantity sold goes in A5, and the total price in B5 with this formula:

=a5*vlookup(A5,$a$1:$b$3,2)
 
R

Richard Sheoo

Thanks Duke

Duke Carey said:
So, if they order 6 do they get the 5 unit price for each of the 6?

If so, then A1:B3 contains
1 1.99
5 1.85
10 1.75

the quantity sold goes in A5, and the total price in B5 with this formula:

=a5*vlookup(A5,$a$1:$b$3,2)
Click to expand...
 
R

Richard Sheoo

Thanks Jeff

jeffbert said:
First, you have to set up a table with the fluctuating costs.

Qty Cost
1 1.99
5 1.85
10 1.75

Then, use the vlookup function but make sure set the last argument of the
function to "TRUE". If 4 units are ordered, the vlookup will return 1.99, if
5 are ordered, it will return 1.85.

Jeff
Click to expand...
 
R

Richard Sheoo

Hi Duke,

Still struggling. This calc has to be performed horizontally because there
are multiple lines and prices

Tot QTY Tot Val Price break 1st price Price Break 2nd
price
0 #N/A 1 €73.75 20 €69.95

I thought I was pretty useful in excel until this. Have you got any ideas?

Kind regards

Richard
 
R

Richard Sheoo

Hi Jeff,

Still struggling. This calc has to be performed horizontally because there
are multiple lines and prices

Tot QTY Tot Val Price break 1st price Price Break 2nd
price
0 #N/A 1 €73.75 20 €69.95

I thought I was pretty useful in excel until this. Have you got any ideas?

Kind regards

Richard
 
R

Richard Sheoo

Hi Bob,

Still struggling. This calc has to be performed horizontally because there
are multiple lines and prices

Tot QTY Tot Val Price break 1st price Price Break 2nd
price
0 #N/A 1 €73.75 20 €69.95

I thought I was pretty useful in excel until this. Have you got any ideas?

Kind regards

Richard
 
R

Roger Govier

Hi Richard

Whether your price break data is horizontal or vertical, in needs to be
in 2 distinct rows or columns.
You cannot have it all in the same row.

if you had in C1 1, D1 20, E1 100
and in C2 ?73.75, ?69.95 , ? 65.00
Then with Qty entered in A3
in A4
=HLOOKUP(A3,$C$1;$E$2,2,0)
 
R

Richard Sheoo

Hi Roger,

Thanks for your help. But I still can't get this to work. I ah ve typed out
your solution but no dice could you perhaps send it to me in a work book to
[email protected] ?
 
R

Roger Govier

Hi Richard

My apologies that should have been True (1) for the 4th argument, not
False (0)
=HLOOKUP(A3,$C$1;$E$2,2,1)

Test file on its way direct to you.
 
R

Richard Sheoo

Dude!!!!!!!!!!!!!

Thanks It works I think I eve understnad it becuase I expanded it to 4 & 5
price breaks!
 
R

Richard Sheoo

I tried to transfer it to my spread sheet and it didnt work. Argghh!

Could you send me your test sheet and I will try and figure out what I'm
doing wrong.

Kind regards

Richard
 
R

Roger Govier

Hi Richard

Both of those email addresses resulted a bounce back.
If you want to send an email direct to me, I will respond with the file.

Remove NOSPAM from my address to send.
 
Top