I would like to be able to do a lookup of a range of values, how would I set up the formula? IE I am trying to say if my number of widgets is 10,500 what would my price be when I have a band of values for one price point.
So lets say volume bands are:
Price
1 to 10,000 $3.00
10,001 to 30,000 $2.00
30,001 to 100,000 $1.00
2007-03-05 08:45:17 · 4 answers · asked by Noonan 1 in Computers & Internet ➔ Software
It can be done by two formulas
Now
Assuming your table is in columns B, C and D, then in E1 paste this
=IF( AND( $G$2> B1, $G$2 <= C1), D1, 0)
then fill this down to the end of the table
Then, in G2, type the number you want to check, and in H2 paste this
=SUM(E:E)
Trust me, I am the VBAXLMan
2007-03-05 22:55:48 · answer #1 · answered by Anonymous · 0⤊ 0⤋
Assuming you have Set1 in Sheet1 ranging from A1 and Set2 in Sheet2 ranging from A1 Then in Sheet2 cellular E2 paste this =A1&B1&C1 and fill it down Then in Sheet1 D2 (it rather is the voltage for Set1) paste this =INDEX( Sheet2!D:D, experience( A1&B1&C1, Sheet2!E:E,0),a million) And fill it down it rather is all VBAXLMan is back to feed your Excel needs
2016-09-30 06:02:14 · answer #2 · answered by fryback 4 · 0⤊ 0⤋
A1: 1
A2: 10001
A3: 30001
B1: 3
B2: 2
B3: 1
C1: 10500
To calculate total price in some other cell:
=VLOOKUP(C1,$A$1:$B$3,2)*C1
To calculate the price per unit in some other cell:
=VLOOKUP(C1,$A$1:$B$3,2)
2007-03-06 02:45:54 · answer #3 · answered by nospamcwt 5 · 0⤊ 0⤋
i am not too sure.
but i can suggest you something.. try checking out this site www.excelgoodies.com , probably you will find live help there on the site.
2007-03-09 03:58:46 · answer #4 · answered by sam e 2 · 0⤊ 0⤋