Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 22nd 17, 11:57 AM posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 279
Default Intersect operation

I hope readers here can help.

I am having trouble developing an array formula in a worksheet.
Once I have the appropriate symbols, I would like to run the
calculation without writing to a worksheet. I suspect that is
impossible.

I have 2 rows in a worksheet containing
Alscot Road stop U 1 78 N1
Spa Road stop V 1 78 N1

In column 1 of the third row, I have the array formula
=R[-2]C[1]:R[-2]C[3] R[-1]C[1]:R[-1]C[3]
I expect the formula to produce
1 78 N1
because the values in the two ranges R[-2]C[1]:R[-2]C[3] and
R[-1]C[1]:R[-1]C[3] are the same
(My data represents London Bus services at adjacent stops.)
However, it produces
#NULL!

When I have an appropriate formula, I will transform the result to 1,
78, N1

Once I have a formula which works for adjacent stops, I will apply it
to distant stops which share one or more routes.

Thanks!
--
Walter Briscoe

  #2   Report Post  
Old March 22nd 17, 12:25 PM posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 279
Default Intersect operation

In message of Wed, 22 Mar 2017
10:57:39 in microsoft.public.excel.programming, Walter Briscoe
writes
I hope readers here can help.

I am having trouble developing an array formula in a worksheet.
Once I have the appropriate symbols, I would like to run the
calculation without writing to a worksheet. I suspect that is
impossible.

I have 2 rows in a worksheet containing
Alscot Road stop U 1 78 N1
Spa Road stop V 1 78 N1

In column 1 of the third row, I have the array formula
=R[-2]C[1]:R[-2]C[3] R[-1]C[1]:R[-1]C[3]
I expect the formula to produce
1 78 N1
because the values in the two ranges R[-2]C[1]:R[-2]C[3] and
R[-1]C[1]:R[-1]C[3] are the same
(My data represents London Bus services at adjacent stops.)
However, it produces
#NULL!

When I have an appropriate formula, I will transform the result to 1,
78, N1

Once I have a formula which works for adjacent stops, I will apply it
to distant stops which share one or more routes.

Thanks!


My apologies. The space operator does not do as I thought.
--
Walter Briscoe


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
intersect ranswert Excel Programming 12 February 6th 08 02:15 PM
Change cut/paste operation to cut/insert operation Don Guillett Excel Programming 0 January 17th 07 04:23 PM
Intersect Arne Hegefors Excel Programming 1 July 25th 06 09:38 AM
Intersect [email protected] Excel Programming 2 July 19th 06 10:41 PM


All times are GMT +1. The time now is 06:59 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017