Microsoft Excel Tutorials for Analyst

Excel formula for Beginners – How to Average last 5 values in Excel

(Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Average last 5 values in Excel.   Excel formula for Beginners – How to Average last 5 values in Excel Generic formula =AVERAGE(OFFSET(A1,COUNT(A:A),0,-N)) Explanation To average the last 5 data points, you can use theĀ AVERAGE …

Excel formula for Beginners – How to Average if criteria not blank in Excel

  (Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Average if criteria not blank in Excel.   Excel formula for Beginners – How to Average if criteria not blank in Excel Generic formula =AVERAGEIFS(range1,range2,”<>”) Explanation To calculate an average, excluding blank values in …

Excel formula for Beginners – How to Average by month in Excel

(Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Average by month in Excel.   Excel formula for Beginners – How to Average by month in Excel Generic formula =AVERAGEIFS(values,dates,”>=”&A1,dates,”<=”&EOMONTH(A1)) Explanation To average by month, you can use a formula based on the AVERAGEIFS …

Excel formula for Beginners – How to Average and ignore errors in Excel

  (Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Average and ignore errors in Excel.   Excel formula for Beginners – How to Average and ignore errors in Excel Generic formula =AVERAGEIF(values,”>=0″) Explanation To average a list of values, ignoring any errors that …

Excel formula for Beginners – How to SUMPRODUCT with IF in Excel

  (Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to SUMPRODUCT with IF in Excel.   Excel formula for Beginners – How to SUMPRODUCT with IF in Excel Generic formula =SUMPRODUCT(expression,range) Explanation To filter results of SUMPRODUCT with specific criteria, you can apply simple …

Excel formula for Beginners – How to SUMIFS with multiple criteria and OR logic in Excel

  (Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to SUMIFS with multiple criteria and OR logic in Excel.   Excel formula for Beginners – How to SUMIFS with multiple criteria and OR logic in Excel Generic formula =SUM(SUMIFS(sum_range,criteria_range,{“red”,”blue”})) Explanation To sum based on …

Excel formula for Beginners – How to Sum top n values with criteria in Excel

(Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Sum top n values with criteria in Excel.   Excel formula for Beginners – How to Sum top n values with criteria in Excel Generic formula =SUMPRODUCT(LARGE((range=criteria)*(values),{1,2,3,N})) Explanation To sum the top n values in …

Excel formula for Beginners – How to Sum top n values in Excel

(Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Sum top n values in Excel.   Excel formula for Beginners – How to Sum top n values in Excel Generic formula =SUMPRODUCT(LARGE(rng,{1,2,N})) Explanation To sum the top values in a range, you can use …

Excel formula for Beginners – How to Sum matching columns and rows in Excel

  (Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Sum matching columns and rows in Excel.   Excel formula for Beginners – How to Sum matching columns and rows in Excel Generic formula =SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2)) Explanation To sum values in matching columns and rows, …

Excel formula for Beginners – How to Sum matching columns in Excel

(Excel examples for Beginners) In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Sum matching columns in Excel.   Excel formula for Beginners – How to Sum matching columns in Excel Generic formula =SUMPRODUCT(data*(headers=A1)) Explanation To sum values in columns by matching matching column headers, you can use …