List of Excel Formula
A spreadsheet is an amazing tool that touches many facets of life from business to education as an analysis and reporting tools.
Below is a list of Excel formula that will come handy while using the spreadsheet.
Suppose you have a list of dates with time in Excel and users want only dates after excluding time or vice versa.
When you have column and users want the count of values in the range and give the addition of last Nth values with the given reference. The same way knows Average of last Nth values.
After reading this article you will able to change the normal date to excel date when time is also given in the same cell or substitute date to dd- mm-yyyy format.
In this article, you are going to learn time difference (add or subtract) between two times and to get negative time to remove ## in a cell. You can also extract hours, minutes and seconds from the given time.
To extract date and day from the week number and year. suppose you want to know the day (Monday, Tuesday, etc) and date based on any week number.
We have two columns, year and week. Our task is to know the date based on the given year and week number.
Changing the time format from AM and PM or 12 hours to 24 hours’ time format. You will learn here to convert the format of time.
In case you want to count the number of days and day names in Excel. Return number of days worked on the basis present date after excluding weekends.
Calculate the age between two dates i.e. today’s date or any given date and birth date with the exact number of years, months, and days.
Suppose that you have a list of numbers and users want the average of maximum (high) numbers like first, fourth, sixth or any highest or lowest values from the array.
In this post, you are going to learn how to calculate the average of the last four, five or any nth values in the columns or rows with the help of formula offset and count.
Suppose that the user wants to find how many days you work or the next working date after excluding certain days like weekends and holidays.
In this article, you are going to learn how to merge Text, Numbers, characters, etc of different columns in a cell with space and delimiter.
To display certain Date or Currency format with text in same Excel cell. To combine Text with number use TEXT function with a custom format.
In case you want to show Random Numbers with Decimal point in multiple rows and columns. For this, we will apply function RANDBETWEEN and RAND.
Return the Name or Text of next cell based Min and Max value. We will use Excel Index and Match function together with Min or Max function to get such value.
Count If there are no error in column then true and returns average otherwise show an Error.
Excel does not return how many Blanks and Texts (both) using a function. This formula will return the count of number of Blank cells & Filled values at once.
After reading this article you will be able to count Text in multiple rows that contain text and numbers (both) in a Data set.
Adding multiple rows if criteria met and then on certain conditions SUMPRODUCT functions are being used in Excel. In case you want sum of the product based on condition, you can apply this formula.
After reading this article you will be able to calculate average numbers in a column between two dates in Excel.
To get average after excluding outliers. In case you want to calculate average after excluding some maximum, minimum, blank or 0 values in the given range.
Calculate Average based on criteria matching string or data of a column that display multiple times and returns average of respective next cell values only.
Adding numbers referencing to the next column where some cells are empty and remaining fills with text, number, dates, etc.
Adding the respective value of a particular name in a column based on the lookup value using the Excel’s Index and Match function. In case of similar names in a column and selecting one of the multiple rows or columns, we can apply this formula.
After reading this article, you will be able to create a formula for counting unique text of a given list, excluding empty cells. Let’s understand with an example, you have a list of Product Managers, but the problem is that they have duplicate entries. Read also count unique text with
The Excel formula returns a count of only unique texts (no duplicate) based on the given condition of a given column.
In this article, we will be looking into examples to count only words or numbers in Excel using different formulas to return the count of the Excel cell.
Count and Sum a number of cells or row based on multiple colors shade without using Excel VBA. We will investigate three different technique to count color cells.