Count only text entries in any excel range

It's been a long time since, I've written anything for this blog. Being free on Sunday, here we go with this little post.

Problem in Hand: We want to count only the text entries in the list under consideration. Meaning we want to ignore all possible entries under sun in an excel cell except "Text". Image below will help you understand problem visually.

To solve this problem I have used function COUNTIF. The formula used is E7: =COUNTIF(B5:B14,"*"). Based on the data above, it has given 5 as a result. Image below is the demonstration of the same.

Thank for your time and "HAPPY LEARNING" :)

All about your LOAN in Excel

Are you planning to borrow money?  If yes, give my EMI Calculator a look and you'll know a lot about it.

It helps you to know how much EMI you'll be paying, during repayment period how much amount you'll have to pay extra and other things.

I have used PMT function to get the value of EMI.  The syntax goes like this:

PMT(rate,nper,pv,fv,type)

Rate is the interest rate for the loan.

Nper is the total of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Instead of going into the details of this function, I would suggest you to download the file and make use of it. Click to Download the file from here.

"HAPPY LEARNING"

Are you above or below AVERAGE in Excel?

AVERAGE this word is no new to any of us and it's meaning is known to all of us.  But what does it exactly means? The literal meaning is "a quantity, rating, or the like that represents or approximates an arithmetic mean".  For statistician it has always meant Arithmetic Mean and when used as an adjective it means typical; common; or ordinary. For example "Prakash has AVERAGE excel skills".

Today I am trying to throw some light on AVERAGE function in excel but will also talk about AVERAGEA, AVERAGEIF & AVERAGEIFS.  So be with this post and by the time you finish reading, you'll be equipped with the power of four functions of excel in a matter of few minutes.

AVERAGE function as described in Excel Help is "Returns the average (arithmetic mean) of the arguments." For example, if the range A1:M1 contains numbers, the formula =AVERAGE(A1:M1) returns the average of those numbers.  See the image below explaining this situation:

Things one should know about AVERAGE function are provided arguments can either be numbers or names, ranges, or cell cell references that contains numbers.  Also if a range or cell reference contains text, logical values, or empty cells, those values are ignored; however, cells with negative or zero value are included for calculation of average.

Now what should one do if his data also has negative or zero values and he is looking to get average of only positive values.  Got stuck? Don't worry there is always a solution for every problem, and this riddle can also be solved.  You just need to know the way to approach it.  The formula created by using combination of SUMIF & COUNTIF as =SUMIF(A1:M1,">0")/COUNTIF(A1:M1,">0") can provide us a solution. If you've any other way to solve our problem, please share with our readers in comments at the end of the post.

Now I believe you guys know the AVERAGE function and are ready to go ahead and study other related fuctions I mentioned in the beginning.  First to go is AVERAGEA, this function also provides arithmetic mean but it doesn't ignore logical values and text in arguments.  Empty cell in the range is still ignored by this function as well.  For example in the below you see AVERAGEA(A1:M1) & AVERAGEA(A1:M1,N1) is giving us same result because blank cell N1 is ignored for calculation by function AVERAGEA.

It may also be possible that during your course of routine you would like to know or find average based on single or multiple criteria.  For example you want to calculate the average for numbers above 30 or average for values belonging to a particular category and are above 30 as well.  For situations like these excel has two powerful functions known as AVERAGEIF & AVERAGEIFS for single and multi criteria respectively.

In the image above AVERAGEIF function has been used to calculate average in the range A1:M2 provide that the values are greater than 30. At the same time AVERAGEIFS is being used to satisfy two conditions and calculate average.  Two conditions here are the number should belong to A2 category and should be greater than 30 as well.

So this way today you've added or refreshed functions related to averages in your knowledge base. Let me know about your past or present experiences of using these amazing functions in comments.  "HAPPY LEARNING"

Finding Top 5 Performers in Excel

I recently came across with a problem asked by one of my friend.  He wanted me to create a function in Excel to know the Top 5 sales performer in his team.  I suggested him a easy way of sorting the data in descending order based on sales figures but he wanted to keep the data intact and know the result.

See the image below to visualize what he was looking for:

To provide him with an answer I used three Excel function namely:

• INDEX(array, row_number, column_number)
• MATCH(value, arrray, match_type)
• LARGE(array, nth_position)

Using these three powerful fucntions in conjunction, I came out with a solution and want to share it with you as well. The formula goes like this

E2: =INDEX($A$2:$A$12,MATCH(LARGE($B$2:$B$12,D2),$B$2:$B$12,0),1)

I dragged the formula till E6 and I got the result required, my Top 5 Sales Performers !

Now let me explain the formula's used here, basically LARGE function helps us in finding k-th largest value in our sales data.  Say fourth or fifth largest sales figure.

Using the value given by LARGE function, we are using MATCH function to know the relative position of the value in our sales figures.

Lastly INDEX function is giving us the name of the Sales Person. That's it !!

I hope you'll get benefitted by using this formula. "HAPPY LEARNING"

SAMPLE FILE

MS Excel: Subtotal Function and Filter

The Subtotal function provide the subtotal of the numbers in a column.  It allows us to perform a calculation in the worksheet, and the same calculation can also be performed on the subsets of the data by applying filters. The magic of this function is that it ignores values in rows hidden by a filter.

