***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[tony567]

Re: June/July 2008 Challenge of the Month

Posts in the Lounge do not count towards your total post count - so if QuinDavis has not posted in any other forum on the Board then he will have a post count of zero

QuinDavis.. i know what you did....

 

[crimson_b1ade]

Re: June/July 2008 Challenge of the Month

Challenge of the...Month?!? Last challenge was June/July 2008, and the previous challenge before that was March/April 2007. At that rate..."Challenge of the Year" would be a more fitting description.

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

I agree: change it to challenge of the year if it remains as is, or do us all a favor and change it back to challenge of the month!

 

[Arvind1977]

Re: June/July 2008 Challenge of the Month

hi the formula helped me alot but can any one explain me what is for 2^15 i can understand the command please help.

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

hi the formula helped me alot but can any one explain me what is for 2^15 i can understand the command please help.

Arvind

Search returns the starting position of the searched-for text within the cell. Maximum cell text length is 32,767 which is 2^15-1. Thus 2^15 represents a value which is at least 1 greater than the start position of any found text. Frequently you will see 9.9999E+307 used in its place (especially when not dealing with cell length) just because this is a bigger number than almost anything you will ever see in Excel. Lookup returns the last value which is less than or equal to the Lookup value - hence by choosing these numbers you are guaranteeing that the lookup value will be greater than the values returned by Search in the Lookup_range - so that if the searched-for term is found, Lookup will always returna result.

 

[narendra]

Re: June/July 2008 Challenge of the Month

How is it that Total No of Posts "Daniel Ferry" is shown as 0.
Is it that he has tweaked some settings or my PC has a Problem.

Or is it something else.

Check his Post# 66 & 67 (Pg 7) on this thread. Or his page in Members Profile Confirming the same.

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

Posts to the Lounge do not count towards total post count - hence if a member has not posted before or has only posted into the Lounge then his/her postcount will show as zero.

 

[Oorang]

Re: June/July 2008 Challenge of the Month

Has a winner been announced yet?

 

[Mr excel novice]

Re: June/July 2008 Challenge of the Month

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

Genius , but
-forgot one thing!
the challenge was for additional list items to be added without alteration to the function - keep your original with:

=LOOKUP(2^15,SEARCH(Keyword,A2),Assign)
where the defined names are:
Keyword =OFFSET(Sheet1!$D$1,1,0,(COUNTA(Sheet1!$D:$D)-1))
&
Assign =OFFSET(Sheet1!$E$1,1,0,(COUNTA(Sheet1!$E:$E))-1))

 

[Mr excel novice]

Re: June/July 2008 Challenge of the Month

Genius , but
-forgot one thing!
the challenge was for additional list items to be added without alteration to the function - keep your original with:

=LOOKUP(2^15,SEARCH(Keyword,A2),Assign)
where the defined names are:
Keyword =OFFSET(Sheet1!$D$1,1,0,(COUNTA(Sheet1!$D:$D)-1))
&
Assign =OFFSET(Sheet1!$E$1,1,0,(COUNTA(Sheet1!$E:$E))-1))

Sorry!
just noticed that this was covered before.
You could also change the 2^15 to COUNTA(Phrases), with dynamic range but this won't make the function any different.