Nested formulas

[John]

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*"),INDEX(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

 

[Bob Phillips]

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

 

[John]

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

 

[Finance Guru]

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

 

[Harlan Grove]

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.

 

[T. Valko]

That formula looks familiar.

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

 

[John]

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]

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

 

[John]

HI Biff
I would appreciate it very much.
Thank you
Best regards
John