|
Formulas
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 |
Formulas
=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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
Formulas
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 |
| All times are GMT +1. The time now is 05:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com