+ Reply to Thread
Results 1 to 8 of 8

Dynamic form in Excel with formulas?

  1. #1
    Registered User
    Join Date
    03-05-2018
    Location
    Washington, DC
    MS-Off Ver
    10 Pro
    Posts
    4

    Dynamic form in Excel with formulas?

    I've got a REAL puzzler for you experts out there!
    I'm pretty sure I've seen people use Excel to create a dynamic form. I've been trying different formulas for days to no avail. This is regarding schools across the US. I've removed the identifying information to protect the privacy of those schools.

    Attachment 564346

    I've used Data Validation to only allow data points present for each column on the Main tab of the workbook. (e.g., Year End in Form!K5 only permits 2013, 2014, 2015, 2016, 2017, or 2018.

    Now, people can choose what they'd like to specify from Form!E5 through Form!K5.

    I'd like to write a formula that considers their choices, and returns: a) a median for those values in the array (which are found on the Main tab), and b) an Average for those values in the array (which are found on the Main tab).

    Attachment 564349

    Example:
    form2.PNG

    Here's my working thought process:
    1. The above photo means I don't care what City the school is in, as well as the Type of school, and whether or not they are a Member of our program. I DO want to count ONLY schools in the Southeast region that are between 201-300 in Size, and only for the 2018 school Year.
    2. I DON'T want to consider any school that has "0" (zero) in column H of the Main tab.
    3. I'd like the Median and Average for these schools.
    4. The following formulas are grossly wrong, but it's all I can think up right now.
    =MEDIAN(IFS(Main!$D:$D=E5,Main!$E:$E=F5,Main!$F:$F=G5,Main!$G:$G=H5,Main!$H:$H=I5,Main!$I:$I=J5,Main!$J:$J=K5, Main!K:K))
    =AVERAGEIFS(Main!K:K,Main!$D:$D,E5,Main!$E:$E,F5, Main!$F:$F,G5, Main!$G:$G,H5,Main!$H:$H,I5,Main!$I:$I,J5,Main!$J:$J,K5)

    5. I know I should add "Main!K:K<>0" in order to preclude the entries that have zero entered into Main!K, but I don't know how.

    I'd love any assistance, please! If I can get this, it'll save me weeks of work.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic form in Excel with formulas?

    Hi and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-05-2018
    Location
    Washington, DC
    MS-Off Ver
    10 Pro
    Posts
    4

    Question Re: Dynamic form in Excel with formulas?

    Thank you for the guidance on making my question clearer. I've selected a sample from my worksheet.
    Using that, I've decided to examine Schools from any City or State, as long as they are in the "Southwest" Region,
    of the Type, "Day," and only records from the Year "2016." You can see those variables selected in columns X-AD/row 6.

    I calculated the averages and medians for this selection by searching manually for schools that meet those criteria.
    As this data-set is HUGE, I'd like help creating Average and Median formulas that consider the user's selections from drop-down menus in columns X-AD/row 6, ignoring any entry that has a 0 (zero) in column K, and ignoring blank in columns X-AD/row 6, where the user does not wish to specify a particular variable.
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic form in Excel with formulas?

    The whole thing could be simplified immensely if your data was in a regular normalised 2 dimensional table.

    In your case that would mean the current columns A:G with column H being the field containing the DC, SS, AA ...etc values, and column I containing the numeric values.

    The database would contain more rows than the current table (but fewer columns). e.g. A2:G2 would be repeated 12 times with columns H & I being different values.

    You can then use a Pivot Table and Slicers to do all your analysis in a far more efficient and elegant manner.

  5. #5
    Registered User
    Join Date
    03-05-2018
    Location
    Washington, DC
    MS-Off Ver
    10 Pro
    Posts
    4

    Re: Dynamic form in Excel with formulas?

    I'll put in the work to reform it according to your suggestion, but without having made that change yet, there are already more than 11,000 rows. Do you think your suggestion pose any challenges because of the volume of data?

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Dynamic form in Excel with formulas?

    Additionally column D data needs to be cleaned up/standardized. Several "Day" entries are followed by multiple spaces.
    Last edited by FlameRetired; 03-07-2018 at 12:09 AM.
    Dave

  7. #7
    Registered User
    Join Date
    03-05-2018
    Location
    Washington, DC
    MS-Off Ver
    10 Pro
    Posts
    4

    Re: Dynamic form in Excel with formulas?

    Thank you for the advice. I've normalized the data, and am now happily playing around with pivot tables.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Dynamic form in Excel with formulas?

    Good deal!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-30-2017, 08:57 AM
  2. [SOLVED] Dynamic calendar in Excel (formulas only) multiple-day projects
    By rewdsaDSAdas in forum Excel General
    Replies: 7
    Last Post: 06-29-2017, 08:23 PM
  3. Convert Excel formulas containing functions to a simple form
    By syedalisajjad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2014, 11:39 AM
  4. [SOLVED] Dynamic X-Axis in excel chart using form control
    By ejdrouil in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-16-2013, 09:56 AM
  5. User form with formulas (excel 2007)
    By Novis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2009, 03:08 PM
  6. Dynamic Form in Excel
    By lisa6538 in forum Excel General
    Replies: 15
    Last Post: 01-11-2007, 03:57 PM
  7. Excel should let me use formulas that refer to other cells w/ form
    By Chenopod in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-06-2005, 03:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1