Formulas

[Guest]

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

 

[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)

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

Thanks Duke

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)

 

[Guest]

Thanks Jeff

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[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)

 

[Guest]

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
(e-mail address removed) ?

 

[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.

 

[Guest]

Thank you, I have had problems with this email address please try:
(e-mail address removed)

Once again thanks for your help

 

[Guest]

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

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

 

[Guest]

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

 

[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.