+ Reply to Thread
Results 1 to 28 of 28

Switch between formulas using drop down list

  1. #1
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Switch between formulas using drop down list

    The attached workbook has three tables. The first two have data computed using formulas while the third table is empty. Calculations in the first table are based on all matches played while calculations in the second table are based on the last five matches.

    There is also a drop down menu in cell AT71 with two options i.e. All Matches and Last 5.

    REQUIRED:
    I want to get rid of the first two tables and have only one table. I would like a situation where when the cell AT71 reads “All Matches, the third table to apply formulas in the first table. And when cell AT71 reads “Last 5”, the third table to pick formulas in the second table. In the end, If I can find a way to achieve this, I will only be having one table (the third one) so I will delete the first two.


    EPL.xlsx
    OnditiGK

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    Try this:

    in AZ1

    =IF(AT71="All Matches",99,5)

    Use the SECOND table i.e. the one which uses the INTERVAL.

    Test by comparing the 2 existing tables; BURNLEY have played more than 5 matches so this is one test team to look at.

  3. #3
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Thank you for your response john. The suggestion will not work because what I intend to have is only one table not all three so that for example:

    In BC70:

    If AT71=All Matches, the formula in BC6 i.e. =IF($AY$6:$AY$29="","",SUMPRODUCT(($V$6:$V$2000=$AY6)*($W$6:$W$2000>$X$6:$X$2000))) be applied without referencing cell BC6

    And if AT71=Last Five, the formula in BC42 i.e. =SUMPRODUCT(($V$6:$V$2000=$AY6)*(ROW($V$6:$V$2000)>=MAX(ROW($V$6),LARGE(INDEX((($V$6:$V$2000=$AY6))*ROW($V$6:$V$2000),0),$AZ$1)))*($Z$6:$Z$2000="W")) be applied.

    Note: Ignore columns AZ to BB. Use attachement below.

    EPL.xlsx
    Last edited by gko_87; 04-20-2017 at 05:56 AM. Reason: Column for Average goals conceded was not filled in previous attachment

  4. #4
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    For example in cell BC70:

    If AT71=All Matches, "IF($AY$6:$AY$29="","",SUMPRODUCT(($V$6:$V$2000=$AY6)*($W$6:$W$2000>$X$6:$X$2000)))"

    If AT71=Last 5, "SUMPRODUCT(($V$6:$V$2000=$AY6)* ROW($V$6:$V$2000)>=MAX(ROW($V$6),LARGE(INDEX((($V$6:$V$2000=$AY6))*ROW($V$6:$V$2000),0),$AZ$1)))*($Z$6:$Z$2000="W"))"

    How can the these formulas be combined in cell BC70?

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    Did you actually try it in table 2??? the "99" in formula should ensure it includes ALL matches: it certainly worked for Burnley where thre results in table 1 (all matches) matched with table 2 interval of 99).

    So you remove table 1 and table 3.

    why have the formula in AZ70????

    in AZ1

    =IF(AT71="All Matches",99,5)

  6. #6
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    What does this formula do John? When I change AT71 to all Matches, the results I am getting in the 2nd table are not similar to those in the first table.

  7. #7
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    I need a situation where I get the results of table 1 when I change AT71 to All Matches, and the results of the second table when I change AT71 to Last 5.

  8. #8
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Check Columns BO to CL when AT71=All Matches.

    And sometimes teams may not have played equal number of matches.
    Last edited by gko_87; 04-20-2017 at 07:59 AM.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    Don't you read the posts? re-read post #5 as where the formula goes.

  10. #10
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    I followed your suggestion in post #5 and pasted your formula to cell AZ1. The formula works fine when cell AT71 reads "Last 5". But when I change AT71 to "All Matches", in columns BC to BN (second table), results are ok and similar to those in table one, but columns BO to CL are misplaced and not similar to those in table one.

    When AT71=All matches, then results in the second should similar to those in table one, which is not the case

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    The results won't be because the data is divide by 99 (the value in AZ1). The need to divided by number of H matches (in BO)

    so in BO

    =SUMPRODUCT(($W$6:$W$2000)*($U$6:$U$2000>=LARGE(--($V$6:$V$2000=$AY6)*($U$6:$U$2000),$AZ$1))*($V$6:$V$2000=$AY6))/COUNTIF(V:V,$AY6)

    And these are "wrong"

    =IF($AY$6:$AY$29="","",AVERAGEIFS(X:X,V:V,$AY6))

    should really be

    =IF($AY$6="","",AVERAGEIFS(X:X,V:V,$AY6))

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    See attached: corrected AVERAGES

    Remove the

    =IF($AY$6:$AY$29="",""

    and ALL replace with

    =IF($AY$6="",""

    as formulae with the error have to entered as array formulas which is not required with the correct option.
    Attached Files Attached Files
    Last edited by JohnTopley; 04-22-2017 at 08:37 AM.

  13. #13
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Thank you a lot John.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    See attached with updated averages for TOTAL games.

    Just realised this leaves a problem of how we AVERAGE last 5 vs ALL games.

    Look at formula in BU47 and compare with BU11 when you canh=ge from "All matches" to "5" matches

    =SUMPRODUCT(($X$6:$X$2000)*($U$6:$U$2000>=LARGE(--($V$6:$V$2000=$AY11)*($U$6:$U$2000),$AZ$1))*($V$6:$V$2000=$AY11))/(IF($AZ$1=99,COUNTIF(V:V,$AY11),$AZ$1))


    I believe this correct result for Burnley

    If so, formulas need be amended to add the IF condition on the AVERAGES.
    Attached Files Attached Files
    Last edited by JohnTopley; 04-25-2017 at 05:37 AM.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    Try

    in BV47

    =SUMPRODUCT(($W$6:$W$2000)*($U$6:$U$2000>=LARGE(--($Y$6:$Y$2000=$AY11)*($U$6:$U$2000),$AZ$1))*($Y$6:$Y$2000=$AY11))/(IF($AZ$1=99,COUNTIF(V:V,$AY11),MIN($AZ$1,COUNTIF(Y:Y,$AY11))))

  16. #16
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Changing AT71 to "All Matches" and auto-filling after pasting the formula does give actual result for liverpool when playing away.

  17. #17
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    I posted this in a new thread but unfortunately it has been closed, so I am reposting the question again. This is a continuation:

    I am attaching three workbooks as follows:

    1. All Matches – calculations are based on all home and away matches
    2. Last 5 – calculations are based on the last 5 home and away matches.
    3. Two in one – This is where the tables above will be merged.

    Required on the 3rd table:

    There is a drop down in cell Y2 which 2 options (All matches and Last 5). I need to have only the 3rd table where it will pick formulas in the workbook “Calculations” when Y2=All Matches and pick the formulas is the workbook “Last 5” when Y2=Last 5.

    I will appreciate all the help I can get. Thanks in advance.

    All Matches.xlsx

    Last 5.xlsx

    Two in One.xlsx

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    Perhaps it would be better if you explained what was wrong in the results in the highlighted table in the attached.
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    When AT71=All Matches, compare following columns:

    BB, BX,CA, CD, CG.....

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    see attached ...
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Check ball possession, shots on goal, shot accuracy, pass accuracy and successful crosses for Burnley under "H".

  22. #22
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Can this be achieved as this problem was solved in the thread below under comment #33?

    https://www.excelforum.com/excel-for...derived-2.html

    I have tried the following formula for example in cell AG5 but I get the "too many arguments error":

    =IF($AC6="","",IF(Y2=”ALL MATCHES”,SUMPRODUCT(($D$6:$D$5000=$AC6)*($E$6:$E$5000>$F$6:$F$5000))),SUMPRODUCT(($D$6:$D$5000=$AC6)*(ROW($D$6:$D$5000)>=MAX(ROW($D$6),LARGE(INDEX((($D$6:$D$5000=$AC6))*ROW($D$6:$D$5000),0),SCOPE!$F$5)))*($H$6:$H$5000="W")))
    Last edited by gko_87; 05-09-2017 at 05:37 PM.

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    try attached ....
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Thanks John for the reply. There seems for be a problem with average goals against for Burnley and Liverpool under "A".

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    Burnley: H 4 goals/ 6 games =4/6 =0.67 A: 9 goals/6 games = 1.75 (All matches)

    5 Matches H: 3/5 =0.6 A 9/4 =2.25 (2.3 if 1 decimal place)

  26. #26
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Switch between formulas using drop down list

    Home goals are to be divided by home games, away goals to be divided by away games.

    So for Burnley Away, 9/6 = 1.75 is wrong because 9 is the number of Away goals and 6 is number of home matches.

  27. #27
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Switch between formulas using drop down list

    Look at formula in BV and work out the error: it easy to spot!

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Switch between formulas using drop down list

    Rule 08: Cross-posting Without Links

    Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    No further help to be offered, please, until the OP has complied with this request.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Have drop down list - Need to Change formulas based on drop down selected
    By snuffnchess in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2015, 01:10 PM
  2. [SOLVED] Drop down list to pull different formulas for interpolation?
    By wglenn13 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2015, 08:54 AM
  3. Drop Down list of Formulas
    By michaeld1004 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-07-2013, 08:19 PM
  4. Drop Down List with formulas and Vlookup (maybe)
    By urumi09 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2012, 07:42 PM
  5. Replies: 3
    Last Post: 02-04-2012, 07:57 AM
  6. Drop-down list connected to multiple formulas
    By m_buskirk in forum Excel General
    Replies: 8
    Last Post: 12-17-2011, 05:03 AM
  7. Excel 2007 : Formulas in drop down list
    By Marz73 in forum Excel General
    Replies: 6
    Last Post: 11-10-2011, 03:11 PM

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