• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Methodology for solving a problem in EXCEL

Now, there's an impossibly open question to answer! It will depend entirely on the nature of the problem. For me, it's a choice between formulae or PQ, as I don't have VBA in my toolbox. I find PQ very helpful these days.

PS When you said "guys", I presumed you were not being gender-specific. ;)
 
lol your presumption is 100% true. thanks for your replay.
i do agree. It is a very broad question,

being an excel beginner i have this image of an advanced user who when facing a problem instantaneously starts working his magic.

I just want to know what is the thought process that different excel ninjas go though to get the most optimum output. I'm sure everyone has his own.

Thanks all
 
It's a gradual process. You do get to stage eventually where you think, "Oh, that's an INDEX MATCH", or "COUNTIFS is needed here", but that comes over time and with experience. It's a bit like learning a language.
 
I do not think there is a unique methodology; if there is I have some explaining to do as to the reason my workbooks appear so different to those of others.

Some of the experts on this site appear to have an amazing repertoire of tried and tested formula approaches at their finger tips. I do not.

I start by analysing the input data and naming the larger objects to capture their relationship to the business problem. As Ali suggests, different problems might lead one to consider array formulas, pivot tables, PowerQuery or VBA. I then try to identify a sequence of process steps (merges, lookups & joins, filters, sorting, aggregation …) that will, step by step, generate information from which the solution should emerge.

Only then do I start writing formulas to generate these intermediate data objects, which I either try to place in a business context or hide away if they are a mere computational device that lacks business significance.

Finally, the solution is packaged, tested, documented and delivered.
 
You must log in or register to reply here.
Back
Top