Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 90
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 245
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Looking for an excel function which can mirror opposite thedata

YES! Was looking for the same thing. Biff's answer nailed it. Thank you!

On Sunday, March 29, 2009 10:33 PM wilchong via OfficeKB.com wrote:


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



On Sunday, March 29, 2009 11:39 PM T. Valko wrote:


Try this:

Entered in B1 and copied down to B1.

=INDEX(A$1:A$10,ROWS(A1:A$10))

--
Biff
Microsoft Excel MVP



On Tuesday, March 31, 2009 11:04 PM AltaEgo wrote:


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...




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there an opposite function to "Concatenate" in Excel? drbonne Excel Worksheet Functions 8 April 3rd 23 01:21 PM
How do you mirror data and formatting in Excel '03 Jorg Excel Worksheet Functions 1 August 1st 07 03:36 PM
Opposite of SUM function is ?? amkazen Excel Discussion (Misc queries) 1 April 18th 05 07:13 PM
Need opposite of excel function CONCATENATE Adam Excel Discussion (Misc queries) 3 March 5th 05 09:59 PM
The opposite of IF function gives #VALUE for value = 0 gizmo Excel Worksheet Functions 2 December 30th 04 02:36 PM


All times are GMT +1. The time now is 07:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"