Forum Discussion
Please tell me what I am doing incorrectly-Excel SUBTOTAL Function 9 and 109 return the same Results
The following adds to information that may be useful inn determining a solution to the above problem.
The lack of responses to the Post prompted me to examine some related matters. Observing that Directions to access features, at deeper levels in Menus, are not up to date it occurred to me that other higher level aspects may also be suffering similarly.
Prior to the 365 Installation ti was seen in the Computer Requirements that the Processor should be 'Intel', however the desire to try Excel encouraged the installation disregarding the processor being used.
The installation has been completed on a MacBook [2020] Pro M1 with 1TB SSD - 16 Mb RAM., working space is conserved courtesy of 3 External SSDs holding Photos, Data and Time Machine.
Given the massive Apple swing toward their own Processors I am hopeful that Microsoft would have, or be in the process, of taking steps toward smooth functionality between 365 and the M range of Processors.
Any information will be greatly appreciated. Thank you.
Needing to progress this matter to, if it becomes necessary, cancel my 365 Subscription before the Trial Month expires it has been reported via the In App Feedback system.
A Link to this Thread has been included in my Feedback and not wishing to waste anyones time include details of the Software involved.
My MacBook Pro 2020 M1 is running macOS Monterey Version 12.1
Excel is Microsoft® Excel for Mac Version 16.56 (21121100) Licence: Microsoft 365 Subscription
Hopefully I have now included all the information that may be needed. Please feel free to inform if there is anything else required. Thank you.
Gelphyn From looking at the workbook, there are values in the cells for the "V" rows, but the format causes the value to not be visible. The Excel SUBTOTAL function does not consider these to be hidden, since the rows are not hidden. If you use a filter to filter out the "V" rows, they will be hidden and the subtotal result should be as expected. You could also hide the rows by right-clicking on the row number and choosing "Hide".
Thank you for your response.
Understanding the purpose of 2 SUBTOTAL Functions, namely '9' and '109', seems to be my difficulty.
I accept that what you say is true, and aware that Cell Content is Retained when Hidden, but additionally my understanding is based the following:
From Excel > Help > SUBTOTAL [NB. Help Information has been Italicised] :
The SUBTOTAL function syntax has the following arguments:
Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
I am unsure about "filtered-out cells are always excluded." and what actions are require from the User and therefore any omissions or errors that may have been made or involved.
The following is an abbreviated [+ modified to circumnavigate Forum formatting restrictions] version of the SUBTOTAL Functions Table:
Function_num Function_num Function
(includes hidden values) (ignores hidden values)9 109 SUM
My use is based on the above information, that offers to hide Cells, chosen by utilising "Format > Cell > Number > Custom > then inserting ';;;' appropriately".
This action duly hides the visibility of the chosen Cell(s) content(s) but raises 2 questions:
1. Is Function 9, designed NOT to display hidden content? This is occurring while Help Information implies that this is NOT true. The resulting Total certainly looks odd and the non-display of Values, given the simplicity of the maths, presents a Result that is visually incorrect.
2. Is Function 109 designed NOT to ignore hidden content with regard to the Total? Again this is occurring while Help Information implies that this is NOT true. Once again the non-display of Values presents a Result that is visually incorrect.
Frankly I cannot believe this is or was the Developer's intention.
Running on my setup strongly suggests dysfunctionality regarding these 2 SUBTOTAL Functions?
As previously mentioned this Function, as described in Excel Help Information, is required on a working Spreadsheet. This Sheet has Rows containing 10+ Cells containing Numeric Values. ONLY one Product Type is affected but there are usually a few Varieties of that Type selected for C&P.
ONLY one Cell of each Product Type Row contains information that needs to be hidden.
If the Values of these Cells are included in the Result output [SUM/Total Row/?] it is pointless hiding something that is not effectively hidden in reality.
Rows are copied from the above Spreadsheet and pasted into another Sheet where the resulting Totals of several Rows are used for evaluation, the inclusion of false information negates the purpose of this process.
Using Apple Numbers the desired Result can be achieved by a workaround, it is assumed this also would be possible in Excel. This simply relies on displacing the Cell, to be excluded from the Result, by moving it to a convenient adjacent Row. Then indicating this displacement by adding a simple graphic, in the form of a line with arrowheads on each end [as in: moved from here to here]. Hopefully reminding that a displacement has occurred by indicating the locations of both Cells. Rows containing information regarding this Product Type cannot include information displaced [read: hidden] because it is absent from the Rows that'll ultimately be C&P.
As this applied to a single Product Group the Graphics were easy to Group, but less easy to manipulate, lock in place or maintain, this unsatisfactory situation caused me to consider returning to Excel.
Given the unanswered aspects regarding whether Microsoft actively support macOS running on M1 Chips, it would be very useful if some kind soul could replicate the attached Spreadsheet [in the Post at the top of this Thread] using a Windows system and post the outcome below.
Please help me to justify subscribing to 365. Thanks in advance for any help you can offer.
