One of the most common questions we get asked is NOT how to do a particular formula or function but rather WHICH excel formula to use. As a result we have created a simple 5 question app to help guide Excel users on which function, formula or tool is best suited to what they are trying to achieve. This is like an Excel Formula AI BOT but where you answer a few questions and we show a list of functions/ features to consider.
Please click here to open it up and while it is opening read the instructions below
Which Excel Formula to Use App
Instructions for ‘Which Excel Formula to use’
The 5 questions can be used in combination or on their own. As you choose the answer that suits your needs, the suggested formula, function or tool will appear below. Look through them and their description and consider how it could help you. Click on the button to get more information on which excel formula to use.
Understanding the questions
Within a cell or over many cells in one go?
The options are Within a Cell or Over Many Cells at the same time.
Within a cell implies that you want to do something to a single cell e.g. extract the first 3 letters or turn a date into a weekday. This option is also chosen even if you intend copying it to other cells. Each of these cells is working within only one cell.
So in the example below, we want to extract the three letters of the city from cell A4 and put it in D4 (red arrow). This is working WITHIN cell A4 to extract the information. Even if you intend copying the formula down (green arrow), it is still WITHIN a cell as each formula is looking WITHIN only one cell.Over Many cells at the same time implies that what you need to do spans over many cells e.g. looking up a value in a list or finding the maximum number out of a list.
In the example below, we want cell B11 to look OVER MANY CELLS (red box) and find the relevant student. Even if you copy this down (green arrow), each formula is still going to have to search OVER MANY CELLS to find an answer.
What type of data are you working with?
The options are Number, Text or Date.
You can choose these options if you are specifically working with one of these types of data. If the cells are mixed leave it as ‘All’. By choosing one of these options you are asking for all functions and tools that can work with this type of data.
Does your answer have to be exact or an approximate?
The options are Exact or Approximate.
An exact answer implies you want the exact answer, for example it must find the value in a list or find nothing.
In the example below we want to know the tuition fees of student 100129. It must find the exact answer and not guess any numbers.
An Approximate answer implies that Excel needs to make a guesstimate.
In the example below we want to know what the interest rate is on a bank balance of 4900. But the table we have does not have a 4900 option so Excel is going to have to APPROXIMATE which band the number falls into.
Are you expecting a single cell answer or potentially a number of cells?
The options are Single Cell as Answer or Many Cells as a Answer.
Do you want a single cell answer? In the example below we want excel to look at the date in A2 and convert it into the day of the week. We only want a single answer for that cell. Even if we want to copy it down as per the green arrow, each cell is still a single answer based on what is in column A.
Do you want Many Cells as a Answer? In the example below, we want to search for Sales Person SP002. Excel must return as many answers as it finds and it may take up more than one cell.
Which descriptive word matches what you are trying to do?
There are many options, all describing something you may want to do in Excel. Choose an option and see which functions and tools may assist. If we have missed something, please email us and tell us other words you may use.
Some of the options include Analyse, Calculate, Clean, Combine, Compare, Convert, Count, Date and Time, Duplicate, Extract, Filter, Finance, Financial Modelling, Find, Get info, Import, Inputting, Link to/ From, Lookup, Navigating, Presentation, Rank/ Sort, Replace, Round/ Remove decimals, Sensitivity, Split, Sum, Unique
Examples
Say we are looking to extract the first 3 letters from a cell that contains the code JHBZAGAU.
In this case we would be working WITHIN A CELL. The data type we are working with is TEXT. We want an EXACT answer in that it must give us EXACTLY what the first three letters are. We expect a SINGLE CELL answer.
At this stage you will see all the formula and tools that can handle this criteria and the one we are looking for (LEFT) will be shown in the list.
If we went into the last question and chose EXTRACT it will show a more relevant list.
Feedback please
At the bottom of the tool is the ability to make comments and suggestions. Please include an email address if you want a reply