Taste of Formulas

[rkspeaks]

Chandoo & Hui and all Ninjas,


As things are getting more and more interesting, I came up with an plan for Chandoo to conduct a survey/voting event on the taste of Formulas on the following lines just to add up something to the spice.


1. Most interesting formula.

2. Most intimidating formula to others when they look at it first time.

3. Most boring formula

4. Easiest formula of all time

5. Most explosive formula (You fear a lot and stop pressing Enter for a while)

6. Most exciting formula (You excite very much before you press ENTER key)

7. Most difficult formula (even if you know it very well)

8. Most Error prone formula but you know it, you like it etc etc.


I know all the formulas are useful and behave in different ways, all are needed somewhere like our friends in life. But when asked people, they react in different ways. Answers will be different from person to person, there comes the fun. I think it will be more interesting and exciting to see the results. My Category list is just to name a few categories/conditions, Let Chandoo take the initiative to frame more categories.


Guys please be ready with your answers. Let Chandoo give a Green signal for this.


It's so irresistible that I could not carry my excitement any long, That's why I dumped my thoughts here without contacting Chandoo personally. We all know about his heart, anyways.


Chandoo, We need more fun. Thank you.


On behalf of all the members

RK

 

[keymaster]

Sounds like a fun idea RK... lets see what our users come up with. I will go first.


1. Most interesting formula.


Well, there are so many formulas I wrote that meet this criteria. One of them is to calculate Work break down structure recursively using INDEX etc. You can see a similar formula here: http://chandoo.org/forums/topic/drop-down-list-in-hierarchical-format#post-38923


2. Most intimidating formula to others when they look at it first time.


Could not pick one easily. But I am afraid whenever I look at a formula that is too long or too nested.


3. Most boring formula


Well, nothing is boring per se. But the usual sums, averages, ifs, vlookups


4. Easiest formula of all time


Same as above.


5. Most explosive formula (You fear a lot and stop pressing Enter for a while)


Almost any formula (especially sumproducts) that process more than a few thousand rows with several conditions in them. I save my workbook few times before running these and always worry if Excel is going to crash on me.


6. Most exciting formula (You excite very much before you press ENTER key)


7. Most difficult formula (even if you know it very well)

Oh so many. I struggle with statistical formulas, very complex lookups & summaries.


8. Most Error prone formula but you know it, you like it etc etc.

I used to fear the IF formula during my graduation days. Over the last 7 years, my formula errors have gone down very much. Now a days, if I see an error, I know what to do


Your turn,

 

[kuldeepjainesl]

From the ABC learning student.


1. Most interesting formula.


Countifs. This has saved me a lot of time.


2. Most intimidating formula to others when they look at it first time.


Nested if and having indirect/offset functions. i still don't understand how to use indirect or offset in practical use.


3. Most boring formula


Sums/Multiply/Divide/Subtract


4. Easiest formula of all time


=A1+B1


5. Most explosive formula (You fear a lot and stop pressing Enter for a while)


Any Array formulas including sum product. Excel many time excel start calculation and never end this and need to press CTRL+ALT+Delete. Had lost my work many times.


6. Most exciting formula (You excite very much before you press ENTER key)


Again Array formulas because after formulas start working i try understand "How it works?" via evaluate function


7. Most difficult formula (even if you know it very well)


Definitely statistical formulas as i am making a mess now a days for doing Weibull analysis. But here as well i will add array formulas along with sum product which lead me to this forum initially. Now i am a Fan on this forum.


8. Most Error prone formula but you know it, you like it etc etc.


My most of formula gives error on first time except the boring ones and need to tune them to reach to final one.


Regards,

 

[NARAYANK991]

Hi ,


Any formula is difficult when you do not know the uses it can be put to !


I can give several examples starting with the first unusual formula I came across , from Haseeb :


Put the month text in A1 such as Jan , January , Sep , Sept , September ,...


Enter the formula =MONTH(A1&0) and you get the month number !


Haseeb has come out with other gems , such as uses for the CHOOSE function.


Yet another one is from Sajan :


Using the N function to get an array of non-contiguous data ; the results are amazing , since such a simple function has been used to get something which otherwise would either not be possible or would require very complicated formulae.


I think we should compile a list of such unusual and powerful techniques , since keeping track of them is difficult.


Narayan