Excel Formula Help

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)
sandramacAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
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
 
barry houdiniCommented:
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
 
sandramacAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
sandramacAuthor Commented:
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
 
sandramacAuthor Commented:
barry,

that did the trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.