averageifs

  1. B

    Use AVERAGEIFS based on dynamic criteria

    I'm trying to average a set of cells based on multiple criteria (hence the AVERAGEIFS), but I want this average to change as more data is added. There may be a very simple fix for this, but I can't figure it out for the life of me. Below is a sample of the table followed by an explanation of...
  2. willow1985

    Help with a complex AVERAGEIFS formula

    I have the below SUMIF formula that works in my spreadsheet that I would like to modify to AVERAGEIFS based on all of the same criteria minus the column to Average. I tried to sub out SUMIFS with AVERAGEIFS but all I get is a #DIV/0! error. Not sure why I am getting the error. I have also tried...
  3. P

    AVERAGEIFS not giving expected value

    Hi, I am trying to do an AVERAGEIFS formula but the provided answer is not correct. Current formula I have is =AVERAGEIFS(D:D, B:B, ">="&A2, C:C, "<="&A2) I have a long list of sequential dates in Column A, column B is the date a device was sent (or today if not sent), column C the date it was...
  4. W

    Average individual cell values between two columns and count the number over X

    I have two columns in Excel: <tbody> Column A Column B Res Qty Total Hrs 2 66 1 30 Res over 32: 10 3 93 3 102 5 180 5 155 ... row #57 ... row #57 </tbody> (66/2 = 33, 30/1 = 30, 93/3 = 31, 102/3 = 34, 180/5 = 36, 155/5 = 31) > 32 count = 10 Question: I want...
  5. M

    Average ifs for filtered data (visible data)

    Hi Everyone, This is my first post here! I am trying to play around with data on a trading strategy I have been working on. I have attached my problem into a new spreadsheet. The real original spreadsheet will be too large to attach here. What I need: I want to be able to filter data e.g...
  6. G

    Dynamic average_range by column text in AVERAGEIFS formula

    Hi, I am trying to obtain an average from multiple criteria using AVERAGEIFS function. The issue is I want to be able to specify the average range in a dynamic way based on column header name. I used index/match to specify the column name using header name for AVERAGEIF and it works like a...
  7. R

    Averageifs with multiple criteria

    The formula listed below is only averaging items for "5B" and does not include items from "LI". I have tried everything I know to get it to produce the desired results. Any ideas? =IFERROR(AVERAGEIFS('WORK ORDERS APPROVED DATA'!$N$7:$N$3500,'WORK ORDERS APPROVED DATA'!$G$7:$G$3500,P11,'WORK...
  8. M

    Weighted salary by salary band

    Hi I'd like to calculated the average salary for employees based on salary bands. The table below shows salary bands and the number of employees in each band. What's the best way of working out the average salary by salary band? I thought about taking the mid-point of the range then...
  9. C

    AverageIfS Function

    I am having trouble with this AverageIfS function. It seems to only calculate the average of the "A" criteria and is not including "UC" & "US" criteria. The result in this case I receive is 76 and it should be 62.6. Thank you for your help. FORMULA: {=AVERAGEIFS('GSMLS Data'!B:B,'GSMLS...
  10. M

    averageifs

    Hi I need to use an averageifs formula to get the average UNIT price of Item SKUs greater than 30000 in the data below and ensure that Quantities of 0 are excluded. I've tried to use the following formula =AVERAGEIFS($B$5:$D$24,$B$5:$B$24,">30000",$C$5:$C$24,"<>0") but get a value error...
  11. I

    AverageifS on a range containing Div/Value/Error...

    Hi all, <tbody> 10 Correct Yes 5 Correct Yes #Div/0! Correct 15 #Value! Correct </tbody> I simplified my problem in the above table. I want to use the averageifS formula on the first column using criteria: Correct and Yes. I know I can use a simple criteria: ">0". Is there a way...
  12. P

    AVERAGEIFS with multiple criteria on one range

    Hi all, I have an AVERAGEIFS statement that I need to take two different criteria on one range. when I try it, I get the good ol' #DIV/0! error. The statement is as follows: =AVERAGEIFS($B:$B;$C:$C;"MRO";$D:$D;"<25";$E:$E;"jakarta";$E:$E;"bali";$R:$R;">=01/01/2018";$R:$R;"<=31/01/2018") It...
  13. J

    Averageifs #div/0

    <a href="https://ibb.co/iG32we"><img src="https://preview.ibb.co/m6oD2K/Capture.png" alt="Capture" border="0"></a> What am I doing wrong?
  14. D

    Averageifs

    Hi All I am looking for help building an averageifs formula. The formula needs to average between 2 dates which will be across the top of the data, and take in to account a third variable which will be to the left of the formula. This is the formula that I currently have...
  15. E

    Issue with AVERAGEIFS function

    I have weather data for a year that I am trying to average based on the hour. My averageifs function keeps giving me a #DIV/0! error and I can't figure out why. Any help is appreciated. Equation: =AVERAGEIFS($C$2:$C$11085,$I$2:$I$11085,"="&R2,$K$2:$K$11085,"<="&S1,$K$2:$K$11085,">"&R1)...
  16. R

    Average Ifs not working

    Hi I was trying to use averageifs to average numbers I have in row 1 from A1:AV1 by a corresponding criteria number I have in row 2 from A2:AV2. So if the numbers in the cells in row 1 have a 1 below in the criteria row 2 they are averaged in row 3 and the same for all the other criteria...
  17. K

    AVERAGE using multiple criteria from multiple columns

    Hello I need to calculate the average value of a column contain temperature values (column H). The difficult part is that I want to average only specific cells of this column, that meet certain criteria. More specifically, I want to average temperature values from column H, only if the wind...
  18. H

    =sumproduct(averageifs(...

    Hi, I regularly use sumproduct(countifs( or sumproduct(sumifs where one of the criteria is a range and it works great.. However I am attempting to do this with averageifs and it doesnt work.. is there an alternative? or something I am missing something? example of what im trying to make...
  19. C

    AVERAGEIFS function - ignore blanks and add qualifying "if" statement

    I have this formula that works to be a nonblank average of a column J that matches to a specific answer in column B (A21). Column B has non-numerical values and Column J is the data I'm averaging. =AVERAGEIF(B3:B20,A3,J3:J20) When I try to expand this to eliminate "0's" from the averages, it...
  20. J

    Rolling Average

    Hello all, I have posted here before and you all have been really great in helping me out. I have a spreadsheet dashboard that I am trying to tweak. in one of the cells I have the following formula: =AVERAGEIFS('DAILY VALUES'!I2:I5200,'DAILY VALUES'!A2:A5200,">="&MAX(IF('DAILY...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top