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

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



"Richard Sheoo" wrote in message
...

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

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


"Richard Sheoo" wrote:


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

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)


"Richard Sheoo" wrote:


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

Thanks Duke

"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

Thanks Jeff

"jeffbert" wrote:

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


"Richard Sheoo" wrote:


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

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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

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


"jeffbert" wrote:

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


"Richard Sheoo" wrote:


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

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


"Bob Phillips" wrote:

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



"Richard Sheoo" wrote in message
...

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

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)

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

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
?

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

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.

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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
?

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

Thank you, I have had problems with this email address please try:


Once again thanks for your help
"Roger Govier" wrote:

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.

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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
?

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

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

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

"Roger Govier" wrote:

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.

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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
?

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

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" wrote:

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.

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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
?

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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

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.

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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
?

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Richard Sheoo" wrote in message
...
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


"Duke Carey" wrote:

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)


"Richard Sheoo" wrote:


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