Over the last few days, I have sought and received help from the community.
The help has been with formulas requiring array answers.
Each time I try the cntr-shift-enter, an error appears in the middle of the
formula. e.g Mike gave me this fomula
=INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
ctrl-shift-enter and an error came up and C1000,LARGE was highlighted. I then
went on to the array learning module, and entered an array formula for part
of that study, and again an error message appeared with a similar part of
their function highlighted as an error. Anyone suggest solution?

Not sure what you are doing but I wonder if the formula should be
=INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
the change being just after IF(
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"dartanion" <(E-Mail Removed)> wrote in message
news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
> Over the last few days, I have sought and received help from the
> community.
> The help has been with formulas requiring array answers.
> Each time I try the cntr-shift-enter, an error appears in the middle of
> the
> formula. e.g Mike gave me this fomula
> =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
> ctrl-shift-enter and an error came up and C1000,LARGE was highlighted. I
> then
> went on to the array learning module, and entered an array formula for
> part
> of that study, and again an error message appeared with a similar part of
> their function highlighted as an error. Anyone suggest solution?

Thanks Bernard, but no the same error appeared, which suggests to me its not
the formula, but something in excel, what do you think, and any ideas what I
can test?

"Bernard Liengme" wrote:

> Not sure what you are doing but I wonder if the formula should be
> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
> the change being just after IF(
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "dartanion" <(E-Mail Removed)> wrote in message
> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
> > Over the last few days, I have sought and received help from the
> > community.
> > The help has been with formulas requiring array answers.
> > Each time I try the cntr-shift-enter, an error appears in the middle of
> > the
> > formula. e.g Mike gave me this fomula
> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
> > ctrl-shift-enter and an error came up and C1000,LARGE was highlighted. I
> > then
> > went on to the array learning module, and entered an array formula for
> > part
> > of that study, and again an error message appeared with a similar part of
> > their function highlighted as an error. Anyone suggest solution?
>
>
>

>> > ctrl-shift-enter and an error came up and C1000,LARGE was highlighted.

Do you use semi-colon separator or comma? If so change the , to ;

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"dartanion" <(E-Mail Removed)> wrote in message
news:AA7ADE3A-357B-4808-900B-(E-Mail Removed)...
> Thanks Bernard, but no the same error appeared, which suggests to me its
> not
> the formula, but something in excel, what do you think, and any ideas what
> I
> can test?
>
> "Bernard Liengme" wrote:
>
>> Not sure what you are doing but I wonder if the formula should be
>> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
>> the change being just after IF(
>> best wishes
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "dartanion" <(E-Mail Removed)> wrote in message
>> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
>> > Over the last few days, I have sought and received help from the
>> > community.
>> > The help has been with formulas requiring array answers.
>> > Each time I try the cntr-shift-enter, an error appears in the middle of
>> > the
>> > formula. e.g Mike gave me this fomula
>> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
>> > ctrl-shift-enter and an error came up and C1000,LARGE was highlighted.
>> > I
>> > then
>> > went on to the array learning module, and entered an array formula for
>> > part
>> > of that study, and again an error message appeared with a similar part
>> > of
>> > their function highlighted as an error. Anyone suggest solution?
>>
>>
>>
>

See my post in your other thread, I should have updated it to continental
separators

=INDEX(C1:C1000;LARGE(IF(C1:C1000<>"";ROW(C1:C1000));2))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"dartanion" <(E-Mail Removed)> wrote in message
news:AA7ADE3A-357B-4808-900B-(E-Mail Removed)...
> Thanks Bernard, but no the same error appeared, which suggests to me its
> not
> the formula, but something in excel, what do you think, and any ideas what
> I
> can test?
>
> "Bernard Liengme" wrote:
>
>> Not sure what you are doing but I wonder if the formula should be
>> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
>> the change being just after IF(
>> best wishes
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "dartanion" <(E-Mail Removed)> wrote in message
>> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
>> > Over the last few days, I have sought and received help from the
>> > community.
>> > The help has been with formulas requiring array answers.
>> > Each time I try the cntr-shift-enter, an error appears in the middle of
>> > the
>> > formula. e.g Mike gave me this fomula
>> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
>> > ctrl-shift-enter and an error came up and C1000,LARGE was highlighted.
>> > I
>> > then
>> > went on to the array learning module, and entered an array formula for
>> > part
>> > of that study, and again an error message appeared with a similar part
>> > of
>> > their function highlighted as an error. Anyone suggest solution?
>>
>>
>>

Thanks Sandy, I changed all the , to ; and the array works. WHY??

"Sandy Mann" wrote:

> >> > ctrl-shift-enter and an error came up and C1000,LARGE was highlighted.
>
> Do you use semi-colon separator or comma? If so change the , to ;
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "dartanion" <(E-Mail Removed)> wrote in message
> news:AA7ADE3A-357B-4808-900B-(E-Mail Removed)...
> > Thanks Bernard, but no the same error appeared, which suggests to me its
> > not
> > the formula, but something in excel, what do you think, and any ideas what
> > I
> > can test?
> >
> > "Bernard Liengme" wrote:
> >
> >> Not sure what you are doing but I wonder if the formula should be
> >> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
> >> the change being just after IF(
> >> best wishes
> >> --
> >> Bernard V Liengme
> >> Microsoft Excel MVP
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> "dartanion" <(E-Mail Removed)> wrote in message
> >> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
> >> > Over the last few days, I have sought and received help from the
> >> > community.
> >> > The help has been with formulas requiring array answers.
> >> > Each time I try the cntr-shift-enter, an error appears in the middle of
> >> > the
> >> > formula. e.g Mike gave me this fomula
> >> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
> >> > ctrl-shift-enter and an error came up and C1000,LARGE was highlighted.
> >> > I
> >> > then
> >> > went on to the array learning module, and entered an array formula for
> >> > part
> >> > of that study, and again an error message appeared with a similar part
> >> > of
> >> > their function highlighted as an error. Anyone suggest solution?
> >>
> >>
> >>
> >
>
>
>

Because you have a continental version of Excel which uses comma as the
decimal separator, so semi-colon is used as the list separator. Our English
version is dot and comma.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"dartanion" <(E-Mail Removed)> wrote in message
news:414E77B6-4D16-4F3C-B9B0-(E-Mail Removed)...
> Thanks Sandy, I changed all the , to ; and the array works. WHY??
>
> "Sandy Mann" wrote:
>
>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>> >> > highlighted.
>>
>> Do you use semi-colon separator or comma? If so change the , to ;
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> (E-Mail Removed)
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "dartanion" <(E-Mail Removed)> wrote in message
>> news:AA7ADE3A-357B-4808-900B-(E-Mail Removed)...
>> > Thanks Bernard, but no the same error appeared, which suggests to me
>> > its
>> > not
>> > the formula, but something in excel, what do you think, and any ideas
>> > what
>> > I
>> > can test?
>> >
>> > "Bernard Liengme" wrote:
>> >
>> >> Not sure what you are doing but I wonder if the formula should be
>> >> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
>> >> the change being just after IF(
>> >> best wishes
>> >> --
>> >> Bernard V Liengme
>> >> Microsoft Excel MVP
>> >> www.stfx.ca/people/bliengme
>> >> remove caps from email
>> >>
>> >> "dartanion" <(E-Mail Removed)> wrote in message
>> >> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
>> >> > Over the last few days, I have sought and received help from the
>> >> > community.
>> >> > The help has been with formulas requiring array answers.
>> >> > Each time I try the cntr-shift-enter, an error appears in the middle
>> >> > of
>> >> > the
>> >> > formula. e.g Mike gave me this fomula
>> >> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>> >> > highlighted.
>> >> > I
>> >> > then
>> >> > went on to the array learning module, and entered an array formula
>> >> > for
>> >> > part
>> >> > of that study, and again an error message appeared with a similar
>> >> > part
>> >> > of
>> >> > their function highlighted as an error. Anyone suggest solution?
>> >>
>> >>
>> >>
>> >
>>
>>
>>

I thought that you had adequately explained the reason in the other thread
Bob so I did not respond. However, because it was in *another thread* I
suppose that for completeness in the archives it did require an answer.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%23$(E-Mail Removed)...
> Because you have a continental version of Excel which uses comma as the
> decimal separator, so semi-colon is used as the list separator. Our
> English version is dot and comma.
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
>
>
> "dartanion" <(E-Mail Removed)> wrote in message
> news:414E77B6-4D16-4F3C-B9B0-(E-Mail Removed)...
>> Thanks Sandy, I changed all the , to ; and the array works. WHY??
>>
>> "Sandy Mann" wrote:
>>
>>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>>> >> > highlighted.
>>>
>>> Do you use semi-colon separator or comma? If so change the , to ;
>>>
>>> --
>>> HTH
>>>
>>> Sandy
>>> In Perth, the ancient capital of Scotland
>>> and the crowning place of kings
>>>
>>> (E-Mail Removed)
>>> Replace @mailinator.com with @tiscali.co.uk
>>>
>>>
>>> "dartanion" <(E-Mail Removed)> wrote in message
>>> news:AA7ADE3A-357B-4808-900B-(E-Mail Removed)...
>>> > Thanks Bernard, but no the same error appeared, which suggests to me
>>> > its
>>> > not
>>> > the formula, but something in excel, what do you think, and any ideas
>>> > what
>>> > I
>>> > can test?
>>> >
>>> > "Bernard Liengme" wrote:
>>> >
>>> >> Not sure what you are doing but I wonder if the formula should be
>>> >> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
>>> >> the change being just after IF(
>>> >> best wishes
>>> >> --
>>> >> Bernard V Liengme
>>> >> Microsoft Excel MVP
>>> >> www.stfx.ca/people/bliengme
>>> >> remove caps from email
>>> >>
>>> >> "dartanion" <(E-Mail Removed)> wrote in message
>>> >> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
>>> >> > Over the last few days, I have sought and received help from the
>>> >> > community.
>>> >> > The help has been with formulas requiring array answers.
>>> >> > Each time I try the cntr-shift-enter, an error appears in the
>>> >> > middle of
>>> >> > the
>>> >> > formula. e.g Mike gave me this fomula
>>> >> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
>>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>>> >> > highlighted.
>>> >> > I
>>> >> > then
>>> >> > went on to the array learning module, and entered an array formula
>>> >> > for
>>> >> > part
>>> >> > of that study, and again an error message appeared with a similar
>>> >> > part
>>> >> > of
>>> >> > their function highlighted as an error. Anyone suggest solution?
>>> >>
>>> >>
>>> >>
>>> >
>>>
>>>
>>>
>
>
>

Hi sandy,

I thought you were watching Inverness Cali <bg>.

I wondered if he actually read that thread anymore after he got his answer
here.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sandy Mann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I thought that you had adequately explained the reason in the other thread
> Bob so I did not respond. However, because it was in *another thread* I
> suppose that for completeness in the archives it did require an answer.
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:%23$(E-Mail Removed)...
>> Because you have a continental version of Excel which uses comma as the
>> decimal separator, so semi-colon is used as the list separator. Our
>> English version is dot and comma.
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>>
>>
>> "dartanion" <(E-Mail Removed)> wrote in message
>> news:414E77B6-4D16-4F3C-B9B0-(E-Mail Removed)...
>>> Thanks Sandy, I changed all the , to ; and the array works. WHY??
>>>
>>> "Sandy Mann" wrote:
>>>
>>>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>>>> >> > highlighted.
>>>>
>>>> Do you use semi-colon separator or comma? If so change the , to ;
>>>>
>>>> --
>>>> HTH
>>>>
>>>> Sandy
>>>> In Perth, the ancient capital of Scotland
>>>> and the crowning place of kings
>>>>
>>>> (E-Mail Removed)
>>>> Replace @mailinator.com with @tiscali.co.uk
>>>>
>>>>
>>>> "dartanion" <(E-Mail Removed)> wrote in message
>>>> news:AA7ADE3A-357B-4808-900B-(E-Mail Removed)...
>>>> > Thanks Bernard, but no the same error appeared, which suggests to me
>>>> > its
>>>> > not
>>>> > the formula, but something in excel, what do you think, and any ideas
>>>> > what
>>>> > I
>>>> > can test?
>>>> >
>>>> > "Bernard Liengme" wrote:
>>>> >
>>>> >> Not sure what you are doing but I wonder if the formula should be
>>>> >> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
>>>> >> the change being just after IF(
>>>> >> best wishes
>>>> >> --
>>>> >> Bernard V Liengme
>>>> >> Microsoft Excel MVP
>>>> >> www.stfx.ca/people/bliengme
>>>> >> remove caps from email
>>>> >>
>>>> >> "dartanion" <(E-Mail Removed)> wrote in message
>>>> >> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
>>>> >> > Over the last few days, I have sought and received help from the
>>>> >> > community.
>>>> >> > The help has been with formulas requiring array answers.
>>>> >> > Each time I try the cntr-shift-enter, an error appears in the
>>>> >> > middle of
>>>> >> > the
>>>> >> > formula. e.g Mike gave me this fomula
>>>> >> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
>>>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>>>> >> > highlighted.
>>>> >> > I
>>>> >> > then
>>>> >> > went on to the array learning module, and entered an array formula
>>>> >> > for
>>>> >> > part
>>>> >> > of that study, and again an error message appeared with a similar
>>>> >> > part
>>>> >> > of
>>>> >> > their function highlighted as an error. Anyone suggest solution?
>>>> >>
>>>> >>
>>>> >>
>>>> >
>>>>
>>>>
>>>>
>>
>>
>>
>
>
>

It was only lately that I found out that *Celtic* is actually an acronym.
Yes it's true, it stands for:

Can Even Lose To Inverness Cali <G>

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi sandy,
>
> I thought you were watching Inverness Cali <bg>.
>
> I wondered if he actually read that thread anymore after he got his answer
> here.
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
>
>
> "Sandy Mann" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I thought that you had adequately explained the reason in the other thread
>> Bob so I did not respond. However, because it was in *another thread* I
>> suppose that for completeness in the archives it did require an answer.
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> (E-Mail Removed)
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:%23$(E-Mail Removed)...
>>> Because you have a continental version of Excel which uses comma as the
>>> decimal separator, so semi-colon is used as the list separator. Our
>>> English version is dot and comma.
>>>
>>> --
>>> ---
>>> HTH
>>>
>>> Bob
>>>
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>> addy)
>>>
>>>
>>>
>>> "dartanion" <(E-Mail Removed)> wrote in message
>>> news:414E77B6-4D16-4F3C-B9B0-(E-Mail Removed)...
>>>> Thanks Sandy, I changed all the , to ; and the array works. WHY??
>>>>
>>>> "Sandy Mann" wrote:
>>>>
>>>>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>>>>> >> > highlighted.
>>>>>
>>>>> Do you use semi-colon separator or comma? If so change the , to ;
>>>>>
>>>>> --
>>>>> HTH
>>>>>
>>>>> Sandy
>>>>> In Perth, the ancient capital of Scotland
>>>>> and the crowning place of kings
>>>>>
>>>>> (E-Mail Removed)
>>>>> Replace @mailinator.com with @tiscali.co.uk
>>>>>
>>>>>
>>>>> "dartanion" <(E-Mail Removed)> wrote in message
>>>>> news:AA7ADE3A-357B-4808-900B-(E-Mail Removed)...
>>>>> > Thanks Bernard, but no the same error appeared, which suggests to me
>>>>> > its
>>>>> > not
>>>>> > the formula, but something in excel, what do you think, and any
>>>>> > ideas
>>>>> > what
>>>>> > I
>>>>> > can test?
>>>>> >
>>>>> > "Bernard Liengme" wrote:
>>>>> >
>>>>> >> Not sure what you are doing but I wonder if the formula should be
>>>>> >> =INDEX(C1:C1000,LARGE(IF(C1:C1000<>"",ROW(C1:C1000)),2))
>>>>> >> the change being just after IF(
>>>>> >> best wishes
>>>>> >> --
>>>>> >> Bernard V Liengme
>>>>> >> Microsoft Excel MVP
>>>>> >> www.stfx.ca/people/bliengme
>>>>> >> remove caps from email
>>>>> >>
>>>>> >> "dartanion" <(E-Mail Removed)> wrote in message
>>>>> >> news:184139A4-5DDD-4895-AE41-(E-Mail Removed)...
>>>>> >> > Over the last few days, I have sought and received help from the
>>>>> >> > community.
>>>>> >> > The help has been with formulas requiring array answers.
>>>>> >> > Each time I try the cntr-shift-enter, an error appears in the
>>>>> >> > middle of
>>>>> >> > the
>>>>> >> > formula. e.g Mike gave me this fomula
>>>>> >> > =INDEX(C1:C1000,LARGE(IF(C1000<>"",ROW(C1:C1000)),2)) I used the
>>>>> >> > ctrl-shift-enter and an error came up and C1000,LARGE was
>>>>> >> > highlighted.
>>>>> >> > I
>>>>> >> > then
>>>>> >> > went on to the array learning module, and entered an array
>>>>> >> > formula
>>>>> >> > for
>>>>> >> > part
>>>>> >> > of that study, and again an error message appeared with a similar
>>>>> >> > part
>>>>> >> > of
>>>>> >> > their function highlighted as an error. Anyone suggest solution?
>>>>> >>
>>>>> >>
>>>>> >>
>>>>> >
>>>>>
>>>>>
>>>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>