Median function ignoring zero

Hi robin and welcome to the forum,

The obvious answer is:

=MEDIAN(A2,A6,A10,A12)

But you must mean that one of the cells is zero? Perhaps?

If you force one of the A2, A6, A10 or A12 to text the formula gives #Value.
Can you be more exact on what values (or text) the above 4 cells might have.

For array function it is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

attached file contains manual method of median and one with CSE

both generate same answer when random numbers are updated?

you should lead with something more representative of your actual problem next time

Short version

Formula:

Please Login or Register  to view this content.

now i can also assume that "data" isnt going to be what your actually have so how is "data" and "not Data" actually defined


attached something that explains abit more of whats going on

yes i see why you started out the way you did
this is a very different kettle of fish

let me a have a think about it and see what i would do in your case
it definitely would not be the solution offered previously

excuse the essay but im guessing you want a break down on whats happening
right, without resorting to VBA

here is what i came up with
A series of 5 helper columns (its a lot but its due to how your data is output) to pipe out the corresponding numbers to the each stimulus
3 helpers is just to define exactly what each stimulus result you are looking at
Congruent, Incongruent or Neutral

Formula:

Please Login or Register  to view this content.

this formula looks for the 3rd " " (space) in the stimulus title to start the MID formula and the length is determined by finding the 4th minus the 3rd

i had to do some data entry to your stimulus as some of them had uneven spacing
ie i change "Stim14 - Up Incongruent- Double Cue - RIGHT" to "Stim14 - Up Incongruent - Double Cue - RIGHT"
***this is an important step for you to clean up the stimulus titles or else it will give incorrect outputs

second helper is to extract
No Cue, Central Cue, Double Cue, Spatial Cue

Formula:

Please Login or Register  to view this content.

same idea as above but using "-" instead of space

third helper is to extract left or right

Formula:

Please Login or Register  to view this content.

^the simple version of this formula is it just trims off everything the final "-"
in this case...its either left or right

4th and 5th checks on 3rd helper column to find out where to take the value from
nEvents is

Formula:

Please Login or Register  to view this content.

time mean is

Formula:

Please Login or Register  to view this content.

now it seems daunting to begin with but its rather simple as a template you can apply ongoing (of which you can hide the columns)

all this accumulates to final formula which are both CSE
mean RT is

Formula:

Please Login or Register  to view this content.

Median is RT

Formula:

Please Login or Register  to view this content.

column u and V are just check boxes for you to know the formula pumps out the same numbers are your other method

you will see it clearly on the file attached

not a problem, was a good challenge

i wasn't sure if you wanted that zero thing in there or not so i excluded it to make the results match your figures

just remember to keep the stimulus titles in the "same" format or else you may get unexpected results
you will when it doesn't work in the helper column outputs

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.