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
|
|||
|
|||
Looking for an excel function which can mirror opposite the data
Dear sir,
There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200903/1 |
#2
|
|||
|
|||
Looking for an excel function which can mirror opposite the data
Try this:
Entered in B1 and copied down to B1. =INDEX(A$1:A$10,ROWS(A1:A$10)) -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:93d5a2390f1a2@uwe... Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200903/1 |
#3
|
|||
|
|||
Looking for an excel function which can mirror opposite the data
If you have a static number of rows:
=INDIRECT("A"&11-(ROW())) Note that the 11 in the above is the number equal to the total number of filled rows plus 1. In other words, the above works when values in A1 to A10 need to be inverted. If the number of rows is dynamic (i.e. the total number of rows changes, a solution would be to set up a dynamic range in column A and use in conjunction with the formula below.. The following formula should enable you to invert the values in column A, irrespective whether blank or filled =INDIRECT("A" &COUNTA(myRange)+COUNTBLANK(myRange) +1 - ROW()) Note that any blanks in column A will show a zero value in column B. How to set up a dynamic named range: http://www.contextures.com/xlNames01.html#Dynamic -- Steve "wilchong via OfficeKB.com" u43231@uwe wrote in message news:93d5a2390f1a2@uwe... Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200903/1 |
Thread Tools | |
Display Modes | |
|
|