Excel has hundreds of formula's available, for varied subjects and requirements. Have you ever wondered if you could work a way where you could use user friendly language to derive what you want; kind of programming your way in Excel !! Heres a way how to...
In this post, we take an example of sample report from various location in an organization. For simplicity, we have prepared a sample table of Pune, Delhi, Mumbai, Goa and Bangalore against sales, expense and income.
Inorder to demonstrate the ease of use of natural language in Excel formulas, we will define data names in terms of places ie Mumbai (B2:D2) and item head ie Sales (B2:B8) and so on. Below is the list of names defined for the rest.
Now, say if we want to know the sum of a range, say total values for Mumbai or total expense, we will simply put =SUM(Mumbai) or =SUM(Expense). If we wish to know the expense done for Mumbai, we will simply use = SUM(Mumbai Expense). Here, when we include a space between the named ranges, Excel includes only the cells at the intersection of the two ranges, and gives you that value.
Go on, explore the possibilities on your own.
In this post, we take an example of sample report from various location in an organization. For simplicity, we have prepared a sample table of Pune, Delhi, Mumbai, Goa and Bangalore against sales, expense and income.
Inorder to demonstrate the ease of use of natural language in Excel formulas, we will define data names in terms of places ie Mumbai (B2:D2) and item head ie Sales (B2:B8) and so on. Below is the list of names defined for the rest.
Now, say if we want to know the sum of a range, say total values for Mumbai or total expense, we will simply put =SUM(Mumbai) or =SUM(Expense). If we wish to know the expense done for Mumbai, we will simply use = SUM(Mumbai Expense). Here, when we include a space between the named ranges, Excel includes only the cells at the intersection of the two ranges, and gives you that value.
Go on, explore the possibilities on your own.
