1. M

    Excel Formula for Avg Tenure with a twist

    Hi I am trying to figure out a formula that would give me the average tenure per year of the Active employees for that year. I have a list of the employees with start and end dates and their Departments, and their tenure for each year they worked. Here are the two lists: What I need is to get...
  2. M

    Sumproduct & Indirect returning 0 or incorrect

    Hi all, Hoping someone can help me with this formula: {=SUMPRODUCT(INDIRECT("'"&"2010_Sold.xlsx"&"'!"&{"January","February","March"}&"["&"Current Price"&"]"))} Which is returning 0 Or, =SUMPRODUCT(SUM(INDIRECT("'"&"2010_Sold.xlsx"&"'!"&{"January","February","March"}&"["&"Current Price"&"]")))...
  3. D

    The average turnover for each country.

    Hey there Could you please help write a formula to find the average turnover of customers from each country I tried =AVERAGEIF(A2:A91061,A2,B2:B65759) but it shows DIV/0. My goal is to find average turnover for 'LV' etc. Thank you
  4. S

    VBA for averages?

    Hellooo Thanks so much in advance I have employees on column M I have a bunch of different items they worked on on column E I have the time they spent on column K I need a table made in a new sheet, that puts each employee and the average time they spent working on each item. So I guess...
  5. M

    AVERAGEIF(column,"<>0) giving error #DIV/0!

    I have a table with 20 rows and only 6 rows have data in them. I'm trying to get the average of only the rows containing data in a column. Can anyone help me figure out why I'm getting an error on this formula? Example: Sam 5 Jess 2 Dean 4 Michael 4
  6. S

    Excel equation for dates past a certain date

    Help please if you can, I cant seem to find anywhere else. Im trying to find the equation for 2 things, 1. I have a chart I'm making and trying to calculate if something is past due 30 days, 10days, and 5 days is there an equation to find out if the dates I entered have passed...
  7. M

    Calculate Tenure with a Twist

    So I have two sheets. One sheet has my employee list with historic Start and End Dates. My other sheet is the summary of that list by Month. For example, lets say I have 10 Employees on my Employee sheet that all have Start and End dates, on my second sheet I have to summarize that info by...
  8. E

    Calculating average- one cell missing

    Hey I have 49 cells which represent profits for different quorters, I need to find their average. It is said it should be less than 400000 per quorter. Plus, the 49th cell is blank, I need to find its value without affecting the average. Please help me?
  9. D

    Forumlas including average

    Hi, I have a spreadsheet that I am using to track audits completed. At the bottom, it adds up and has to be in a certain range to turn red/amber/green over the 10 audits. I was wondering how it is best to add a column in to work out if we only did 5 audits, but so that it still fits into the...
  10. P

    #Spill! Error when trying to find average of 2 letter grades

    Good afternoon; I'm trying to get 2 cells, not side-by-side, to average 2 letter grades in a 3rd cell. All I am getting is a #Spill! error. Also, if I do enter grades into the cells, it subs the value in H4 into K4, which is not helpful as it is not finding the average. In my screenshot, it...
  11. P

    AVCO formula Guidance

    Hello ! i have created a excel for the shares i buy. i want to use formula for AVCO (Average cost method) method. I am attaching file for reference please if some one can help me with the formula. ABCDEFGHI1#DateStatusC.Note #Scrip NameQtyT.RateT.AmountC.O.G20110/09/21Buy9794Sun...
  12. T

    "Average(If" not working

    I am currently averaging values in one table based off of their rankings in a separate table. I am doing so for each combination of the potential rankings. The formula below works for this purpose. =AVERAGE(IF((Table2[ranking]=B2)+(Table2[ranking]=C2)+(Table2[ranking]=D2),Table3[Value]))...
  13. L

    SUMPRODUCT (with criteria) first N even rows in a column and then AVERAGE

    Hi, I’m trying to average the total minutes of the first N -- in this case, 3 in lieu of N -- movies watched by a client. Please see my sheet: docs.google.com/spreadsheets/d/1oTwnB5u7y4KqAwamvjsEats3JiTPpYhEkOqUvOzEkDE In column A, you can see the extracted numbers from the minutes texts so...
  14. T

    sumproduct with custom ranges

    Hi all, hope things are going well. I am trying to calculate a weighted average using sumproduct but have a little problem: I can't just use normal ranges in the sumproduct and sum formulas. Assume row 1 has my values (in yellow) and row 2 has my weights (in green), then normally I would...
  15. T

    Calculating average of weighted grades in ever-expanding table

    I am creating a gradebook which has users add new evaluations through a VBA userform. Each new evaluation is added to the next empty column in the worksheet. Two of the data entered are the Category and the Points. In the sheet Settings!K2:L17, the Category code (column K) is associated with...
  16. T

    Code VBA average function when adding data with userform

    I have a userform that takes input from the user and adds it to the first five rows in the next available column of the worksheet. In row 6, I want a formula like: =IFERROR(AVERAGE(D7:D32),"") but the cell reference, of course, has to be for the current column (which changes with each instance...
  17. L

    Average if there is a count above 2

    Hi all, Hoping for some help with a formula please. I have an averageifs formula that works fine looking for an average with two criteria. My issue is I only want this function to work if a name appears more than 2 times for everything else I just want it to be blank. I have uploaded an...
  18. M

    Find average when other column reads "true"

    Using Office 365 on Windows 10, When column A is TRUE, I want to find the Average of column B. I know I use could Filter, but I want to create a formula instead. Can someone help...
  19. D

    AVERAGE from different cells ignoring 0

    Hi there, I want to use avergae function to calculate average from different cells like: (AB14,AB51,AB88,AB125,AB162,AB199) but ignoring 0, #DIV/0!. What formula I have to use to calculate average from different cells? Any help will be very appreciated. Thank you.
  20. B

    Average, Large, Date Range question

    Hello, I am calculating a weekly score for number of steps performed. I'm taking the top 5 days out of the 7 days in a week using this: =AVERAGE(LARGE($B$2:$B$8,{1,2,3,4,5})) The NO HOLIDAYS table in my spreadsheet is working perfectly. The HOLIDAYS table is just a copy of that table which...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back