Urgent MIN function help
Posted by Rob on December 14, 2001 9:58 AM
I am trying to get a MIN value for a set of specific cells, (not a range). I am trying to get the MIN value of cells g3,k3,o3,s3,w3. Do not want the value of the cells in between those cells.
How can I do this? I need it urgently.
Any advice is greatly appreciated.
Posted by Aladin Akyurek on December 14, 2001 10:03 AM
Rob --
Why start another thread again? Mark has given you a formula that covers your special need. If you didn't understand the underlying logic, you could have said so in the previous thread.
I'm sorry I felt that I had to say this much.
Aladin
Posted by Mark W. on December 14, 2001 10:04 AM
Rob, don't you read your earlier postings? (nt)
Posted by Joe Was on December 14, 2001 10:24 AM
You can use Ctrl+f to find your name on the board!
Rob,
When you repost the same question over time, hence different positions on the board list or if you are answering several quistions like I am. You can use the search utility to find all your open problems.
Hit Ctrl+f to open the search utility, then add your name and it will find your most resent question/problem, search again and it will find the next an so on. Hope this helps you. JSW
Posted by Mark W. on December 14, 2001 10:34 AM
Sorry, if I was too short with you...
Here's all you have to do to adapt the array
formula published earlier...
{=MIN(IF((COLUMN(A3:Q3)={0,0,3,0,0,0,7,0,0,0,11,12,0,0,0,0,17})*ISNUMBER(A3:Q3),A3:Q3))}
1. Change all range references from A3:Q3 to A3:W3
since W3 is the last cell of interest
2. Change the array constant, {0,0,3,...} to pick
the columns of interest. This new array constant
must have 23 entries instead of 17 since W:W is
the 23rd column on the worksheet. Notice that
columns of interest have their column numbers
listed in the array constant instead of 0. The
new array constant is...
{0,0,0,0,0,0,7,0,0,0,11,0,0,0,15,0,0,0,19,0,0,0,23}
...so the revised array formula is...
{=MIN(IF((COLUMN(A3:W3)={0,0,0,0,0,0,7,0,0,0,11,0,0,0,15,0,0,0,19,0,0,0,23})*ISNUMBER(A3:W3),A3:W3))}
Posted by Mark W. on December 14, 2001 10:48 AM
If you find this too cumbersome, you could...
...reserve (and if desired, hide) a row for a set
of "switches" Enter the formula, =COLUMN(), into
cells G1, K1, O1, S1 and W1 leaving the remainder
of row 1 blank, and change your formula to...
{=MIN(IF((COLUMN(A3:Q3)=A1:Q1)*ISNUMBER(A3:Q3),A3:Q3))}
Posted by Mark W. on December 14, 2001 10:50 AM
Oops! Typo requiring revision...
{=MIN(IF((COLUMN(A3:W3)=A1:W1)*ISNUMBER(A3:W3),A3:W3))} ...reserve (and if desired, hide) a row for a set
Posted by Paul on December 14, 2001 10:58 AM
Why not =MIN(G3,K3,O3,S3,W3)
Posted by Mark W. on December 14, 2001 2:08 PM
Re: Why not =MIN(G3,K3,O3,S3,W3)
There's a bit of history on this one... He also
wants to exclude zero values. : I am trying to get a MIN value for a set of specific cells, (not a range). I am trying to get the MIN value of cells g3,k3,o3,s3,w3. Do not want the value of the cells in between those cells.
