Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-25-2014, 09:14 AM
Will Will is offline Explanation for this formula Windows XP Explanation for this formula Office 2007
Novice
Explanation for this formula
 
Join Date: Oct 2011
Posts: 5
Will is on a distinguished road
Default Explanation for this formula

Hi,



I was searching on-line for some help on the Find formula, and came across this. Would someone be able to explain it?

=LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A1),$E$1:$E$10) ))

There were a list of values in cells E1:E10.
There were a list of inputs in A1 downwards.
What was wanted was a formula which gave, for each input in A1 downwards, one of the values in E1:E10 if an input in A1 downwards contained one of those values, and if not then it returned "Another Value".

It works but I can't work out how or why!

For example:-
(1) the second LOOKUP includes FIND($E$1:$E$10,A1) but on its own this formula just returns #VALUE.
(2) what do the {} mean within the CHOOSE formula? I assume these are not to do with an array?

Many thanks,

Will
Reply With Quote
  #2  
Old 11-25-2014, 09:21 PM
whatsup whatsup is offline Explanation for this formula Windows 7 64bit Explanation for this formula Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Hi Will

How much do you know about excel? You will need at least to know excel's feature for evaluating formulas, and how to create array formulas:
Quote:
(1) the second LOOKUP includes FIND($E$1:$E$10,A1) but on its own this formula just returns #VALUE.
FIND() on its own can't work with arrays, it's just able to look within a single cell for a character. The common use is:
=FIND("Hello","e",1)
It will return 2

Fill Range E1:E10 with characters a to j, now look at the evaluation if you select the cell containing:
=FIND(E1:E10,"e")
You will see =FIND("a","e") the result will be #VALUE but that's not of interest, the point is, the evaluation shows, that just the value of E1 is evaluated. No matter whether you entered the formula normally or as array formula!

To get FIND() working with arrays you will need a function, which got the ability to work with arrays. That's for example LOOKUP(), or MIN():
Enter the formula =MIN(FIND(E1:E10,"e")) and look at the evaluation, you will see something similiar as before, again only E1 is regarded.
Now enter the above formula as arrayformula and the evaluation will show you the values of each cell E1:E10.

LOOKUP() and MIN(), besides others, always will return a single value, but not an array as result!

Quote:
(2) what do the {} mean within the CHOOSE formula? I assume these are not to do with an array?
CHOOSE() is a special function. Besides the function N() and T() it got the ability to return an array as result. The {} are used in order that CHOOSE returns an array instead of a single value as result.

The whole formula as you posted, is quite sophisticated. It won't work with numeric values, unless you change REPT("z",255) to - for example - 9^9

Cheers
Reply With Quote
  #3  
Old 11-26-2014, 06:24 AM
Bruno Campanini Bruno Campanini is offline Explanation for this formula Windows 8 Explanation for this formula Office 2013
Novice
 
Join Date: Nov 2014
Posts: 19
Bruno Campanini is on a distinguished road
Default

Quote:
Originally Posted by whatsup View Post
Hi Will

How much do you know about excel? You will need at least to know excel's feature for evaluating formulas, and how to create array formulas:
The whole formula as you posted, is quite sophisticated. It won't work with numeric values, unless you change REPT("z",255) to - for example - 9^9
******

Try this A1:A10
a1a11a1df12b1qwe12bccasdasd102aasd1ws


with this E1:E10
wedfqwfghhtr2a1a1asdrrr2ddd


You get
a1a1a1ANOTHER VALUEqwANOTHER VALUEasdasdANOTHER VALUEasd

That is only 2 results correct!
Is it a sophisticated formula?
Bad formula and bad analysis!

Bruno

Last edited by macropod; 11-26-2014 at 05:25 PM. Reason: Repaired messed up QUOTE tagging
Reply With Quote
  #4  
Old 11-26-2014, 06:28 AM
Bruno Campanini Bruno Campanini is offline Explanation for this formula Windows 8 Explanation for this formula Office 2013
Novice
 
