Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-28-2013, 02:41 PM
Steve_B Steve_B is offline Find MAX value in data containing '#N/A' Windows 7 64bit Find MAX value in data containing '#N/A' Office 2010 32bit
Novice
Find MAX value in data containing '#N/A'
 
Join Date: Jul 2013
Posts: 7
Steve_B is on a distinguished road
Wink Find MAX value in data containing '#N/A'

Need a way to find the max value in data that may contain the error value '#N/A' instead of a numeric value. MAX() will return #N/A if it encounters it anywhere in the data being queried. It can't be that hard. Well, you would think not. Using Excel 2010.

Last edited by Steve_B; 10-28-2013 at 03:45 PM.
Reply With Quote
  #2  
Old 10-29-2013, 05:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Find MAX value in data containing '#N/A' Windows 7 64bit Find MAX value in data containing '#N/A' Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Perhaps following array formula
Code:
=MAX(IF(NOT(ISNA($A$1:$A$50)),($A$1:$A$50)))
Commit with Ctrl+Shift+Enter
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 10-29-2013, 07:49 AM
Steve_B Steve_B is offline Find MAX value in data containing '#N/A' Windows 7 64bit Find MAX value in data containing '#N/A' Office 2010 32bit
Novice
Find MAX value in data containing '#N/A'
 
Join Date: Jul 2013
Posts: 7
Steve_B is on a distinguished road
Default Excellent Solution

Thank you, Pecoflyer. Your solution is exactly what I needed. You are indeed an expert.
Reply With Quote
  #4  
Old 10-30-2013, 12:02 AM
Kevin@Radstock Kevin@Radstock is offline Find MAX value in data containing '#N/A' Windows 7 32bit Find MAX value in data containing '#N/A' Office 2010 32bit
Office 365
 
Join Date: Feb 2012
Posts: 94
Kevin@Radstock is on a distinguished road
Default

Quote:
Originally Posted by Steve_B View Post
Thank you, Pecoflyer. Your solution is exactly what I needed. You are indeed an expert.
If your using Excel 2010 as in your profile, why don't you use the AGGREGATE function (Non array) and assuming your data is in column A!

=AGGREGATE(4,6,A:A)
Reply With Quote
Reply

Tags
#na, max()



Similar Threads
Thread Thread Starter Forum Replies Last Post
Find MAX value in data containing '#N/A' Word 2010 cannot find data source wsdb Mail Merge 7 09-05-2012 03:16 PM
Find MAX value in data containing '#N/A' Cannot find Outlook 2010 data files COLC Outlook 4 08-09-2012 06:02 AM
Find MAX value in data containing '#N/A' find data GPENTEZ Excel 3 01-07-2012 03:41 PM
Find MAX value in data containing '#N/A' Bad view when using Find and Find & Replace - Word places found string on top line paulkaye Word 4 12-06-2011 11:05 PM
Find MAX value in data containing '#N/A' How to write a macro to find a specified name in a list of data? Jaffa Excel 1 10-23-2010 02:39 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:47 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft