How to average data from one Excel tab (based on list input) in another tab

Question

Hello! In tab 1, I collect data on how long it takes my institution to review and approve a research submission (see below). You'll notice in the first column (Submission type), the user picks their response from a list.

I would like to aggregate and average the data in the "Approval time (in days)" column in a new, sperate tab organized by submission type (see below). In real time, I would like the "Approval time (in days)" in tab 1 to populate in tab 2 (below) depending on what kind of submission it is (e.g., Initial, Amendment, Admin Amendment, etc.). I want tab 2 (below) to continuously update the average time it takes to approve each submission as more data is entered into tab 1 (above, "Approval time (in days)").

Thank you in advance for your help!

Answer 1

Your drop down list has an linear array of values that your users can choose from by clicking on the arrow. Assume this list is vertical and has the name Types.

Your user data is in an array of n rows by 4 columns. Assume this array has the name User_Data.

You can use the INDEX function to select elements of an array and you can use the AVERAGEIF function to average just those time values that match a particular type. Create a two column array with the same number of rows as Types:

• The left cell contains =INDEX(Types,x,1) where x is the number of some row in Types.
• The right cell contains =AVERAGEIF(INDEX(User_Data,0,1),left_cell_coord,INDEX(User_Data,0,4))

Answer 2

Hi,

In sheet2, enter this formula

=groupby('sheet1'!A2:A4,'sheet1'!D2:D4,average)

Hope this helps.