Mean, Median and Mode among sheets in the same Spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1478743%22%20slang%3D%22en-US%22%3EMean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478743%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20applied%20the%20formulas%20of%20mean%2C%20median%20and%20mode%20among%20different%20sheets%20in%20the%20same%20spreadsheet%2C%20but%20the%20MODE%20formula%20is%20giving%20me%20an%20error.%20Any%20help%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%2BMEDIANA(Analia%3ASantiago!F6)%20Result%20is%20Fine%3C%2FP%3E%3CP%3E%3D%2BPROMEDIO(Analia%3ASantiago!F6)%20Result%20is%20Fine%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3D%2BMODA(Analia%3ASantiago!F6)%20%23%C2%A1REF!%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3D%2BMODA.UNO(Analia%3ASantiago!F6)%26nbsp%3B%23%C2%A1REF!%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3D%2BMODA.VARIOS(Analia%3ASantiago!F6)%26nbsp%3B%23%C2%A1REF!%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EThank%20you%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1478743%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478760%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705535%22%20target%3D%22_blank%22%3E%40Pata05%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20expect%20to%20see%20a%20multi-cell%20reference%20to%20values%20to%20be%20averaged%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478789%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478789%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Peter.%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20I%20understood%20your%20comment.%20I%20am%20using%20to%20calculate%20the%20mode%20the%20same%20cell%20range%20I%20used%20for%20Mean%20and%20Median%20with%20no%20porblem%20and%20it%20gives%20me%20the%20correct%20result%2C%20but%20for%20some%20reason%20when%20I%20try%20to%20use%20the%20Mode%20formula%20it%20does%20not%20work%20and%20it%20shows%20that%20%23%C2%A1REF!%20in%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479078%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705535%22%20target%3D%22_blank%22%3E%40Pata05%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20I%20didn't%20look%20carefully%20enough%20and%20thought%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EAnalia%3ASantiago!F6%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewas%20a%20single%20cell%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20appears%20that%20MODE%20does%20not%20accept%203D%20references.%26nbsp%3B%20Not%20all%20functions%20do.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479119%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479119%22%20slang%3D%22en-US%22%3E%3CP%3EOk.%20But%20I%20find%20very%20strange%20that%20this%20family%20of%20statistical%20functions%20like%20mean%2C%20median%2C%20and%20even%20standard%20deviation%20work%20fine%2C%20and%20mode%20not.%20Why%20would%20not%20the%20Mode%20function%20accept%203D%20references%3F%26nbsp%3B%3CSPAN%3EI%20believe%20this%20is%20an%20error%20of%20Excel.%20Would%20it%20be%20posible%20that%20Excel%20porgramers%20fix%20this%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479140%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479140%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20no%20expert%20but%20why%20wouldn%E2%80%99t%20it%20be...%3CBR%20%2F%3E%3DMODE.SNGL(Analia%3ASantiago!F6)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20thought%20that%20was%20the%20preferred%20syntax%2C%20but%20again%2C%20I%E2%80%99m%20no%20expert.%20(I%20just%20happened%20to%20be%20trying%20to%20use%20the%20mode%20function%20and%20came%20across%20this.)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479143%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F617687%22%20target%3D%22_blank%22%3E%40EG3EG3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20that%20is%20probably%20the%20syntax%20in%20English%2C%20which%20tranllated%20to%20Spanish%20is%20MODA.UNO(%3CSPAN%3EAnalia%3ASantiago!F6).%20I%20will%20try%20to%20post%20this%20in%20the%20Spanish%20forum.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDo%20you%20know%20if%20the%20function%20as%20you%20wrote%20it%20works%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479305%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705535%22%20target%3D%22_blank%22%3E%40Pata05%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%2C%20it%20doesn't%20work.%20List%20of%20functions%20which%20support%203D%20reference%20is%20within%20this%20post%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2015%2F12%2F09%2Fexcel-3d-reference-formula%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2015%2F12%2F09%2Fexcel-3d-reference-formula%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EIt's%20relatively%20old%2C%20but%20I%20don't%20think%20something%20changes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479411%22%20slang%3D%22en-US%22%3ERe%3A%20Mean%2C%20Median%20and%20Mode%20among%20sheets%20in%20the%20same%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479411%22%20slang%3D%22en-US%22%3E%3CP%3EThan%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20post%20is%20clear%2C%20I%20will%20send%20a%20post%20over%20there%20and%20also%20check%20if%20LibreOffice%20works%20with%20the%20mode%20function%20and%203D%20references.%3C%2FP%3E%3CP%3ERegards.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

@Pata05 

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

Highlighted

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 

Highlighted

@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.

Highlighted

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

Highlighted
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.)
Highlighted

@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?

Highlighted

@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.

Highlighted

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.