#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
|
#3
|
|||
|
|||
Here is a version with only one volatile function (see cell C16).
(Helper cells in C10, C11 and C12). |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
I am not sure what you want. Couldn't you use what I uploaded?
Maybe you like this one better? |
#6
|
||||
|
||||
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 |
#7
|
|||
|
|||
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)) |
#8
|
|||
|
|||
Try to search for R1C1 style reference.
|
#9
|
|||
|
|||
Quote:
Peco, This works! is there any relevance behind the 9.99E+307 part? |
#10
|
||||
|
||||
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 |
#11
|
||||
|
||||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Formatting- Only format cells once data is entered | chart3 | Excel Programming | 1 | 12-15-2015 11:26 AM |
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 |
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 |