vlookup?

I want to calculate the cost of an item based on the number of units sold. In other words, 0-10 is $3, 11-20 is $2, 21-30 is $1, and so on. How do i get excel to compare the inputted number of items to the above range, and multiply by the associated price?

Hi,

Make a matrix containing the starting number in the range and the price.
For eg.

No.of units price
0 3
11 2

etc and use the formula

=D4*VLOOKUP(D4,$A$1:$B$3,2,TRUE)

where D4 is the given quantity and A1 and B3 is the range where you had
listed out the quantity & prices.

Regards

Govind.


zkayess wrote:
> I want to calculate the cost of an item based on the number of units
> sold. In other words, 0-10 is $3, 11-20 is $2, 21-30 is $1, and so on.
> How do i get excel to compare the inputted number of items to the above
> range, and multiply by the associated price?
>
>

Start by creating your data list.

Say in G1 to G6 you enter your quantities:
0, 1, 11, 21, 31, 41

And in H1 to H6 you enter your prices:
0, 3, 2, 1, 0.5, 0.25

With your quantity in B1, try this formula to get your cost:
=LOOKUP(B1,G1:G6,H1:H6)*B1

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"zkayess" <[email protected]> wrote in
message news:[email protected]...
>
> I want to calculate the cost of an item based on the number of units
> sold. In other words, 0-10 is $3, 11-20 is $2, 21-30 is $1, and so on.
> How do i get excel to compare the inputted number of items to the above
> range, and multiply by the associated price?
>
>
> --
> zkayess
> ------------------------------------------------------------------------
> zkayess's Profile:
http://www.excelforum.com/member.php...o&userid=17517
> View this thread: http://www.excelforum.com/showthread...hreadid=391688
>

Hi. Try this tutorial:
http://www.officearticles.com/tutori...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"zkayess" <[email protected]> wrote in
message news:[email protected]...
>
> I want to calculate the cost of an item based on the number of units
> sold. In other words, 0-10 is $3, 11-20 is $2, 21-30 is $1, and so on.
> How do i get excel to compare the inputted number of items to the above
> range, and multiply by the associated price?
>
>
> --
> zkayess
> ------------------------------------------------------------------------
> zkayess's Profile:
http://www.excelforum.com/member.php...o&userid=17517
> View this thread: http://www.excelforum.com/showthread...hreadid=391688
>

Thanks to all for your invaluable help...ended up using RD's suggestion and it worked perfectly. Thx to Anne and Govind.
zkayess

Thanks for the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"zkayess" <[email protected]> wrote in
message news:[email protected]...

Thanks to all for your invaluable help...ended up using RD's suggestion
and it worked perfectly. Thx to Anne and Govind.
zkayess


--
zkayess
------------------------------------------------------------------------
zkayess's Profile:
http://www.excelforum.com/member.php...o&userid=17517
View this thread: http://www.excelforum.com/showthread...hreadid=391688