Only works with number, date, time, datetime, datetimezone and duration values. On my display sheet, have a list of IDs in column B, various data in the other columns, and the week number in column X. SUMPRODUCT is one of the most useful functions that can extract data or do complex calculations by scrutinizing all mentioned criteria along rows & columns from an array. Returns the average of a data column depending on multiple criteria. The first parameter is the , which is A2:A10. Count from multiple columns with multiple conditions in Excel. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. If I understood correctly, you are trying to choose dates before 4/1/14 OR after 8/30/14. To perform a dynamic two-way average with a formula, you can use an Excel Table, the UNIQUE function, and the AVERAGEIFS function connected to the spill ranges returned by UNIQUE. NOTE: AVERAGEIF is only available in Excel 2007 and later. I'd just have to order those fields to get the result I want. I have 2 Tabs, Tab 1 = Multiple (2) Tab 2 = Auto Created --> lets call it "Sheet1" Sheet1: Column A - is Date (15 min interval) Column B - Lets call it "CPU" Column C -. Averageifs using OR function. AverageIFs multiple columns when meets multiple criteria in one column. In the UI, open the Advanced Editor. 2. About. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory,. I'd like. In Column N2 and NO I put those 2 times respectively. Average for multiple criteria in one. AVERAGEIFS: Returns the average of a range depending on multiple criteria. You require an OR condition since any one row could not be both 1 and 2. Range("M:M"), _ Sheets(modelName). g. In the above, the sum of the blue/greens is 23, their count is 5 making the average 4. 8. Averaging a range of values is easy. Coachcasa New Member. "A" and "G") may be zero. The AVERAGEIFS function will take these criteria and ignore the entire row if there is a blank. Use the following formula for G1 and copy down to the bottom of your list in column G. It can be used as a worksheet function (WS) in Excel. AverageIf(Range("F5:F30"), "Savings",. so on so forth. Array formula 1: find value with two or multiple criteria in Excel. You are comparing three whole columns to a range 5 columns wide and 8 rows high. This formula can be easily solved with the AVERAGEIF function or the AVERAGEIFS function . Sorted by: 1. Excel: How to Use AVERAGEIFS with Multiple Ranges (different columns) Hot Network Questions Will recording as MIDI lose the quality of a WAV? Who am I? Mind, body, mind and body or something else?C3:M17 entire target range. criteria_range is the set of categories containing the specific criterion whose average will be taken. To calculate an average by group with a formula, you can use an Excel Table, the UNIQUE function, and the AVERAGEIFS function connected to the spill range returned by UNIQUE. This function can be used to know the average balance in the inactive bank accounts with an updated KYC. YES, that completely helped. We can add as many criteria as needed to determine our final results. In the opening Advanced Combine Rows dialog box, (1) Select the column you will search for lookup value, and click the Primary Key; (2) Select the column where you will average vlookup findings, and click Calculate. The AVERAGEIFS function calculates the average of all sales values in the range C3:C11 if the corresponding cells in the range A3:A11 (Sales Manager) with six characters; and the corresponding cells in the range B3:B11 (Bonus Date) should be greater than or equal to 10-May-20. 2 Apply AVERAGEIFS Function for Multiple Columns. Then we create a COUNTIF function to count the number of records that appear more than once: =count. OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i. The function was introduced in Excel 2007. Averages the values in the "Quantity Ordered" column, but only for the rows where the "Item" is "Shirt". The AVERAGEIFS function in Excel can calculate the average of cells only with values using multiple criteria. Unlike some functions, e. Thus, 5 columns are reserved for the category of a given record. Here's the formula I'll use: =COUNTIF (C2:C17,">100") This is a simple, two part formula: simply point Excel to the list of data to count, and the rule to count. To be honest, if you're only going to have 1, 2, 3 or 4 in that column, then you could easily use: =AVERAGEIF(F4:F39,">=2",D4. RangeForCriteria1: C2 to C8. g. What are multiple condition averages? They are averages based on a column of numbers that include rows that meet one or more criteria. I want to accomplish what this formula does, but to include. I have used your code of calculating rolling average but. And you always get a #DIV/0 when you try and average nothing. To calculate the average, Excel sums all numeric values and divides by the count of numeric values. If you select cell D8 of the example, the complete function appears in the formula bar above the worksheet. FORMULA. 1. AVERAGE using multiple criteria from. AVERAGEIFS Match multiple criteria. I would like a formula to display the average of a number of filtered values in Excel. 2. Use AVG and COLLECT: =AVG(COLLECT({Column to Avg}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"))AVERAGEIF (or AVERAGEIFS) won’t handle your ranges being different sizes; one is multiple columns wide, the other just one. Then you can merge this new table into your original one matching on Group, then expand the Average column. In this video we will be learning calculating conditional average from multi column data. AverageIFs multiple columns when meets multiple criteria in one column. stack the 2 ids and 2 weights columns to create dataframe with 1 ids and 1 weight column. Group Average A 113. AverageIfs(Sheets(modelName). Data / Get & Transform / From Table/Range. Sample Usage. Criteria Required. In Excel, we often need to calculate the average of multiple columns based on multiple conditions. Note that the desired values that we wish to average are present, along with a bunch of #DIV/0! errors. Syntax. You can apply the AVERAGEIFS function on a single column to calculate the average. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. Where Column B contains the numbers being averaged, and column A is your zones. INDEX (F2:H5,0,MATCH (A2,F1:H1,0)) Calculate the Average of Result based on the returned column. Id like to build a new column in my PowerBI table called "average". COUNTIF(S), SUMIF(S), with which the use of entire column references has virtually no detriment to calculation performance, array-processing functions must calculate over all cells passed to them. Any record appearing more than once (>1) is considered a duplicate. 365; Platform. =AVERAGEIF(A1:A50,">0") This function only includes in the average those cells that contain values greater than zero. I’m trying to get an average of data in column C based on the corresponding time range in column A. Now, write down the VBA code to calculate the average of multiple ranges. The formula makes use of a virtual array, containing the values of column C and the output of the mmult () function. AVERAGEIF (or AVERAGEIFS) is not the function you want. Joined Jul 23, 2014 Messages 2. AVERAGE takes multiple arguments in the form number1, number2, number3, etc. Specify the range to check against the 1st condition (B3:B15 - items). criteria_column1 - The data. So, we get the sum: SUM (Store1:Store3!C6) And then we use the FREQUENCY function (s) to get the count of non-zero numbers. In this example, we want to combine the cell ranges A2:B14 and D2:E14. 0. How to Calculate Average of Multiple Columns Based on Single Condition 1. AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range Required. Row 2: 104 – 99 = 5. Using the Power Queryfeature of Excel can be an efficient way to do this. AverageIFs multiple columns when meets multiple criteria in one column. I've tried several 'averageif' formulas with 'and' and 'or'. This is the range that contains numbers to average. The formula I made was the following:In column C, there are 3 formulas, to verify the result of the AVERAGE formula. The criteria range is data[Group], and the criteria is the spill range: =AVERAGEIFS(data[Score],data[Group],F5#) To calculate a conditional average for multiple columns of data, you can use the AVERAGE function with the FILTER function. . From the remarks for the AVERAGEIFS function: Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell. Many more great Excel tutorials linked below:sure to watch my other Excel tutorial vid. Another issueis that some rows not all of the columns in reaction [Hit 1]:reaction [Hit 5] are populated and the blanks should not be included in the average. Arguments can include numbers, cell references, ranges, arrays, and. I have tried using the OR function, and tried the curly brackets, but both give me errors. I am using the averageifs function, and have one column where I need to calculate the average if either of the criteria are true. 0. This screenshot shows my attempt to replicate your setup: The formula in column D is based on the following steps: Find the column number corresponding to the category. Can seemed to get it right other than a formula that reads: (averageif red + average if yellow) / (counta (if yellow) + counta (if red)). We take a dataset that includes some students,. Hi I am very new to VBA and have been trying to figure out how add formulas into Excel via VBA but been hitting the wall. This sort of ambiguous situation it can't compute. AVERAGEIFS uses all criteria you offer to select a number. Here, cell range B5:B13 is selected as criteria_range1, and cell range C5:C13 is selected as criteria_range2. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). The first argument is average_range; this is the range that contains the values to average. Result. 0. That seems a bit complex so I tried an other approach: do the average value of the first entry for each name. 6. 2. Re: AVERAGEIF Multiple Columns Hi, In addition to above example, it is average of "D4:H4", means in case of multiple occurrence it will not work. To Blue_Elliot's point, an AVERAGEIF function would do what you are wanting. Combining the averageifs function I think does not work with "or" criteria, so I tried this [but it's not working]. Aug 2, 2017 at 10:41. Note: you might wonder why we don't use the SUMIF function with a 3D reference to sum multiple worksheets with criteria? The problem is that SUMIFS, COUNTIFS, AVERAGEIFS, etc. In this video show you how to calculate the average values across multiple columns with a filterin. Phrased another way, you could say it like this: "calculate the average of columns J and K (from the 1st screenshot) for all the instances where the following conditions are met: column K is less than or equal to 1638, column M is "1". To get an average of data in a column with multiple criteria the following formulae may be used. =AVERAGEIF (B:B,F1,C:C)Hi Creative999, AVERAGEIFS doesn't have an OR option. Here is a solution using a helper column: Select all five sheets by clicking on the sheet tab of Sheet1, then Shift-clicking on the sheet tab of Sheet5. Excel begin AverageIF function. However, when I try a range with multiple columns, Excel only seems to include rows if the code appears in the first code column. Double click the AVERAGEIFS command. Is there a simple way to copy the formula over and have the column numbers move four across to average subsequent years?In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. In Excel 2016+ (may be different in earlier versions): select some cell within the data table. 0. One last one, just need to add in one further criteria to the above. microsoft-excel; worksheet-function; Share. So I would like to select the 2011 year, the month of May and get the average of the values in column C that satisfy these requirements. The formula in E2 is either, =SUMPRODUCT ( (C2:C8)* (B2:B8=F1:G1))/SUMPRODUCT (-- (B2:B8=F1:G1)) Unfortunately, no, it will not help. Note the lookup_value in XLOOKUP is 1 since the. AverageIFs multiple columns when meets multiple criteria in one column. You may want to make sure that the range size and shape of each column reference is the same. In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. AVERAGEIF function using reference as criteria range. For example, you may want to find the average of a column only for specific regions or months. Subsequently, insert the following. One approach would be to do a Group By on Group using Average as your aggregate. Formula. When I adjusted my formula to C2:N22 I get the result of 4. So if I need, say, a. )] I hope I was able to explain what I want. We know that an average is a sum divided by a count. Breaking my head over this, time to look for help :(I have a sheet with raw data, as illustrated below. Read below for details. Syntax = AVERAGEIFS (Range, Range 1, criteria 1, range 2, criteria 2) In this case, my requirement is to find the average of the data in a range of numbers, without considering '0 - Zero' & values greater that '3000'. To get an average of data in a column with multiple criteria the following formulae may be used. Countif with average. The formula I have now works fine but as I add more columns and the data table that may be out of order it would be much better if I was able to incorporate a lookup or match into the. When say conditional average, we think of Averageif, but in this sc. Let us see how to apply this function with multiple ranges. I've found the following post: AVERAGEIFS with multiple criteria on one range In which T. Just keep in mind that parameter orders are slightly different from AVERAGEIF. Joined Dec 15, 2005 Messages 1,227 Office Version. Expected output would be "4". Select the range of cells you want to evaluate. I've also tried inserting a new row below the dates (i. To accomplish the task, you build an AVERAGEIFS formula with two criteria: Define the range to average (C3:C15). 0. I am wanting to do an averageifs statement, but it does not seem that is supported. Hot Network QuestionsIn this table i would be looking to get the average of all the "A" in column B that also have "D" and "E" in column C (Using the values in column F to get the average) however in some instances a combination (e. This is some simple data that is similar to the data I'm actually working with. One or more cells to average, including numbers or names, arrays, or references that contain numbers. Type "=AVERAGEIF (" into a blank cell. Follow. Criteria Required. Starting in Excel 2007, you should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions, and in Excel 2016 MAXIFS and MINIFS functions, instead of SUMPRODUCT. conlan New Member. The VSTACK function accepts 1 or. The AVERAGEIFS function in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. Go through the steps below to accomplish this. That seems a bit complex so I tried an other approach: do the average value of the first entry for each name. AVERAGEIF: Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria: AVERAGEIFS Excel 2007+: Returns the average (arithmetic mean) of all cells that meet multiple criteria. I have created a simple formula using AVERAGEIFS - using multiple criteria, it selects rows that match the criteria and returns an average for the numbers which occur in the selected column for those matching rows. The VBA help does provide some detailed. 0. To test if the date is within the year of 1999 you can use 2 criteria: AVERAGEIFS (N:N,A:A,">="&DATE (1999,1,1),A:A,"<"&DATE (2000,1,1)). . Stack Overflow. Columns(20), _. The AVERAGEIF function calculates the average of cells in a range that meet a specific condition. 5. Select the columns or rows you want to compare. e. AVERAGEIFS needs all ranges to be the same size (including the range to average) so if you want to average multiple columns based on multiple criteria in single columns then it won't be possible to use AVERAGEIFS - try using an "array formula" like this: The issue arises when there are multiple columns with same group as Averageif takes the first column only. [like excel =AVERAGEIFS(average_range, criteria_range1, criteria1,. I used the TODAY function to indicate 30. Yep! Averageif is capable of handling multiple text conditions in one column. This is achieved through the use of the Excel AVERAGE function. I want to build a table on the last sheet where I would calculate the average of every price of each product for the entire period of time of the document (several years and thus several sheets). For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. g. Using SUMPRODUCT Along Multiple Columns and Rows. AVERAGEIF with multiple criteria: AVERAGEIFS. I am trying to average multiple columns (ignoring zeros and blank cells). 1. In the example shown, the formula in cell G5 is: =AVERAGEIFS(data[Score],data[Group],F5#) Where data is an Excel Table in the range. Second part SUMPRODUCT ( (C3:M17<>"")* (A3:A17=750)* (C1:M1=15)) gives. The AVERAGEIFS Function [1] is an Excel Statistical function that calculates the average of all numbers in a given range of cells, based on multiple criteria. If not included, `criteria_column` is used for the average instead. Windows; Dec 6, 2011. 3. I'd like to calculate the average duration per TestName (column A) between the 2 timestamps (B and C) in another sheet. If you need to add two criteria, for example the average quantity of shirts that Joe orders, use a combination of AVG and. 2, and finally averages the values from the range E2:E10 that are in the rows that qualify the above two conditions. AVERAGEIF function on different cells in different sheets. Unsure where my errors are on this one. Sample Usage. I am trying to calculate average based on multiple column. Wat needs to happen is that in the Columns C the program needs to calculate the average of all the values of F IF the number in E falls within the interval in Columns A and B. Now, write down the VBA code to calculate the average of multiple ranges. I'm wondering if there is a way to nest a vlookup or index match or anything like that in the AVERAGEIFS that read the criteria column heading and criteria in a cell (or 2 if they need to be separated) to be added to the AVERAGEIFS. 1 Answer. I came across a scenario today however which stumped me, and was hoping that perhaps someone on here might have a solution! I am attempting to get an average of multiple non consecutive cells, ignoring anything that is equal to '0'. Let us see how to apply this function with multiple ranges. In Excel, it’s not possible to use the AVERAGEIF () function to calculate an average value using multiple ranges. To include all October dates you should use either "<="&DATE (2023,10,31) or "<"&DATE (2023,11,1)The fact that you have an "or" condition is going to screw up your use of AVERAGEIFS, since it only does "and" conditions. I have 10 years worth of 24-hour data in range G3:P35162 and need to average only the data that meets the criteria of being equal to "Jan" in range A3:P35162 and "1" in range D3:D35162. This cell range contains multiple columns. I'd just have to order those fields to get the result I want. i have tried multiple ways, including this one: Avg = List. Valko proposes =IFERROR (AVERAGE (IF (ISNUMBER (MATCH. Is there a way to do. The AVERAGEIFS function works like AVERAGEIF, except it is designed to apply multiple criteria. e. . if column G is the sum of columns A and B and column H is the sum of columns C and D you could use this version3. 3. Failed attempts. Returns the average (arithmetic mean) of all cells that meet multiple criteria. The above formula. kckliodna; Aug 25, 2023; Excel Questions; Replies 3 Views 452. I want the average of 'column b' data that corresponds to 'red' and 'yellow'. However, the result needs to ignore any zeros that appear in the data. So I have 3 columns in this example. The latter creates a column with 1's if 'Franrkijk vakantie' is found in that row AND the date in column E matches the date in L1. Average Revenue by "FY Quarter, Month, City and product" and then calculate "Max Sales by FY quarter, Month and city" and finally calculate difference. How do i get an average of a column, with multiple conditions herof an OR condition. The AVERAGEIF Function allows you to average the sum of a range of cells that meet a certain criteria. The Regexmatch will help us to use multiple text conditions in one column in the Averageif function in Google Sheets. goaldFor column i want to add-up all values and devide by the number of values. I can create the AVERAGEIF on each page to get the price average for each product types: =AVERAGEIF (range of product types;"name of the product type. The VSTACK function allows us to create a new table that combines two or more ranges into a single continuous range that we can use for the AVERAGEIF formula. To average all values in multiple columns you can apply an Excel. Using SUMPRODUCT Along Multiple Columns and Rows. criterion: The pattern or test to apply to `criteria_column`. For example, you need to calculate the average of three date’s quantity of Apple excluding zero cells in the table, you can apply the following formula. The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. So all rows with the same combination of. Assuming I know the correct column, the formula is easy: AVERAGEIFS(COLUMN B, DATE,>= START DATE, DATE, <= END DATE) What I'm struggling with is how do I identify the correct column using the location names?Step 1: Click on an empty cell where we want the average to be calculated. For. It returns a number that represents the average (arithmetic mean) of the arguments. You could perhaps use SUMPRODUCT to get the sum of all the columns with the given restrictions, then COUNTIF to get the number of columns with the given restriction: =SUMPRODUCT (A2:D11*ISNUMBER (SEARCH ("Yes",A1:D1)))/COUNTIF (A1:D1,"*Yes*") SUMPRODUCT should return 77+88 and COUNTIF should return 2. In this section, we will use. 5. Average for multiple criteria in one. The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. Formula. Excel can calculate a sum, average or count of values based on specific criteria by using the SUMIFS, COUNTIFS and AVERAGEIFS functions. Thus, 5 columns are reserved for the category of a given record. Averageifs with Or. xlsmWhen I try to drag the formula across though, the start and end cells only move one column over (i. This cell range contains multiple columns. Another way to open the Visual Basic Editor is to simply right-click on the sheet and select View Code. If you instead would like to use the AVERAGEIFS function with OR logic to calculate the average value of cells that meet at least one of multiple conditions, you can use the following syntax:. Two-column example. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column - The data column to average. 1. AVERAGEIF based on multiple columns in google spreadsheet. So for example in the sample above, how can I take out the average of "A" and "B" and "Z" and "L", such that I can change A to B and the answer changes. In G1 get the average value for in column C for all seconds matching what is in F1. So you just need to add one more criteria for BH US Agent Web form. Rather than spanning dates across the columns on a single row, you should use a single date column. If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function. Your sheet structure could be improved to make analysis much more efficient. WorksheetFunction _ . g. Note that, we have changed the column 1 title from Month to Month Number so as to include the numbers inside the formula. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],. 0. In the example shown, the formula in H5 is: = AVERAGEIFS ( sales, group,F5, region,G5) where data is an Excel. As you can see clearly above, in Averageif the criteria range comes first, but in Averageifs it. In this example, this is the Score column in the table: =AVERAGEIFS(data[Score], The next two arguments specify the criteria. You require an OR condition since any one row could not be both 1 and 2. Here's a formula for that:Calculating Average by multiple columns. So how would I be able to take out the average of a table using dynamic columns and rows. 1. SUM will also get the average of G (where E="A") for every row of F=0 if the same. Using entire column references within an array formula is not at all a good idea. The procedure to apply the Excel AVERAGEIF function with the required date criterion is: Select cell G6, enter the formula =AVERAGEIF(B2:B11,G3,C2:C11), and press Enter. 97. Thread starter conlan; Start date Jul 23, 2014; C. Calculating Average by multiple columns. The syntax for the AVERAGEIF function is:What I want to do is have that formula reference the heading and then lookup the appropriate column in the data table to apply the averageif criteria to. Please help with a formula. Excel: averageif function for more than one cell. 2. (3) Divide Sum by Count to get Average. Change the Table Name in Line 2 to reflect the actual table name in your worksheet. Average for multiple criteria in. For example, one formula that is in use: =AVERAGEIFS (K:K,C:C, ">=01/01/2021", C:C, "<=1/31/2021") This formula works exactly the way I want, for the data specifically only in column K. The total amount for the groceries is $78. AverageIFs multiple columns when meets multiple criteria in one column. Also i noticed that the AverageIF doesn't skip blank cells in a multiple range it reported the #DIV/0! error, when i removed the averageif for the blank columns data was retrieved successfully but wrong values. Hot Network Questions A man wants to create a bomb shelter in a small cave on his land, but there's no visible water source, except a dripping wall. @TerryW column F is just a list of. AVERAGEIFS() requires that all the ranges must be the same size and shape. I. And each row is given a category. 1. Rajesh Sinha. You need the AVERAGEIFS formula that will take a column of values and multiple conditions on whether to include the value of a particular row in the calculation. 1. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. Returns the average of a data column depending on multiple criteria. For convenience all data is in an Excel Table named data in the range B5:. In this example, the two criter. Therefore the cells in the range are used to calculate the average. . I need the formula to find the value of "BF3" in every sheet from A3 down to A100 but i need the average value. For example, to find an average of sales in B3:B15 that are greater than $120, the formula is: =AVERAGEIF (B3:B15, ">120") To average based on another cell, you define all 3 arguments: range (cells to check against the condition), criteria (the condition) and average_range (cells to calculate). 0. Returns the average value for the items in the list, list. 1. A couple of ways to overcome this. Returns the average of a data column depending on multiple criteria. Use of AVERAGEIF Function to Find VLOOKUP AVERAGE in Excel. For example, one formula that is in use: =AVERAGEIFS (K:K,C:C, ">=01/01/2021", C:C, "<=1/31/2021") This formula works exactly the way I want, for the data specifically only in column K. "<1000") Select the range of cells you want to average. 2. So, one thing I'm thinking is that your lookup has no records or you have a mispelling. Trying to run an AVERAGEIFS function across a date variable AND either criteria 1 OR criteria 2 out of a second range. Originally Posted by daddylonglegs. I have no problem running this on a single column of data but get lost when trying to apply the average to multiple columns. The answer should be 4. The criterias would be by year (have multiple years), then by Pt type (in or out Patient),. I am trying to sum a total with multiple criteria (a name descriptor and a date). 0 Excel: averageif function for more than one cell. The article below explains two approaches. In your scenario, you could use the following formula to get the average: =AVERAGE (IF (A2:A200=1,B2:C200)) ENTERED AS AN ARRAY FORMULA using cntrl + shift + enter. The Convert Text to Columns Wizard –. I instead just used a set of nested IFS and will eventually look at changing these particular pivots to regular tables with index lookups to enable the actual data to be in multiple columns. column_references: The columns which you want to average. What you can do is use INDEX and some MATCH to make a dynamic function that will identify the cells to be averaged.