formula for choosing between Mode and Median

I'm trying to use a data validation list as a way to allow the user to select between mode or median. I need a formula that would look at the value in the cell (Mode/Median) and return the correct product for a range of cells. I hope this makes sense. I can try to elaborate if necessary.

Hello welcome to the forum.

One option would be to use the CHOOSE function, for example:

Formula:

=CHOOSE(MATCH(Validtion_Cell,{"MODE","MEDIAN"},0),MODE(Values),MEDIAN(Values))

'Value' is the range of numbers, and 'Validation_Cell' the drop down selection. Here 'MODE' and 'MEDIAN' are hard coded (MATCH) but could be read from sheet cells along with the drop down list.

Hope this helps.
DBY

Thank you so much for the quick response. That seems to work when the value in the validation cell is median, but when mean is chosen it returns #N/A. here is the formula I used based on your advice.

Formula:

=CHOOSE(MATCH(A11,{"MODE","MEDIAN"},0),MODE(B7:B10),MEDIAN(B7:B10))

Did I do something wrong?

Hi

I need a formula that would look at the value in the cell (Mode/Median)

You did say MODE/MEDIAN not Mean. There's no MEAN function in Excel as it's interpreted as AVERAGE, of which there are a few variations. For each drop down item you have to add the relevant function to the choices.

Thank You, I'm an idiot!

Glad to have helped. Mahju is correct, MODE will return an error in those circumstances.