Microsoft Office Forums Running Average
 Register FAQ Search Today's Posts Mark Forums Read

#1
02-06-2019, 01:46 PM
 JARVAN Windows 10 Office 2016 Novice Join Date: Feb 2019 Posts: 1
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.
#2
02-06-2019, 09:39 PM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,029

Maybe like this
Attached Files
 Average_yes.xlsx (10.4 KB, 3 views)
#3
02-07-2019, 12:28 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,396

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.
#4
02-07-2019, 12:32 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 463

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.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post FTL Excel 1 08-09-2016 06:56 AM jennamae Excel 4 01-17-2014 05:10 AM joflow21 Excel 4 10-21-2013 06:57 AM nfphilpot Excel 3 11-24-2010 02:19 PM speedycorn1 Excel 1 10-30-2010 07:54 PM

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

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top