Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 989
Default lowest number in row excluding empty cells

I am trying to return the minimum value in a range J2:AJ2 whilst ignoring
empty cells. How do I do it?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default lowest number in row excluding empty cells

hi,
for rows, try =min(2:2) or =mina(2:2)

regards
FSt1

"Mark" wrote:

I am trying to return the minimum value in a range J2:AJ2 whilst ignoring
empty cells. How do I do it?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default lowest number in row excluding empty cells

Try this array entere with trl+Shift+Enter

=MIN(IF(J2:AJ20,J2:AJ2))

Mike

"Mark" wrote:

I am trying to return the minimum value in a range J2:AJ2 whilst ignoring
empty cells. How do I do it?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default lowest number in row excluding empty cells

hi
or Min(J2:AJ2)

Regards
FSt1

"FSt1" wrote:

hi,
for rows, try =min(2:2) or =mina(2:2)

regards
FSt1

"Mark" wrote:

I am trying to return the minimum value in a range J2:AJ2 whilst ignoring
empty cells. How do I do it?

  #5   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default lowest number in row excluding empty cells

"Mark" wrote:
I am trying to return the minimum value in a range J2:AJ2 whilst ignoring
empty cells. How do I do it?


Perhaps you had earlier tried: =MIN(J2:AJ2)
and you got zero, which is not what you want?
(MIN ignores empty cells, but those "empty" cells may actually contain
masked zero values)

If so, you could try this, array-entered,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=MIN(IF(J2:AJ20,J2:AJ2))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default lowest number in row excluding empty cells

Non-array alternative:

=SMALL(J2:AJ2,COUNTIF(J2:AJ2,0)+1)

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Max" wrote in message
...
"Mark" wrote:
I am trying to return the minimum value in a range J2:AJ2 whilst ignoring
empty cells. How do I do it?


Perhaps you had earlier tried: =MIN(J2:AJ2)
and you got zero, which is not what you want?
(MIN ignores empty cells, but those "empty" cells may actually contain
masked zero values)

If so, you could try this, array-entered,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=MIN(IF(J2:AJ20,J2:AJ2))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
lowest number in a set EXCLUDING zero Bad Kitty Excel Worksheet Functions 2 November 3rd 06 11:34 PM
Looking for formula for lowest costs, excluding zero Pipeladylu Excel Worksheet Functions 7 September 20th 05 10:42 PM
Is there a way to get an average, excluding the lowest number? TNTraining Excel Discussion (Misc queries) 7 June 2nd 05 10:12 PM
How to get lowest value excluding blanks JohnT Excel Worksheet Functions 5 December 4th 04 11:57 AM
excluding cells from the formula when empty Joe Shell Excel Worksheet Functions 5 November 21st 04 05:37 PM


All times are GMT +1. The time now is 10:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"