If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating a value from a variable result
To start with I'll explain how my sheet is set up:
Cells W40:W45 contain values of 40 through to 45 These are levels gained by an individual. Cells X40:X45 contain the grade needed to access the next level. Looking something like this:- W X 40 40 41,171 41 41 45,529 42 42 50,399 43 43 55,649 44 44 61,512 45 45 67,983 D27 contains the current level (40 - 45) E27 contains the current grade (in this case 42,224) Here's my query. I want a calculation in E85 that finds D27 from within (W40:W45), matches the corresponding value from (X40:X45), then subtracts E27 from that corresponding value. For example, D27 is 41, E27 is 42,224. The corrresponding X value is 45,529. Resulting in 1,305 being shown in E85. I have tried combining Vlookup's index's and matches but always get a #ref result. Sorry it's so spread out but this is just a few lines taken from the whole sheet. If you could help with this 1 cell I can then expand it to the rest. Any help would be greatly appreciated. Thanks in advance, Darren. |
#2
|
|||
|
|||
Creating a value from a variable result
Ok, it was staring me in the face:
=LOOKUP(D27,W:W,X:X)-E27. "Darren" wrote: To start with I'll explain how my sheet is set up: Cells W40:W45 contain values of 40 through to 45 These are levels gained by an individual. Cells X40:X45 contain the grade needed to access the next level. Looking something like this:- W X 40 40 41,171 41 41 45,529 42 42 50,399 43 43 55,649 44 44 61,512 45 45 67,983 D27 contains the current level (40 - 45) E27 contains the current grade (in this case 42,224) Here's my query. I want a calculation in E85 that finds D27 from within (W40:W45), matches the corresponding value from (X40:X45), then subtracts E27 from that corresponding value. For example, D27 is 41, E27 is 42,224. The corrresponding X value is 45,529. Resulting in 1,305 being shown in E85. I have tried combining Vlookup's index's and matches but always get a #ref result. Sorry it's so spread out but this is just a few lines taken from the whole sheet. If you could help with this 1 cell I can then expand it to the rest. Any help would be greatly appreciated. Thanks in advance, Darren. |
Thread Tools | |
Display Modes | |
|
|