0

I don't really know how dates work in excel, and I've run into some errors. I am trying to change chart data based on a date that the user selects. My first question is: Would it be better to create a drop down list of unique dates from the data, or to let the user type in a date?

The next question is a bit complicated: For my chart data, I use a string of IFs and AVERAGEIFSs. For example, here is one of my functions for one set of data:

=IF(AND(Summary!I1 = "Clients", Summary!J1 = "Groups"), AVERAGEIF(Questions[ConversationQuestionID], 1, Questions[RespondMessage]), IF(AND(Summary!I1 = "Clients", Summary!J1 <> "Groups"), AVERAGEIFS(Questions[RespondMessage], Questions[ConversationQuestionID], 1, ClientNames[GroupName], Summary!J1), IF(AND(Summary!I1 <> "Clients", Summary!J1 = "Groups"), AVERAGEIFS(Questions[RespondMessage], Questions[ConversationQuestionID], 1, ClientNames[ClientName], Summary!I1), IF(AND(Summary!I1 <> "Clients", Summary!J1 <> "Groups"), AVERAGEIFS(Questions[RespondMessage], Questions[ConversationQuestionID], 1, ClientNames[ClientName], Summary!I1, ClientNames[GroupName], Summary!J1), " "))))

For something like this, is there a way to filter by range on the outside of the function? Or will I have to enter it in each AVERAGEIF?

Thank you, and please let me know if this question does not make sense.

EDIT: By filter, I mean to just extract data taht is within the date range. Below is a small example of my data:

Order Date    Order ID   Client     Store     Order Price
MM/dd/yyyy    1234       Client1    Store1     $xx.xx
MM/dd/yyyy    5678       Client6    Store4     $x.xx
MM/dd/yyyyy   9876       Client 3   Store2     $xxx.xx

I want the user (Sales team, executives, etc) to be able to enter a date range, and my chart will update based on the values within that range. So if they enter the dates 08/12/2017 and 12/01/2017, the chart would display information about orders from that time period.

I just want to know the best way of doing this.

  • It's not clear what you're asking. What do you mean by "filter by range on the outside of the function"? Please EDIT your question to show example data - what you have now, and what you are trying to get. You can upload a screenshot to Imgur.com and add the link in your question. – Bandersnatch Dec 19 '17 at 16:57
  • That helps. To answer your first question, I think drop down lists would allow you to avoid typos and invalid inputs. Regarding the second question, what are you calculating for the y-values of the graph, the average price for a particular store and client? – Bandersnatch Dec 19 '17 at 17:16
  • I agree with the list, but I can't seem to get that to work. As far as y-values, it's average price per client and store. I have two lists, one for client and one for store. The user can look at the overall average, or select a client and/or a store and see the average for that selection. – andrewbuikema Dec 19 '17 at 17:25
  • If you need to pick any one out of 10 or 50 values, its better use Drop Down list to save time and faster process and it also avoids wrong entry. Also, if you want to match and extract data within certain Date, then use Logical as well as Relational Operators. – Rajesh S Dec 20 '17 at 7:07
0

I think dates are better input by the user rather than a drop down list (unless you have very few dates from which dates are to be selected).

With respect to extracting the data you want to chart, put in a beginning date in a cell (call it G1) and the ending date in another cell (call it G2). And in another column (say column F in your table) where you will extract the data for the dates in the range, use this formula:

'=IF(AND(A2>=$G$1,A2<=$G$2),E2,"")

Where column A has the date, and column contains the data you are trying to extract.

Column F will now contain only the data that is in the date range from G1-G2 (inclusive); and you can now chart that data.

  • So i can just add this around my formula? So in this case, E2 would be the formula above? – andrewbuikema Dec 19 '17 at 18:07
  • F2 would contain the formula above and would be extracting the data from E2. – AlRacoon Dec 19 '17 at 18:12
  • Ah, I see. Thanks for the help! – andrewbuikema Dec 19 '17 at 18:23

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.