contiguous reference

hi folks.

when I am using histogram in data analysis tools, I have my input range and bin range in 2 sheets, but there always popup "Input range must be a contiguous refrence". This happens since last week. but it never happened before.

I don't know what setting was changed, and I didn't set "R1C1 reference style" .
in option.


Please help! many thanks!
jeff

"Contiguous" means touching or beside.

It would seem your input range consists of two or more discrete ranges.

Change that configuation to prevent the error message.


Gord Dibben MS Excel MVP

On Thu, 10 Aug 2006 02:19:07 -0400, jjeff
<[email protected]> wrote:

>
>hi folks.
>
>when I am using histogram in data analysis tools, I have my input
>range and bin range in 2 sheets, but there always popup "Input range
>must be a contiguous refrence". This happens since last week. but
>it never happened before.
>
>I don't know what setting was changed, and I didn't set "R1C1 reference
>style" .
>in option.
>
>
>Please help! many thanks!
>jeff

Thanks Gord!

but my input range consists of only one range.

I selected my input range, say, sheet1!$B$2:$B$B51, and the bin range and output range, and click OK, then the contiguous warning window popup. The input range always displayed as something like [myexcel]sheet1!R1C2:R50C2.

thanks!

Thanks Gord!

but my input range consists of only one range.

I selected my input range, say, sheet1!$B$2:$B$B51, and the bin range and output range, and click OK, then the contiguous warning window popup. The input range always displayed as something like [myexcel]sheet1!R1C2:R50C2.

thanks!
jeff

Cannot replicate your problem with a contiguous range.

I have tried with filters on, rows hidden and blank rows hidden but cannot get
the error message if range is contiguous.

The [myexcel]sheet1!R1C2:R50C2 looks like you are referencing sheet1 in myexcel
workbook.

The R1C1 notation can be turned off at Tools>Options>General if you wish.


Gord


On Thu, 10 Aug 2006 15:35:00 -0400, jjeff
<[email protected]> wrote:

>
>Thanks Gord!
>
>but my input range consists of only one range.
>
>I selected my input range, say, sheet1!$B$2:$B$B51, and the bin range
>and output range, and click OK, then the contiguous warning window
>popup. The input range always displayed as something like
>[myexcel]sheet1!R1C2:R50C2.
>
>thanks!

This error seems to be consistent across all of the data analysis tools (at least I see it in f-test, t-test, and z-test). It occurs when the input data ranges are not on the same sheet. I have a fairly large worksheet consisting of surgical data for 1,000 patients. The patients were either treated by a physician (MD) or a physician assistant (PA). Each patient has 226 columns of data. To facilitate analysis i copied the rows of those patients treated by an MD to a Sheet named "MD" and those treated by a PA to a Sheet named "PA". In a third Sheet I set about the task of looking at risk factors between the two samples. Specifying the data ranges as MD!C:C and PA!C:C causes the "Input range must be a contiguous reference" error. Additionally, the input ranges are changed to "[Title 22 Study Data.xls]MD!C3" and "[Title 22 Study Data.xls]PA!C3", respectively. If I copy the two columns of data to a separate Sheet and reference them as "Sheet3!C:C" and "Sheet3!D:D", all works well.

I literally have hundreds of these calculations to perform and requiring me to rearrange the data like this is unreasonable. From my perspective, this is a bug in the Excel Data Analysis package. At a minimum, it is an unreasonable constraint. Do MS staff monitor this forum? If not, where is the best place to report this? -ranz

OK, now I'm feeling really stumped. I decided to attempt to recreate this using a very simple worksheet with three Sheets, one column of data in the first and second sheet. These columns have the numbers 0 through 9 in the rows of column A. In the third Sheet I attempted to perform a f-test and t-test on the data in these other two sheets using ranges of Sheet1!A:A and Sheet2!A:A, respectively. This works fine without reproducing the error. I will keep working on a way to simply reproduce this and report my findings here. -ranz

darkyam,

i apologize if you think i have violated the rules. i read the rules, (esp # 2), and believe i am in compliance. i have been using forum since its early days at mit back in 1979 and am well aware of the etiquette. i chose to respond to this thread because i had the exact same issue as described by JJeff in his first post. since the issue remains unanswered, and indeed Gord stated he was unable to reproduce the error, i thought my contribution was relevant to the discussion. as i stated in my post, this is not just a problem in histograms (as reported by JJeff) but seems to be consistent along the entire suite of Excel data analysis tools. i believed that my contributions to this thread would further highlight the issue and provide additional fodder for anaysis. as i further stated in my post, i am working with a large workbook with tens of thousands of cells and about 12 worksheets. i have been very careful to make sure my address ranges are indeed contiguous and am now attempting to reproduce the error in a simple worksheet. the only "questions" i asked were if this forum is attended my Microsoft engineers who could comment or help resolve this issue and failing that, where would be the best place to discuss it with the MS staff. if this is not the type of analysis and contribution you encourage from your membership then perhaps i should seek out another venue. there are at least three others where this issue has been reported. i stopped by thos fora but decided to settle here as discussions on other issues seemed well thought out.

let me know if you would like me to contribute here or move on. -ranz