Jun 20 2020 08:59 AM
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
Jun 20 2020 09:11 AM
I would expect to see a multi-cell reference to values to be averaged?
Jun 20 2020 10:09 AM
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.
Jun 20 2020 04:24 PM
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.
Jun 20 2020 06:40 PM
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
Jun 20 2020 07:28 PM
Jun 20 2020 07:47 PM
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?
Jun 21 2020 03:11 AM
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.
Jun 21 2020 05:58 AM
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.