The syntax for the Subtotal function is:

        Subtotal(function_num, ref1, ref2, ...)

function_num is the type of subtotal that you'd like to create. Image below explains 11 types which we can select:

ref1, ref2, ... are the ranges of cells that you want to subtotal.

Please go through the example with images below, it will help a lot in understanding Subtotal function.

Here I want to know the average monthly salary for each city. Normal AVERAGE function won't be helpful here because once we'll apply filter to it will still give us the average of all the records in the data. 

Now as per the requirement if I want to know the average monthly salary payable in Augusta region, the SUBTOTAL function will provide me the desired result while AVERAGE function will still give me the average of complete data.

I believe, I've not confused you and this post will be helpful to you while at work.  Let me know your feedback or suggestion.  I SHARE COZ I CARE! "Happy Learning"

Download SAMPLE FILE

VLOOKUP to fetch 2nd, 3rd or 4th Value

We all use VLOOKUP and must have observed a shortcoming that it only provides you with the first matching result. I mean if we have data where the lookup_value is coming twice or more times, VLOOKUP will give us the result for the first occurrence only. In the data below if your boss is interested in knowing the commission paid to "Mavericks Reality" for their second booking, how will you find that?

Solution will go like this:

You'll be a adding a helper column right next to broker's names and try to get a unique name. I am using COUNTIF function to do the trick for me like: =COUNTIF($B$2:B2,B2)&B2.

What this will do is, convert our lookup_values as 1Mavericks Reality, 2Mavericks Reality and so on (like in the image below). Now you have a unique lookup_value and you can simply use VLOOKUP("2Mavericks Reality",table_array,col_index) and you are done with your answer.

This is simple right?

You can download the sample file to get a better understanding of this tip. It tells you how you create formulae to get 2nd or 3rd occurrence. VLOOKUP FOR 2nd, 3rd OCCURRENCE.

Share this tip with everyone. "Happy Learning"

Excel Function: IF ( logical_test, value_if_true, value_if_false )

Now in coming two weeks I have decided to discuss functions available in the stable of Microsoft Excel.  I have taken Logical function to start with, as I found them most useful will working data (this is my personal view :) though). To start with I am gonna explain IF function with an example.

The IF function is one of Excel's logical functions and it tests to see if a certain condition in a spreadsheet is true or false.

The syntax for the IF function is:

=IF (logical_test, value_if_true, value_if_false)

logical_test    - a value or expression that is tested to see if it is true or false.
value_if_true  - the value that is displayed if logical_test is true.
value_if_false - the value that is displayed if logical_test is false.

Some of the conditional operator you need to know:
<   - Less Than
>= - Greater than Or Equal To
<= - Less than Or Equal To
<> - Not Equal To

The following two images will tell you how we can use IF function. In cell A1 have value 15 and I want to populate test "Good" or "Bad" based on this value. For example if value is greater than 10 then it is "Good" else "Bad".

 

Formula which I have entered in cell A2: =IF(A1>10,"Good","Bad")

Similarly you can see cell B2 is giving me "Bad" because A2 is less than 10.

You can use this many ways and under numerous conditions. Few days back one of my teacher friend came to me and we were discussing how we can use Excel to help him in work. He gave me a situation where based on a number he wants to assign grades to his student. The parameters to grades were as follows:

A If the student scores 85 or above
B If the student scores 65 to 84
C If the student scores 50 to 64
D If the student scores 33 to 49
FAIL If the student scores below 33

An example of this data looks like this:
To solve his problem I used IF Function. I created a similar table below his data and wrote a formula as B12: =IF(B2>=85, "A", IF(B2>=65, "B", IF(B2>=50, "C", IF(B2 >=33, "D", "Fail" ) ) ) )

I draged the formaula cross the data sheet and got my result.

I hope this is clear to everyone. I know many of you are already using this function.  Kindly let us know how do you use this function in your daily life.  You can post your experiences and example in the comment box, it will help us in learning new things.  "Happy Learning"

Pass this to your friends and colleagues this may also help them! 

To download the example file click here

Excel Function: MROUND (Rounding value to a multiple)

Readers if you want to round a value to some multiple of a whole number, you need to know MROUND function.  It works wonders when it comes to round a value with respect to a given multiple. The MROUND function can be used to round a number up or down to a specified multiple.

The syntax for the MROUND function is as follows:
=MROUND (Number, Multiple)

 
Number: the number which you want to round up or down

 
Multiple: the number provided will be rounded up or down in multiple of this figure.  Number will be rounded up if the last digit is more or equal to 5. If it is less than 5, it will be rounded down.

Another important thing you should know is that both number and multiple should have same sign. Either positive or both bearing negative sign else the function will give #NUM! Error.

You can find this function under Formulas tab of the ribbon menu. There you choose Math & Trig and click on MROUND in the list.

In Excel 2007 (and later) this function has been removed from the Analysis ToolPak add-in and is available as standard. For people using Excel 2003 or earlier, this function is only available when you have the Analysis ToolPak add-in loaded.

In the below screenshot I have tried to incorporate all possible examples:

I hope you all will be benefitted with MROUND function.  Happy Learning!