Nested formulas
 

Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is to
follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John

Nested formulas
 

It is just a nesting of simple formulae, so if you are happy with such,
break it down

<F1: SEARCH("xyz",A$2:A$6) looks for all cells in the range with the value
<F2: IF(ISNUMBER(<F1),ROW(A$2:A$6)) returns the row numbers of same
<F3: SMALL(<F2,ROW(A$2:A$6)),ROWS(C$2:C3)) gets the next smallest
depending on the formula row
<F4: INDEX(A$2:A$6,<F3-MIN(ROW(A$2:A$6))+1) gets the value in the range
A2:A6 for this match
<F5: IF(ROWS(C$2:C3)<=COUNTIF(A$2:A$6,"*xyz*"),<F4,"") just ensures that
it shows blank after all are done

--
__________________________________
HTH

Bob

"John" wrote in message
...
Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is to
follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John

Nested formulas
 

Hi Bob
Thank you for your reply and all the best for the NewYear.
I understand your answer but the sample that i attached was just taken
randomly in one of the post. Some formulas are easier then others and when i
read the answer to the question, I'm able to follow some of the replies, its
always easy when you get the answer.
My problem is comming up with a complex formulas, how to start the logic and
proper syntax. this is why I would like a book on Nested Formulas or
anything that would help.
How did you learn how to do it ?
Regards
John
"Bob Phillips" wrote in message
...
It is just a nesting of simple formulae, so if you are happy with such,
break it down

<F1: SEARCH("xyz",A$2:A$6) looks for all cells in the range with the
value
<F2: IF(ISNUMBER(<F1),ROW(A$2:A$6)) returns the row numbers of same
<F3: SMALL(<F2,ROW(A$2:A$6)),ROWS(C$2:C3)) gets the next smallest
depending on the formula row
<F4: INDEX(A$2:A$6,<F3-MIN(ROW(A$2:A$6))+1) gets the value in the range
A2:A6 for this match
<F5: IF(ROWS(C$2:C3)<=COUNTIF(A$2:A$6,"*xyz*"),<F4,"") just ensures that
it shows blank after all are done

--
__________________________________
HTH

Bob

"John" wrote in message
...
Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is to
follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John

Nested formulas
 

John,

Treat yourself to book called 'Microsoft Excel Functions & Formulas' by
Bernd Held
It is a marvelous little tome of functions.

A Happy New Year to You.
Regards
FinanceGuru

--
Wales - the land of fire breathing dragons and rugby playing wizards.


"John" wrote:

Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is to
follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John

Nested formulas
 

"John" wrote...
....
My problem is comming up with a complex formulas, how to start the logic and
proper syntax. this is why I would like a book on Nested Formulas or
anything that would help.

Do the reverse of what Bob did. If you currently use several formulas
to produce one final result, try to combine the formulas in each of
the cells into a single formula in a single cell.

Also, if you don't need to do this, why worry about it?

How did you learn how to do it ?

I figure most people who use very long formulas learned by doing. For
programming generally, nothing beats experience. It really is nothing
more than combining several calculations which could be performed
using multiple formulas in multiple cells into a single formula in a
single cell. It's not necessary to do this, but it sometimes works
better to do so.

Putting it another way, there are no books or web sites that deal
specifically with creating long spreadsheet formulas. Newsgroups like
this and learning by doing are really your only alternatives.

Nested formulas
 

That formula looks familiar.

Later on tonight if I'm not too busy I'll write up an explanation.

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is to
follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John

Nested formulas
 

Thank you All for your comments.
It's appreciated, I will keep reading these post and practice.
All the best for the NewYear to you All
Regards
John
"T. Valko" wrote in message
...
That formula looks familiar.

Later on tonight if I'm not too busy I'll write up an explanation.

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is to
follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John

Nested formulas
 

Would you like me to explain the formula function by function and the logic
of how all those functions relate to each other?


--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Thank you All for your comments.
It's appreciated, I will keep reading these post and practice.
All the best for the NewYear to you All
Regards
John
"T. Valko" wrote in message
...
That formula looks familiar.

Later on tonight if I'm not too busy I'll write up an explanation.

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is
to follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John

Nested formulas
 

HI Biff
I would appreciate it very much.
Thank you
Best regards
John
"T. Valko" wrote in message
...
Would you like me to explain the formula function by function and the
logic of how all those functions relate to each other?


--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Thank you All for your comments.
It's appreciated, I will keep reading these post and practice.
All the best for the NewYear to you All
Regards
John
"T. Valko" wrote in message
...
That formula looks familiar.

Later on tonight if I'm not too busy I'll write up an explanation.

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
Hi Everyone

I've been reading these post for just over a year and I'm not bad with
simple formulas.
but when I see something like
this,=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),IN DEX(A$2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6 )),ROW(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"")
my head start spinning.
Is there a book that specialize in that type of formulas.My problem is
to follow the logic and proper syntax.
Thank you for any suggestion and Happy New Year to all.
Regards
John