Category: Formulas

TRIMMEAN FUNCTION

Formulas

Hi there,

 

How was your experience working on excel to find the mean for a set of values? I hope it will be good because you are passionate on Excel. Here is a new function which exactly works like a mean but with a condition. AVERAGEIF or AVERAGEIFS are the functions which you were using till date to find the mean for a given condition.

TRIMMEAN function in excel

 

However, TRIMMEAN is an another statistical function which helps to find the average of values based on the percentage of exclusions. Which means,

MEAN: Mean is nothing but the average of values divide by count of values ( SUM / COUNT).

TRIMMEAN: This means the average of values will be calculated based on the percentage criteria.

SYNTAX: =TRIMMEAN(ARRAY,PERCENT)

Full story »

Hidden Tip About SUMPRODUCT

Formulas

Hi there,

 

SUMPRODUCT and SUMIFS are the most powerful functions which extracts filtered data. SUMPRODUCT is the most powerful function where as SUMIFS is the fast best function to pull the data because of the impact on file size.

 

As per the documents, SUMPRODUCT will multiplies the array values and sum's each of them. But, it also does the calculation based on the explicit value for each array given in the function.

 

=SUMPRODUCT(array1,array2,array3,array4,array5...)

Full story »

Convert Number to Words (100 = One Hundred Rupees Only) (no VBA)

Formulas, How-to's, Tutorial

Hi there,

 

Explain this formula and win exceltoxl goodies (choose the t-shirt which you want)

In this post, I will be sharing excel howto's.

 

How to convert Rs.100 = "One Hundred Rupees Only" through Excel Formulas?

 

This is our challenge for today.


Due to short of time, I am publishing only the formula in two currencies. And, I am leaving you the explanation of the formula. 


You can also download the working file.xls in the bottom of this post.

 

1. Indian Currency (Rs. = Rupees)

   a. Crores as the base

Full story »

VLOOKUP for Case-Sensitive Mapping in Excel

Formulas

Hi there,

 

In this post, I will be sharing a giant problem in excel. Even my client is in dilemma how to crack it. After giving him my solution, they were very happy because they are dealing with dollars.

My client has a database where the renewal amounts are placed next to customer ID's. So, our work is to deduct the renewal fee from the customer's account. You, as a reporting analyst will know how crucial this is upto. Finally, my client finished his job in couple of hours which was actually taking more than a week.

 

The scenario is, the client has a list of customer ID's where they are all case-sensitive. They look like this:

 

Mapping Table which needs to extract renewal amounts to the first table
 

Sorry, if this was little messy.

Full story »

Avoid Formula Rebuilding (small tip)

Formulas, Excel Tips

Hi there,

 

I have something for the formula enthusiasts today!

 

While working on formulae, sometimes we would want to pause the formula and recheck the cell addresses we have mentioned in the formula or the data we have selected or the syntax in general just to ensure we input the right things.

 

Check the illustration below to get a better picture: 


importance of apostrophe in formula

In this scenario, we mostly press ESC button to check back our doubts and come back to re-write our formula from scratch. This is a tedious and time consuming process. 


Why reinvent the wheel? Excel is not like our strict Math teacher in school :p It does give us so many flexible options, if only we explore them! 

 


So, to avoid such instances we can always pause the formula where ever we want and just apply ' (apostraphe) before = (is equal to) symbol.


Below picture shows what I mean here.

importance of apostrophe in formula

This will make sure your formula will pause where ever you stopped and can be resumed after removing the ' (apostrophe).


Note: We can use any special characters (except  = (is equal to) or + (plus) or - (minus)) or alphabets or numbers in place of apostrophe (') to pause the formula.


DID YOU PAUSE FORMULA?

Please do share your experience about this topic in the below comments section.

If you like this post, please do share it on your social networking sites by clicking on the icons placed around the blog.

If you want to receive such updates to your inbox, REGISTER here for free.


enjoying your shopping by just few clicks, your loved product will knock your door bell.



SOME IMPORTANT LINKS:

GROUP SUM OF THEM

Difference Between FORECAST & TREND

GET DISTINCT COUNT FROM FORMULAS

USAGE OF -- IN THE FORMULA

RANDOM NUMBERS without DUPLICATES

Thanks,

Group SUM of them

Formulas, Tutorial, Arrays, ActiveX & Form Controls, 3D-formulas

Hi there,

 

This post is a consolidation of SUM of the posts that I have wrote earlier. The reason for why I am grouping them is to let you know how many ways the simple SUM function can be used in our day to day reporting.

Let me proceed to first example:



Full story »

Contact / Help. ©2019 by Abhilash VK. Design & icons by N.Design Studio. Skin by Tender Feelings / EvoFactory.