Solved

Excel Formula Help

Posted on 2011-02-24
6
250 Views
Last Modified: 2012-05-11
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
Comment
Question by:sandramac
  • 3
  • 2
6 Comments
 
LVL 50

Expert Comment

by:barry houdini
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

by:sandramac
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

by:teylyn
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 50

Accepted Solution

by:
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

by:sandramac
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

by:sandramac
ID: 34975891
barry,

that did the trick.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now