Solved

# Excel Formula Help

Posted on 2011-02-24
250 Views
Hello I have this formula listed below, it analyzes J5 to M5 to find a common value, but I discovered an error, here is the data this time listed below, the formula should return a -TSRA but it did not. any help would be greatly appreciated.

J5            K5             L5             M5
+TSRA      -TSRA      -TSRA

=IFERROR(SUBSTITUTE(SUBSTITUTE(INDEX(J5:M5,MODE(MATCH(SUBSTITUTE(SUBSTITUTE(J5:M5,"-",""),"+",""),SUBSTITUTE(SUBSTITUTE(J5:M5,"-",""),"+",""),0))),"-",""),"+",""),M5)
0
Question by:sandramac
• 3
• 2

LVL 50

Expert Comment

ID: 34975819
Hello sandramac, that looks like one of mine.....

It needs to be confirmed with CTRL+SHIFT+ENTER and then it should return the value TSRA (it ignores + and minus signs....) what does it return, what do you expect?

regards, barry
0

Author Comment

ID: 34975846
Hi barry, the CTR-SHFT-ENTER worked, had a brain lapse on it.  but on one instance it wont return the right data when the following is present:

J5                K5                 L5                  M5
TSRA            -TSRA                                                           it should return -TSRA or TSRA, but it gives me                                                  a blank...
0

LVL 50

Expert Comment

ID: 34975849
Hello,

the way the formula is set up, it will never return a + or a - since these are substituted with "".

Can you explain what you want to achieve? Maybe there's a different approach.

cheers, teylyn
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 34975875
OK, yes, I can see that - that's because there are 2 blanks so it's actually taking a blank as the modal value (because the blank comes first, too), so you can prevent it considering blanks with a small tweak

=IFERROR(SUBSTITUTE(SUBSTITUTE(INDEX(J5:M5,MODE(IF(J5:M5<>"",MATCH(SUBSTITUTE(SUBSTITUTE(J5:M5,"-",""),"+",""),SUBSTITUTE(SUBSTITUTE(J5:M5,"-",""),"+",""),0)))),"-",""),"+",""),M5)

still confirmed with CTRL+SHIFT+ENTER

regards, barry
0

Author Comment

ID: 34975886
Hello I have results from 4 different sheets that are displayed in J5 to M5  if there are two or more of the same value, ignoring any + or - signs then the return result will be that matching result with a + sign if there is one or a - sign if there is one, if both exist then use the + sign here are some examples below

J5              K5                 L5                M5
RA            +RA              -RA                              return +RA
SHRA      -SHRA           -SHRA                          return -SHRA
SN           +SN               SN                                 return SN
TSRA        -TSRA                                               return -TSRA
0

Author Comment

ID: 34975891
barry,

that did the trick.
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.