# excel multiply by different values based on quantity

1. ## 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. ## Re: 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" <ronzander1@discussions.microsoft.com> 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!
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1