Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-06-2019, 01:46 PM
JARVAN JARVAN is offline Windows 10 Office 2016
Novice
 
Join Date: Feb 2019
Posts: 1
JARVAN is on a distinguished road
Question Running Average

Good afternoon. I am relatively inexperienced with Excel, but am tasked to create a spreadsheet in which the goal is to have running averages for columns. I am running Excel 2016 on Windows 10.

The answer choices for the column are "yes" "no" and I want a running average of the "yes" answers.



I found some tutorials on using COUNTIF/COUNTA. But, all the examples show using a defined data range and I this is an open-ended column, hence my need for a running average that would recalculate when a new cell is populated.

I hope this makes sense.

Is someone able to assist with a formula that I can use for this?

Much appreciated.
Reply With Quote
  #2  
Old 02-06-2019, 09:39 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 962
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Maybe like this
Attached Files
File Type: xlsx Average_yes.xlsx (10.4 KB, 3 views)
Reply With Quote
  #3  
Old 02-07-2019, 12:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,294
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

To use a dynamic range, and based on Xor's example ( as you did not provide a sample hseet), select B2:C21 -Click - Insert - click Table - click " My table has headers " (or not) - Ok

Realign Xor's result range
Now when you enter more data the formula will adapt automatically
Beware that when you enter a "yes" in the first column and nothing in the second, the function will recalculate based on a zero value for col C
To avoid this use =IF(OR(B3="",C3=""),"",AVERAGEIF($B$3:B3,"yes",$C$ 3:C3))
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #4  
Old 02-07-2019, 12:32 AM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 386
ArviLaanemets will become famous soon enough
Default

Do you have a column with some numeric values, and another column with yes/no? And you want an average of numeric values with "yes" in another column 'until' current record?

The problem is, how do you define this 'until'! When it is simply position of entry in table, then any sorting you are making will recalculate this average, and it will be different compared with average the same entry did have before.

When you have a column with dates (without hours) in your table, you can base your running average on those dates. But there is no way to decide which entry was earlier for cases with several entries in same date - you will have same running average for all entries with 'yes' on this date.

The best case will be, when you have some unique ordered identifier in your table, like entry registering date and time, or entry number - entered manually or through VBA code. Then you can calculate a real running average for every entry - and this will remain same unless you enter forcibly some earlier entry.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate average age FTL Excel 1 08-09-2016 06:56 AM
Average if and sumif jennamae Excel 4 01-17-2014 05:10 AM
Average for the month joflow21 Excel 4 10-21-2013 06:57 AM
'AVERAGE' Formula nfphilpot Excel 3 11-24-2010 02:19 PM
Average of many rows speedycorn1 Excel 1 10-30-2010 07:54 PM


All times are GMT -7. The time now is 05:46 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft