how to set up formula with more than 1 result
 

How to how to set up formula with more than 1 result

For example

A

1 50
2 100
3 50
4 150
5 100
6 200


if i use =mode(A1:A6) the result is 50, what should i do to make it display
all result? I faced the same problem while using Vlook & Hlookup also

Ths

how to set up formula with more than 1 result
 

Assuming you want:

50
100
150
200

Enter this formula in C1:

=MODE(A1:A6)

Enter this array formula** in C2 and copy down as needed:

=MODE(IF(COUNTIF(C$1:C1,A$1:A$6)=0,A$1:A$6+{0,0}))

No error checking and does not account for empty cells!

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)



--
Biff
Microsoft Excel MVP


"ONG" wrote in message
...
How to how to set up formula with more than 1 result

For example

A

1 50
2 100
3 50
4 150
5 100
6 200


if i use =mode(A1:A6) the result is 50, what should i do to make it
display
all result? I faced the same problem while using Vlook & Hlookup also

Ths

How to set up formula with multiple results
 

If i use Match in as below

100
200
200
200
50
200

Formula= Match(200,a1:a6,false)
Result =2, what formula should i use if i want the all results coming out?
ie {2,3,4,6}

How to set up formula with multiple results
 

what formula should i use if i want the all results
coming out? ie {2,3,4,6}

Explain what you are trying to do?


--
Biff
Microsoft Excel MVP


"ONG" wrote in message
...
If i use Match in as below

100
200
200
200
50
200

Formula= Match(200,a1:a6,false)
Result =2, what formula should i use if i want the all results coming out?
ie {2,3,4,6}

How to set up formula with multiple results
 

I wish to display all results when i use the "MATCH" & also for other formula

How to set up formula with multiple results
 

You can't return the results like: {2,3,4,6}. You'd have to return one
number per cell like this:

2
3
4
6

To do that the formula gets more complicated. Try this array formula**:

Entered in C1 then copied down until you get blanks:

=IF(ROWS(C$1:C1)<=COUNTIF(A$1:A$6,200),SMALL(IF(A$ 1:A$6=200,ROW(A$1:A$6)),ROWS(C$1:C1))-MIN(ROW(A$1:A$6))+1,"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"ONG" wrote in message
...
I wish to display all results when i use the "MATCH" & also for other
formula