Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find out
how to find the intersection point between a column and a row. Basically, i
have exchange rates in a column and the rates to which i want to convert are in a row. I need to find where the column and row intersect...is there a way to do that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find out
I think VLOOKUP is the answer but you have not fully explained the problem
Try like this: In A1:20 I have ..... In the B columns I have.... In the C columns .... In G1 I have ..... and in H1 I want ..... Then we can be more helpful best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... how to find the intersection point between a column and a row. Basically, i have exchange rates in a column and the rates to which i want to convert are in a row. I need to find where the column and row intersect...is there a way to do that? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find out
Good point Bernard, i'll try to be more specific.
I have something that looks like this. AED USD BRL CAD EGP USD 1.2 1.5 2 1 .5 EUR 1.5 3 .75 100 35 MAD etc... VND LBP ZMK ZAR This is what i have to use, on another page, i have specific conversions i need to make and dont want to do this manually every month. So in column A i have what i need to go from to what i need to go to in column B. I want to just be able to look those up. Hopefully that explains it a little better. Let me know if you can help, Thanks, Alex "Bernard Liengme" wrote: I think VLOOKUP is the answer but you have not fully explained the problem Try like this: In A1:20 I have ..... In the B columns I have.... In the C columns .... In G1 I have ..... and in H1 I want ..... Then we can be more helpful best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... how to find the intersection point between a column and a row. Basically, i have exchange rates in a column and the rates to which i want to convert are in a row. I need to find where the column and row intersect...is there a way to do that? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find out
Let's say your table is in range A1:F8, column A is your "from" currency and
row 1 is your "to" currency. The following formula gives you the conversion from EUR to CAD: =VLOOKUP("EUR",$A$1:$F$8,MATCH("CAD",A1:F1,0),0) Just substitute "EUR" and "CAD" to the appropriate cell reference in your second sheet. "alex" wrote: Good point Bernard, i'll try to be more specific. I have something that looks like this. AED USD BRL CAD EGP USD 1.2 1.5 2 1 .5 EUR 1.5 3 .75 100 35 MAD etc... VND LBP ZMK ZAR This is what i have to use, on another page, i have specific conversions i need to make and dont want to do this manually every month. So in column A i have what i need to go from to what i need to go to in column B. I want to just be able to look those up. Hopefully that explains it a little better. Let me know if you can help, Thanks, Alex "Bernard Liengme" wrote: I think VLOOKUP is the answer but you have not fully explained the problem Try like this: In A1:20 I have ..... In the B columns I have.... In the C columns .... In G1 I have ..... and in H1 I want ..... Then we can be more helpful best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... how to find the intersection point between a column and a row. Basically, i have exchange rates in a column and the rates to which i want to convert are in a row. I need to find where the column and row intersect...is there a way to do that? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find out
I put your data on Sheet1.
In row 1 I have: (empty cell A1), AED, USD, BRL etc In column A I have (empty cell A1), USD, EUR ... The number 1.2 is in B2 On Sheet2 in A1, I have EUR In B1 I have BRL In C1 the formula =INDEX(Sheet1!B2:K10,MATCH(A1,Sheet1!A2:A10,0),MAT CH(B1,Sheet1!B1:K1,0)) returns the value 0.75 If the row 1 and column A were different (say there was no USD in row !) Then I could select the data and use Insert | Names. This would let me use the intersection operator as in =EUR BRL (the space between the names is the intersection operator) Suppose in row 1 you had names like AEDX, USDX... and in A you had USDY, EURY.... Then select all the data and use Insert | Names Now we can use =USDX EURY to get the value 3 Thank goodness you data is hypothetical - look at the $CND best wishes (happy to continue this with private email) -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... Good point Bernard, i'll try to be more specific. I have something that looks like this. AED USD BRL CAD EGP USD 1.2 1.5 2 1 .5 EUR 1.5 3 .75 100 35 MAD etc... VND LBP ZMK ZAR This is what i have to use, on another page, i have specific conversions i need to make and dont want to do this manually every month. So in column A i have what i need to go from to what i need to go to in column B. I want to just be able to look those up. Hopefully that explains it a little better. Let me know if you can help, Thanks, Alex "Bernard Liengme" wrote: I think VLOOKUP is the answer but you have not fully explained the problem Try like this: In A1:20 I have ..... In the B columns I have.... In the C columns .... In G1 I have ..... and in H1 I want ..... Then we can be more helpful best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... how to find the intersection point between a column and a row. Basically, i have exchange rates in a column and the rates to which i want to convert are in a row. I need to find where the column and row intersect...is there a way to do that? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find out
You want Vlookup, but you also need to be able to calculate the column
number. For example, if you wanted to convert USD, you would use something like: =vlookup(cell,table,3,false) because USD is in column 3. Now we just need a way to calculate the column number. Match will do that. So try: =vlookup(cell1,A:F,match(cell2,A1:F1,0),false) Adjust the ranges to suit. Regards, Fred. "alex" wrote in message ... Good point Bernard, i'll try to be more specific. I have something that looks like this. AED USD BRL CAD EGP USD 1.2 1.5 2 1 .5 EUR 1.5 3 .75 100 35 MAD etc... VND LBP ZMK ZAR This is what i have to use, on another page, i have specific conversions i need to make and dont want to do this manually every month. So in column A i have what i need to go from to what i need to go to in column B. I want to just be able to look those up. Hopefully that explains it a little better. Let me know if you can help, Thanks, Alex "Bernard Liengme" wrote: I think VLOOKUP is the answer but you have not fully explained the problem Try like this: In A1:20 I have ..... In the B columns I have.... In the C columns .... In G1 I have ..... and in H1 I want ..... Then we can be more helpful best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... how to find the intersection point between a column and a row. Basically, i have exchange rates in a column and the rates to which i want to convert are in a row. I need to find where the column and row intersect...is there a way to do that? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find out
Bernard,
I've been experimenting with your intersection operator. After naming the ranges, using a formula like =USDX EURY works fine. The problem arises when I don't want to type "USDY", etc into every cell, but use the "USDY" and "EURY" that are written into cells A1 and B1 instead. Just typing =A1 B1 doesn't work, since it does not recognise the names as ranges but as text, i guess... how do you solve this? "Bernard Liengme" wrote: I put your data on Sheet1. In row 1 I have: (empty cell A1), AED, USD, BRL etc In column A I have (empty cell A1), USD, EUR ... The number 1.2 is in B2 On Sheet2 in A1, I have EUR In B1 I have BRL In C1 the formula =INDEX(Sheet1!B2:K10,MATCH(A1,Sheet1!A2:A10,0),MAT CH(B1,Sheet1!B1:K1,0)) returns the value 0.75 If the row 1 and column A were different (say there was no USD in row !) Then I could select the data and use Insert | Names. This would let me use the intersection operator as in =EUR BRL (the space between the names is the intersection operator) Suppose in row 1 you had names like AEDX, USDX... and in A you had USDY, EURY.... Then select all the data and use Insert | Names Now we can use =USDX EURY to get the value 3 Thank goodness you data is hypothetical - look at the $CND best wishes (happy to continue this with private email) -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... Good point Bernard, i'll try to be more specific. I have something that looks like this. AED USD BRL CAD EGP USD 1.2 1.5 2 1 .5 EUR 1.5 3 .75 100 35 MAD etc... VND LBP ZMK ZAR This is what i have to use, on another page, i have specific conversions i need to make and dont want to do this manually every month. So in column A i have what i need to go from to what i need to go to in column B. I want to just be able to look those up. Hopefully that explains it a little better. Let me know if you can help, Thanks, Alex "Bernard Liengme" wrote: I think VLOOKUP is the answer but you have not fully explained the problem Try like this: In A1:20 I have ..... In the B columns I have.... In the C columns .... In G1 I have ..... and in H1 I want ..... Then we can be more helpful best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "alex" wrote in message ... how to find the intersection point between a column and a row. Basically, i have exchange rates in a column and the rates to which i want to convert are in a row. I need to find where the column and row intersect...is there a way to do that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
'find' somtimes can't find numbers. I folowd the 'help' instructi. | Excel Worksheet Functions | |||
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | Excel Discussion (Misc queries) |