Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel multiply by different values based on quantity
I would like to multiply the value of a cell based on quantity.
Think of it as paying more (higher multiplication factor) for a few pounds of something, and paying less for bigger quantity (lower multiplication factor) and then and paying far less still for a huge quantity (even lower multiplication factor) I need to have at least three variables to make my factors work. THANKS! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel multiply by different values based on quantity
Hi
There are many ways to tackle this problem. The following will allow you to create as many price breaks as you wish, without needing to amend the formula. Somewhere on a sheet set up a table in 2 columns like the following (I use cells E1:F4 for this example) 0 100 100 80 500 70 1000 65 This would be charging 100 per unit for quantities up to 99, 80 per unit for quantities 100 to 499, 70 for quantities 500 to 999 and 65 per unit for any quantity above 1000 Now if you enter the quantity in cell A1, in cell B1 enter =A1*VLOOKUP(A1,$E$1:$F$4,2) Change value and locations to suit your needs. If you need more price breaks, extend the table. If you made the formula =A1*VLOOKUP(A1,$E$1:$F$4,2) from the outset, then you would have 6 more rows available to have further price breaks available to you. -- Regards Roger Govier "ronzander1" wrote in message ... I would like to multiply the value of a cell based on quantity. Think of it as paying more (higher multiplication factor) for a few pounds of something, and paying less for bigger quantity (lower multiplication factor) and then and paying far less still for a huge quantity (even lower multiplication factor) I need to have at least three variables to make my factors work. THANKS! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging different quantites of labels based on Excel data | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) | |||
Missing values in Excel Line Chart | Charts and Charting in Excel | |||
project values based on other values | Excel Discussion (Misc queries) | |||
If I have X,Y data how do I sum the Y values using a set of bins based on x values | Excel Worksheet Functions |