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?


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

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

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

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


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

Thanks for the feed-back.
--

Regards,

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

"zkayess" wrote in
message ...

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