+ Reply to Thread
Results 1 to 13 of 13

Combine multiple Substitute functions into 1 formula

  1. #1
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Combine multiple Substitute functions into 1 formula

    In B2:B4 I have alphanumerical characters.
    In D2:D4 I've done the seperate steps to Substitute those characters to a prefixed numerical value so I can use the Average function in D5.
    In E5 the result of D5 is substituted back into alphanumerical value.
    The question is can all these seperate steps of D2:D5 & E5 be put into 1 formula in B5 ?
    The table in G:H is just to show what the substituting values are and is not to be used in formula (if even possible).
    I can easily write a UDF in VBA which takes care of the problem but I'm just wondering if there's a formula solution.

    Regards

    Rudi
    Attached Files Attached Files
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Combine multiple Substitute functions into 1 formula

    I don't understand why you need to use SUBSTITUTE. Use VLOOKUP instead
    Enter in D2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,953

    Re: Combine multiple Substitute functions into 1 formula

    There is always only these 5 Letters
    O
    M
    V
    G
    ZG
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Combine multiple Substitute functions into 1 formula

    Thanks for your prompt replies.

    @ Alkey

    The table is just an example and not permanent on worksheet. (otherwise it would that as simple as your reply)
    If the solution is possible without the table the better, if truly neccessary I can put it somewhere on the Sheet

    @ Siva

    Yes, there will be no empty cells in range B2:B4 and always one of those letters will be used
    Last edited by bakerman2; 06-07-2016 at 08:42 AM.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,953

    Re: Combine multiple Substitute functions into 1 formula

    Then try below formula
    Please Login or Register  to view this content.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,953

    Re: Combine multiple Substitute functions into 1 formula

    or
    Please Login or Register  to view this content.

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,714

    Re: Combine multiple Substitute functions into 1 formula

    I agree with AlKey that it's best to use your table to do lookups.
    The table in G:H is just to show what the substituting values are and is not to be used in formula
    Why not? That is actually the best way to solve this.

    Here is a formula that will also give your final answer (G in your example) in a single formula. This is an array formula, so after entering it in the cell do not press ENTER, press CTRL+SHIFT+ENTER.

    =INDEX($G$2:$G$6,ROUND(AVERAGE(MATCH($B$2:$B$4,$G$2:$G$6,0)),0))

    If you enter it correctly it will appear in the formula bar enclosed by { } but you can't type them, you have to use CTRL+SHIFT+ENTER.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Combine multiple Substitute functions into 1 formula

    @ Siva

    The formula in post#5 is spot on . Thanks very much for that.
    The second one I will try out later today

    @ Jeff

    As Siva proved it can be done without extra table so that answers my initial question fully (can it be done in 1 formula if possible without extra table)

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Combine multiple Substitute functions into 1 formula

    I think you just missing the point. It is not that it can be done with one formula. It is about it should't be done like that at all. You should always aim it a simpler approach instead of more complicated. That is just my opinion.

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combine multiple Substitute functions into 1 formula

    I agree.

    Actually the simplest way to do it is the way you have it originally.
    I would just replace the formulas in D2:D4 with a vlookup suggested in post #2.


    There is nothing wrong with using multiple cells to accomplish a calculation.
    It is usually more efficient to spread the calculations out over multiple cells, than it is to cram it all into 1 formula.

    Excel 2007+ gives you 1,048,576 rows and 16,384 columns
    That's 17,179,869,184 cells (per sheet) to work with.
    Might as well use a few of them.

    I understand the desire to keep the sheet 'appearance' simplified.
    You can HIDE the intermediary calculations (column D, and the table in G2:H6)
    In fact, they don't even need to be on the same page.

    It also makes it easier to troubleshoot and make adjustments to your calcuations this way.
    Last edited by Jonmo1; 06-07-2016 at 09:42 AM.

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,714

    Re: Combine multiple Substitute functions into 1 formula

    If you want a single formula with no tables, helpers, or anything else, then Siva's solution is a good one. That's exactly what you asked for, and there is nothing wrong with it. There is also nothing wrong with the other solutions here.

    I would just like to mention a couple of pros & cons to the single-formula solution:

    Pro: It's a self-contained solution. You don't have to look anywhere but the formula to see all the logic.

    Con: The formula is a bit dense out of necessity. The two arrays {"O","M","V","G","ZG"} and {1,2,3,4,5} each appear three times in the formula, and each occurrence has to match the other two. If you ever have to change this formula, there are six opportunities to make a mistake. If you use a table, you only need to make an update once instead of three times, and the visual layout of a table makes it easier to see how it works.

    As with many design decisions, part of the goodness of a solution depends on how you are going to use it. I personally find that cramming a lot of logic into one formula can be difficult to decipher six months later when I have to change it, even if I'm the one who wrote it in the first place. bakerman2, just make sure you understand how that formula works so you can take ownership of it.

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Combine multiple Substitute functions into 1 formula

    @ Jeff

    You'll never here me say that there's something wrong with the other solutions provided but it's the same when I'm writing code (since VBA is more my thing), I like it short and sweet.
    I'm not a fan of spreading out code over 10-15 lines if I can do it in 5. IMHO it's up to the user to show enough interest to understand what is written down, just the same as you state in your last sentence. Understand how it works to make it your own. Being that said I understand more then enough of the functions used in the formula of Siva, it's only making the combination of where I struggle the most.

  13. #13
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,714

    Re: Combine multiple Substitute functions into 1 formula

    Quote Originally Posted by bakerman2 View Post
    I'm not a fan of spreading out code over 10-15 lines if I can do it in 5.
    I think that religious wars have been fought over that issue You might enjoy looking at deliberately obfuscated code--some of this is remarkably clever, short, and completely impenetrable.

+ 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. [SOLVED] How to combine If and Substitute in Excel
    By alonecupid in forum Tips and Tutorials
    Replies: 4
    Last Post: 02-11-2016, 04:24 AM
  2. Vlookup and Substitute - Combine
    By Stressed_Daniel in forum Excel General
    Replies: 2
    Last Post: 07-16-2015, 07:38 AM
  3. [SOLVED] How to combine Upper Left and substitute in one formula?
    By MI3operations in forum Excel General
    Replies: 4
    Last Post: 03-20-2015, 02:26 PM
  4. [SOLVED] Combine multipe VLOOKUP results
    By Rubbertje in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-02-2013, 02:10 AM
  5. [SOLVED] Combine VLOOKUP and SUBSTITUTE Functions?
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2013, 11:00 AM
  6. How to combine multiple substitute functions?
    By ron_b_michigan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2012, 11:15 AM
  7. Looking for a site with functions that substitute the ATP functions
    By Franz Verga in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-23-2006, 11:35 PM

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