# Combine multiple Substitute functions into 1 formula

1. ## 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  Register To Reply

2. ## 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:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Combine multiple Substitute functions into 1 formula

There is always only these 5 Letters
O
M
V
G
ZG  Register To Reply

4. ## Re: Combine multiple Substitute functions into 1 formula

@ 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  Register To Reply

5. ## Re: Combine multiple Substitute functions into 1 formula

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

6. ## Re: Combine multiple Substitute functions into 1 formula

or ``Please Login or Register  to view this content.``  Register To Reply

7. ## 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.  Register To Reply

8. ## 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)  Register To Reply

9. ## 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.  Register To Reply

10. ## 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.  Register To Reply

11. ## 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.  Register To Reply

12. ## 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.  Register To Reply

13. ## Re: Combine multiple Substitute functions into 1 formula Originally Posted by bakerman2 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.  Register To Reply