How to use the Excel MID function

When using WPS Spreadsheets to process data, sometimes we need to catch part of the data from a series of numbers. We can use the MID formula to realize it, to catch up a certain length of characters from a series of numbers. In this example, there is a list of phone numbers. The MID function can extract the long series of numbers, keeping the last seven numbers when removing the first three characters. Select cell D2 and click the Insert Function to pop up the Search for a function d....

SUBTOTAL function: Calculate the data with the hidden value

Take this table as an example. When using the SUM function to do summations, we may find two rows hidden in the table, but the result of the summation still includes the hidden value. So how can we calculate the data containing hidden values and get a result that does not include the hidden values? This video can help us and it can be also used in Microsoft Office Excel and Openoffice. In this case, we can use the SUBTOTAL function. Next, we are going to show you the usage of the SUBTO....

The Trace Precedents function

WPS Spreadsheet could be an alternative to Microsoft Office Excel. It includes 100's of built-in formulas, pivot tables, and more. While processing a table, we have to double-click a cell containing a function formula to view the referenced data cell, However, this will easily lead to incorrect editing of the formula. At this time, we can use the function of Tracking Precedents of WPS table.Take this table as an example. We want to see which cells are referenced by the formula of ....

Look up data quickly with LOOKUP function

Today I would like to introduce you to the LOOKUP function, which has many relevant knowledge points. Beginners will have some difficulty in learning the LOOKUP function, and the video will introduce the common use of this function and the notes in the process of using it. Take this spreadsheet as an example. If we want to find out who is the purchaser based on the lump sum in cell J9, we can use the LOOKUP function. The general statement structure of the LOOKUP function is LOOKUP (Loo....

COUNTIFS function: count data of multiple criteria

We often need to count the number of data in our work, so how can we count them quickly? Here, we need to use a new function, COUNTIFS. The grammatical structure of COUNTIFS is COUNTIFS (range1, criteria1, [range2, criteria2]...) Take this table as an example. Now we want to count how many types of foods are produced in Thailand and also have a sales volume of more than 5,000? There are two criteria that we want to consider. One, the sales volume needs to be greater than 5,000, and the....

Use Date function to convert date format

We may sometimes need to convert the format of the date in the table when using WPS Spreadsheet like Microsoft Excel. At this point, by using the Date function, we can quickly generate a specific date symbol. Take this table as an example. This table contains the information on the date of birth of some students in class. We can see that the information of year, month, and day are separated in this table. So how can we convert the format quickly to know the birthdays of the students? 1....

AVERAGE function quickly average data

In WPS Spreadsheet, we can use the AVERAGE function to quickly find the average value of a set of data quickly. It is often used to calculate average wages, average sales, and so on. Take this table as an example. We now need to find the average sales of the bakery this week. After we click cell D4, click Insert Function, and enter AVERAGE in the Search for Function of the dialog box. Click the OK button, and then the Function Arguments dialog box will pop up. The data we filled in Num....

The INDEX function

The INDEX function can look up a value regarding its row and column numbers.There are two forms of the INDEX function, the Array form and the Reference form. · One: Array formWe'll use the Array form to return the value located in the seventh row and the fourth column.First, select cell J6. Next, click Formulas and then Insert Function to insert the INDEX function. The function syntax:INDEX(array,row_num,column_num)The first parameter, 'Array', refers to the array for valu....