Join Date: Nov 2014
Posts: 19
Bruno Campanini is on a distinguished road
Default

Quote:
Originally Posted by Will View Post
Hi,

I was searching on-line for some help on the Find formula, and came across this. Would someone be able to explain it?

=LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A1),$E$1:$E$10) ))
This is a bad formula (see my previous post).

Try this one, it's simple and more reliable:

=(IF(ISERROR(MATCH(A1,$E$1:$E$10,0)),"Another Value",A1))

Bruno

Last edited by macropod; 11-26-2014 at 05:25 PM. Reason: Repaired messed up QUOTE tagging
Reply With Quote
  #5  
Old 11-26-2014, 09:04 AM
Will Will is offline Explanation for this formula Windows XP Explanation for this formula Office 2007
Novice
Explanation for this formula
 
Join Date: Oct 2011
Posts: 5
Will is on a distinguished road
Default

Many thanks for your help - much appreciated. Just in the process of working through it all...
Reply With Quote
  #6  
Old 11-26-2014, 10:16 AM
whatsup whatsup is offline Explanation for this formula Windows 7 64bit Explanation for this formula Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Oh Bruno,

Quote:
This is a bad formula (see my previous post).
I didn't expect anything else from you

But your formula is great !!! It doesn't look at parts of a value, as the other formula does, but that's just a detail we don't really care for, isn't it?
Reply With Quote
  #7  
Old 11-26-2014, 05:12 PM
Bruno Campanini Bruno Campanini is offline Explanation for this formula Windows 8 Explanation for this formula Office 2013
Novice
 
Join Date: Nov 2014
Posts: 19
Bruno Campanini is on a distinguished road
Default

Quote:
Originally Posted by whatsup View Post
Oh Bruno,



I didn't expect anything else from you

But your formula is great !!! It doesn't look at parts of a value, as the other formula does, but that's just a detail we don't really care for, isn't it?
Your right, I supposed it was a non-sense to look at parts of a value,
but if you like:

Case 1
====
In A1:A10 we have "abc"
in E1:E10 we have "abc"
=(IF(ISERROR(MATCH(A1,$E$1:$E$10,0)),"XXX",A1))
and you get "abc"

Case 2
====
In A1:A10 we have "abc"
in E1:E10 we have "ZZabcKKK"

{=IF(SUM((IF(ISERROR(FIND(A1,$E$1:$E$10)),0,1)))=1 ,A1,"XXX")}
if you want to get "abc"

{=IF(SUM(IF(ISERROR(FIND(A1,$E$1:$E$10)),0,FIND(A1 ,$E$1:$E$10))),INDEX($E$1:$E$10,MATCH(SUM(IF(ISERR OR(FIND(A1,$E$1:$E$10)),0,FIND(A1,$E$1:$E$10))),(F IND(A1,$E$1:$E$10)))),"XXX")}
if you want to get "ZZabcKKK"

Why don't you help me in simplifying the last one?
It looks so heavy!

Bruno
Reply With Quote
  #8  
Old 11-26-2014, 05:27 PM
macropod's Avatar
macropod macropod is offline Explanation for this formula Windows 7 64bit Explanation for this formula Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Bruno,

When Quoting parts of the posts replied to, please don't remove the [/QUOTE] tag from the end of the quoted portion. Doing so breaks the forum formatting and makes it harder for others to see where the quoted content ends and your response begins
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
where to find explanation of reference styles LMHmedchem Word 5 07-22-2014 09:24 PM
Formula muhu Excel 1 04-14-2014 08:19 AM
Explanation for this formula Need help with formula zachluke Excel 3 03-13-2014 11:15 AM
Explanation for this formula Explanation for fields on the form hklein Word VBA 5 10-23-2011 04:10 AM
Microsoft Office XP/2003/2007/2010 Graphic Filters "Allow List" Explanation Jazz43 Office 0 08-24-2011 10:47 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft