Forum Discussion
Daily Calculating
Yes I need the most recent day's data. The report is emailed to the company every day. The report or spreadsheet I posted here was one that was actually emailed to the company on the 7th of January.
OK. Here's a revision that shows the count for the prior day. To make that completely clear, I also revised the adjacent cell so it incorporates the date associated with the data.
May I persist in asking some design questions of you. I do realize you may not be in a position to change things, if "this is always the way we've done it," so feel free to take my wondering with a grain or two of salt. During my career I was able to take existing processes on more than one occasion and make them more effective by making a few minor changes--often in exactly the kind of "established reporting format" that you have here.
- So, first question: in my experience, management often wants not only to look at one day's worth of information, but also at trends: are the numbers increasing, decreasing, fluctuating intermittently, etc. You could help in that kind of trend analysis by incorporating a new column in this that shows each day's ending stock for the month. It seems to me as, granted, an outsider, that ONLY doing the total for yesterday is not anywhere near as helpful as displaying the history. The question: are you able to propose a change like this in your corporate culture? Or does this form fulfill some sort of legal/governmental requirement and therefore require all sorts of levels of approval for any change, no matter how warranted?
- Second, and somewhat related to the prior question, why do you start over again each month? Why not retain the history on a continuous basis? It would be fairly easy to still display only, say, the most recent 30 days, but the bigger picture would be there if somebody wanted it.
- Third, a minor question: do you email this out as a "live" spreadsheet? Using the formula I've just given you would be problematic if that is the case and a user doesn't look at it for a day or two....because it would have updated itself to be showing data that didn't exist at the time it was mailed out. So I hope you email out a PDF that freezes the data for the day. But this question also relates to the question about showing all of the daily totals in a new column, so no matter when a given view looks at it, they'd see the actual totals for each day prior to the generation of that given report.
The current spreadsheet (attached again below) uses this formula in X39:
=INDEX(O5:O36,MATCH(TODAY()-1,A5:A36,0))+INDEX(R5:R36,MATCH(TODAY()-1,A5:A36,0))
That formula does give yesterday's (the prior day's) total for columns O and R
You had asked that it do that--give the prior day's (most recent day) data rather than the current, because presumably it's actually mailed out at the end of the day or first thing in the morning. Here's your message stating that requesr:
Yes I need the most recent day's data. The report is emailed to the company every day. The report or spreadsheet I posted here was one that was actually emailed to the company on the 7th of January.
However, I also think it's important that you pay attention to this other question that I asked, one of the ones you say you don't want to deal with. It's very important. Because by entering a formula such as the one above into cell X39, you ARE making a major change and you appear to be unaware of potential consequences. Here's the question again.
Third, a minor question: do you email this out as a "live" spreadsheet? Using the formula I've just given you would be problematic if that is the case and a user doesn't look at it for a day or two....because it would have updated itself to be showing data that didn't exist at the time it was mailed out. So I hope you email out a PDF that freezes the data for the day. But this question also relates to the question about showing all of the daily totals in a new column, so no matter when a given view looks at it, they'd see the actual totals for each day prior to the generation of that given report.
I said it was a "minor question" when I wrote it, but I'm going to reclassify it as major. IF you are sending this out as a live spreadsheet, one with all formulas functioning, then anybody who looks at it on a day later than the day when you send it out, or yet again a day later---they're NOT going to be seeing data in cell X39. It will always be adding up column O and R data from whatever is the day prior to the day they're looking at it. So it will read 0 if they are looking at it two days or more after the last day data were entered. Whereas, if you send it out as a PDF (which is easy to do, but I'm not going to go into that here), then it's "frozen" and will always be accurate for the day shown.
You're to be commended for looking for a solution to make your job easier; I'm sorry to say that it doesn't look like you actually want to make what you do more effective, just easier for you. But unless you give more thought to what does happen down the road, you might be creating more problems for yourself than you realize. You've made pretty clear that you "don't care" about what happens with this once you send it out. Given that, given your lack of interest in thinking through the whole process of which you are a part, I think that it probably makes sense for you to keep doing it as you've been doing it.
In other words:
- Use this formula IF and ONLY IF you send out your report as a PDF.
- Otherwise, you should enter an updated row reference as you've been doing. It will remain stable with the result for that day, no matter when somebody else up the management chain looks at it.
In your present spreadsheet, X39 would display 22.14 and assume R is zero (0). Yours does not do that. Is there a formula where X39 displays the sum of barrels O + R each day? As for the other points, I am not comfortable discussing any of that. I just want to know if a formula exists without having to change anything on the spreadsheet.
You wrote: I just wanted to know if there was a formula that could return a daily amount as I have explained. I do not know what happens to this report once it leaves my office, or care.
You should care, first of all, at least if you have any idea of building a career. It's paying attention to precisely this kind of little detail that, over the long run, can give you a reputation as someone who, well, who cares, and who makes a difference.
I did not realize this would be so difficult. I did ask on my first post if this was possible. And I am beginning to think it isn't.
Wait a minute: didn't we just do, earlier, what you asked?!! I just asked some follow-up questions to see if the whole process could be improved. I think you may be selling yourself short.
First of all, this is way over my head. I just wanted to know if there was a formula that could return a daily amount as I have explained. I do not know what happens to this report once it leaves my office, or care. I am only responsible for entering the data a gauger has put on his report. That's all. I did not realize this would be so difficult. I did ask on my first post if this was possible. And I am beginning to think it isn't.
I do thank you for all your interest in trying though.