Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |