[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Compare multiple cells of data within the same row to find outliers in Excel.

Posted on 2013-11-19
3
Medium Priority
?
375 Views
Last Modified: 2013-11-19
I have a spreadsheet with 6850 rows of products mapped to charge numbers in multiple facilities.  I need to compare these facility values in each row to find any that do not match the others within that row. Some cells are blank so we need to ignore the empty cells. I am not comparing to another spreadsheet or data-set simply the data within each row. I would like a formula that simply returns a true/false result.
Data-Validation-for-Outliers.xlsx
0
Comment
Question by:nts42a
3 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 39659988
try:

=IF(COUNTA(B2:F2),SUMPRODUCT(COUNTIF(B2:F2,B2:F2))/COUNTIF(B2:F2,"<>")=COUNTA(B2:F2))
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39659992
I have seen use of the SMALL function to return a list of unique values from a column.

I guess if you could transpose the data so comparing columns rather than rows, you could then apply a count to the list produced by the SMALL function and if the count is more than 1, you have multiple entries. Not sure how it would handle blank.

Thanks
Rob H
0
 

Author Closing Comment

by:nts42a
ID: 39660014
Worked perfectly.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

834 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