Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-17-2017, 07:20 AM
mreynolds1775 mreynolds1775 is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2013
Novice
Calculate the most recently entered data in columns?
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default Calculate the most recently entered data in columns?

Good morning all,



new to the forums. Thanks in advance for any help/ideas.

As an example, I enter sales for some 400 items on a daily basis. In order to maintain a chronological record of sales, I simply add a column with a header of today's date, and enter the sales for that day.

In a specified cell,(demonstrated in cell C8 of the attached worksheet), I would like to automatically calculate the average of the last three days of sales entered for the specified item (Demonstrated in cell A8). I'd also like to replicate a similar process for a seven day period, and thirty day period.

I'm open to all thoughts and suggestions!

Again, thanks in advance.

-Mike
Attached Files
File Type: xlsx Sales.xlsx (9.2 KB, 9 views)
Reply With Quote
  #2  
Old 09-17-2017, 10:18 AM
xor xor is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe like the attached:

Sales.xlsx
Reply With Quote
  #3  
Old 09-17-2017, 10:35 PM
xor xor is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Here is a version with only one volatile function (see cell C16).
(Helper cells in C10, C11 and C12).
Attached Files
File Type: xlsx Sales_2.xlsx (10.5 KB, 12 views)
Reply With Quote
  #4  
Old 09-18-2017, 05:43 AM
mreynolds1775 mreynolds1775 is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2013
Novice
Calculate the most recently entered data in columns?
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

after review, I think it may simplify the process to do something similar to attached... I can then find the desired sales rates via VLOOKUP later...

desired endstate would be to have a rolling average displayed in C:C of the last 3 entered data points for that row.

Thanks in advance for any help!
Attached Files
File Type: xlsx Sales2.xlsx (10.1 KB, 12 views)
Reply With Quote
  #5  
Old 09-18-2017, 06:12 AM
xor xor is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I am not sure what you want. Couldn't you use what I uploaded?

Maybe you like this one better?
Attached Files
File Type: xlsx Sales_3.xlsx (10.4 KB, 11 views)
Reply With Quote
  #6  
Old 09-18-2017, 07:01 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Calculate the most recently entered data in columns? Windows 7 64bit Calculate the most recently entered data in columns? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

To avoid volatile functions in C3
=(SUM($E3:$Z3)-SUM($E3:INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3)-3)))/3

and pull down as needed
Replace the 3 with seven for D3
Adapt ranges as needed
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 09-18-2017, 07:05 AM
mreynolds1775 mreynolds1775 is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2013
Novice
Calculate the most recently entered data in columns?
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
Maybe you like this one better?
haha...no idea what you did, but that appears to work great!

Can you explain to me what "RC" is for?

=AVERAGE(OFFSET(INDIRECT("RC"&COUNT($E3:$ZZ3)+4,0) ,,,,-3))
Reply With Quote
  #8  
Old 09-18-2017, 08:36 AM
xor xor is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Try to search for R1C1 style reference.
Reply With Quote
  #9  
Old 09-18-2017, 10:07 AM
mreynolds1775 mreynolds1775 is offline Calculate the most recently entered data in columns? Windows 10 Calculate the most recently entered data in columns? Office 2013
Novice
Calculate the most recently entered data in columns?
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
To avoid volatile functions in C3
=(SUM($E3:$Z3)-SUM($E3:INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3)-3)))/3

and pull down as needed
Replace the 3 with seven for D3
Adapt ranges as needed

Peco,

This works! is there any relevance behind the 9.99E+307 part?
Reply With Quote
  #10  
Old 09-18-2017, 11:07 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Calculate the most recently entered data in columns? Windows 7 64bit Calculate the most recently entered data in columns? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Have a look here to understand this technique http://www.xldynamic.com/source/xld.LastValue.html
Be aware that OFFSET and INDIRECT are volatile functions
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #11  
Old 09-19-2017, 11:28 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Calculate the most recently entered data in columns? Windows 7 64bit Calculate the most recently entered data in columns? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

As your following question covers a different topic it's best to start a new post eventually adding a link to this one. I did it for you this time
https://www.msofficeforums.com/excel...le-ranges.html
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Last edited by Pecoflyer; 09-19-2017 at 11:29 PM. Reason: Add link
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate the most recently entered data in columns? Conditional Formatting- Only format cells once data is entered chart3 Excel Programming 1 12-15-2015 11:26 AM
Calculate the most recently entered data in columns? Calculate amount of entered fields sp1d3r69 Excel 3 01-26-2015 07:35 AM
calculate date if date entered in cell, do nothing if blank ConfuddledOne Excel 3 11-07-2014 09:37 AM
Calculate the most recently entered data in columns? Compare columns + calculate difference Inatic Excel 1 01-27-2013 08:00 AM
Using data entered in prompt to search database table BluRay Word 1 03-25-2011 01:47 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft