Mean, Median and Mode among sheets in the same Spreadsheet

Copper Contributor

I have applied the formulas of mean, median and mode among different sheets in the same spreadsheet, but the MODE formula is giving me an error. Any help: 

 

=+MEDIANA(Analia:Santiago!F6) Result is Fine

=+PROMEDIO(Analia:Santiago!F6) Result is Fine

=+MODA(Analia:Santiago!F6) #¡REF!

=+MODA.UNO(Analia:Santiago!F6) #¡REF!

=+MODA.VARIOS(Analia:Santiago!F6) #¡REF!

 

Thank you

 

8 Replies

@Pata05 

I would expect to see a multi-cell reference to values to be averaged?

Thank you Peter.

I am not sure I understood your comment. I am using to calculate the mode the same cell range I used for Mean and Median with no porblem and it gives me the correct result, but for some reason when I try to use the Mode formula it does not work and it shows that #¡REF! in the cell. 

@Peter Bartholomew 

@Pata05 

Sorry, I didn't look carefully enough and thought

Analia:Santiago!F6

was a single cell reference.

 

It appears that MODE does not accept 3D references.  Not all functions do.

Ok. But I find very strange that this family of statistical functions like mean, median, and even standard deviation work fine, and mode not. Why would not the Mode function accept 3D references? I believe this is an error of Excel. Would it be posible that Excel porgramers fix this? 

Thank you

I’m no expert but why wouldn’t it be...
=MODE.SNGL(Analia:Santiago!F6)

I thought that was the preferred syntax, but again, I’m no expert. (I just happened to be trying to use the mode function and came across this.)

@EG3EG3 

Ok, that is probably the syntax in English, which tranllated to Spanish is MODA.UNO(Analia:Santiago!F6). I will try to post this in the Spanish forum. 

Do you know if the function as you wrote it works?

@Pata05 

No, it doesn't work. List of functions which support 3D reference is within this post https://www.ablebits.com/office-addins-blog/2015/12/09/excel-3d-reference-formula/

It's relatively old, but I don't think something changes.

Than you @Sergei Baklan 

Your post is clear, I will send a post over there and also check if LibreOffice works with the mode function and 3D references.

Regards.