Mean, Median and Mode among sheets in the same Spreadsheet

Question

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

pata05 · Answer

EG3EG3&nbsp;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.&nbsp;Do you know if the function as you wrote it works?

sergeibaklan · Answer

Pata05&nbsp;
No, it doesn't work. List of functions which support 3D reference is within this post&nbsp;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.

peterbartholomew1 · Answer

Pata05&nbsp;I would expect to see a multi-cell reference to values to be averaged?

pata05 · Answer

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.&nbsp;PeterBartholomew1&nbsp;

peterbartholomew1 · Answer

Pata05&nbsp;Sorry, I didn't look carefully enough and thoughtAnalia:Santiago!F6was a single cell reference.&nbsp;It appears that MODE does not accept 3D references.&nbsp; Not all functions do.

Forum Discussion

Mean, Median and Mode among sheets in the same Spreadsheet

8 Replies

Resources