Why use Subtotals 1-11 instead of the built-in functions directly?

I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden
values.

But, I don't see why I want to use Subtotals 1-11, instead of using the
built-in functions like average, count, max etc. directly.

I do understand that if I use Data>Subtotals, I can later modify the
Subtotals function which may have 1-11.

Please enlighten me.  Why would I choose Subtotals 1-11 over a more direct
method?  Am I missing something obvious?

Thanks.

Epinn


0
someone6932 (228)
8/18/2006 5:34:28 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
503 Views

Similar Articles

[PageSpeed] 56

Epinn wrote:
> I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden
> values.
> 
> But, I don't see why I want to use Subtotals 1-11, instead of using the
> built-in functions like average, count, max etc. directly.
> 
> I do understand that if I use Data>Subtotals, I can later modify the
> Subtotals function which may have 1-11.
> 
> Please enlighten me.  Why would I choose Subtotals 1-11 over a more direct
> method?  Am I missing something obvious?
> 
> Thanks.
> 
> Epinn
> 
> 
Remember we can't see your spreadsheet. The above question can't be 
answered with the info you provided.

gls858
0
gls858 (460)
8/18/2006 6:29:33 PM
"Epinn" <someone@example.com.NO_SPAM> skrev i en meddelelse 
news:%23wSSSyuwGHA.4220@TK2MSFTNGP06.phx.gbl...
>I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden
> values.
>
> But, I don't see why I want to use Subtotals 1-11, instead of using the
> built-in functions like average, count, max etc. directly.
>
> I do understand that if I use Data>Subtotals, I can later modify the
> Subtotals function which may have 1-11.
>
> Please enlighten me.  Why would I choose Subtotals 1-11 over a more direct
> method?  Am I missing something obvious?
>
> Thanks.
>
> Epinn
>
>

Epinn

The important difference between Subtotal 1-11 and Subtotal 101 - 111 is,
that Subtotal 1-11 do not work on rows *hidden by filtering*, while
Subtotal 101 - 111 don't work on *any* hidden row (hidden by filtering OR
hidden manually).
The built-in functions you mention works on all rows, hidden or visible.


-- 
Best regards
Leo Heuser

Followup to newsgroup only please. 


0
8/18/2006 6:45:13 PM
> Remember we can't see your spreadsheet. The above question can't be
> answered with the info you provided.

Why do you need a spreadsheet?  This is a **general** question to understand
a theory/concept.  I have already cited my understanding below.  I am asking
for examples as to why I want to use one method over the other.  Anyone else
who understands what I am asking, please feel free to contribute.

Thanks.

Epinn

"gls858" <gls858@yahoo.com> wrote in message
news:unYTBRvwGHA.1272@TK2MSFTNGP05.phx.gbl...
> Epinn wrote:
> > I see the need to use 101-111 for Subtotals; e.g. I want to ignore
hidden
> > values.
> >
> > But, I don't see why I want to use Subtotals 1-11, instead of using the
> > built-in functions like average, count, max etc. directly.
> >
> > I do understand that if I use Data>Subtotals, I can later modify the
> > Subtotals function which may have 1-11.
> >
> > Please enlighten me.  Why would I choose Subtotals 1-11 over a more
direct
> > method?  Am I missing something obvious?
> >
> > Thanks.
> >
> > Epinn
> >
> >
> Remember we can't see your spreadsheet. The above question can't be
> answered with the info you provided.
>
> gls858


0
someone6932 (228)
8/18/2006 7:04:17 PM
Subtotals will ignore rows that have been filtered via
Data>Filter>Autofilter

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" <someone@example.com.NO_SPAM> wrote in message
news:u$5EzkvwGHA.888@TK2MSFTNGP02.phx.gbl...
> > Remember we can't see your spreadsheet. The above question can't be
> > answered with the info you provided.
>
> Why do you need a spreadsheet?  This is a **general** question to
understand
> a theory/concept.  I have already cited my understanding below.  I am
asking
> for examples as to why I want to use one method over the other.  Anyone
else
> who understands what I am asking, please feel free to contribute.
>
> Thanks.
>
> Epinn
>
> "gls858" <gls858@yahoo.com> wrote in message
> news:unYTBRvwGHA.1272@TK2MSFTNGP05.phx.gbl...
> > Epinn wrote:
> > > I see the need to use 101-111 for Subtotals; e.g. I want to ignore
> hidden
> > > values.
> > >
> > > But, I don't see why I want to use Subtotals 1-11, instead of using
the
> > > built-in functions like average, count, max etc. directly.
> > >
> > > I do understand that if I use Data>Subtotals, I can later modify the
> > > Subtotals function which may have 1-11.
> > >
> > > Please enlighten me.  Why would I choose Subtotals 1-11 over a more
> direct
> > > method?  Am I missing something obvious?
> > >
> > > Thanks.
> > >
> > > Epinn
> > >
> > >
> > Remember we can't see your spreadsheet. The above question can't be
> > answered with the info you provided.
> >
> > gls858
>
>


0
bob.NGs1 (1661)
8/18/2006 7:09:05 PM
Leo,

Thank you so much.  You told me exactly what I needed to know but missed.

The help feature talked about 1-11 including hidden values as a result of
format>row>hide but it had no mention of data hidden as a result of
**AutoFilter**.

What a subtle difference that can cause dramatically different effect!

Your help is much appreciated.

Cheers,

Epinn

"Leo Heuser" <leo.heuser@DROPTHISadslhome.dk> wrote in message
news:evutycvwGHA.4300@TK2MSFTNGP05.phx.gbl...
> "Epinn" <someone@example.com.NO_SPAM> skrev i en meddelelse
> news:%23wSSSyuwGHA.4220@TK2MSFTNGP06.phx.gbl...
> >I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden
> > values.
> >
> > But, I don't see why I want to use Subtotals 1-11, instead of using the
> > built-in functions like average, count, max etc. directly.
> >
> > I do understand that if I use Data>Subtotals, I can later modify the
> > Subtotals function which may have 1-11.
> >
> > Please enlighten me.  Why would I choose Subtotals 1-11 over a more
direct
> > method?  Am I missing something obvious?
> >
> > Thanks.
> >
> > Epinn
> >
> >
>
> Epinn
>
> The important difference between Subtotal 1-11 and Subtotal 101 - 111 is,
> that Subtotal 1-11 do not work on rows *hidden by filtering*, while
> Subtotal 101 - 111 don't work on *any* hidden row (hidden by filtering OR
> hidden manually).
> The built-in functions you mention works on all rows, hidden or visible.
>
>
> --
> Best regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
>


0
someone6932 (228)
8/18/2006 9:35:43 PM
You're welcome, Epinn.
Thanks for the feedback!

You're right about "Help". It's not very clear
in this situation.

-- 
Regards
Leo Heuser

"Epinn" <someone@example.com.NO_SPAM> skrev i en meddelelse 
news:O2%23NN5wwGHA.888@TK2MSFTNGP02.phx.gbl...
> Leo,
>
> Thank you so much.  You told me exactly what I needed to know but missed.
>
> The help feature talked about 1-11 including hidden values as a result of
> format>row>hide but it had no mention of data hidden as a result of
> **AutoFilter**.
>
> What a subtle difference that can cause dramatically different effect!
>
> Your help is much appreciated.
>
> Cheers,
>
> Epinn
>
> "Leo Heuser" <leo.heuser@DROPTHISadslhome.dk> wrote in message
> news:evutycvwGHA.4300@TK2MSFTNGP05.phx.gbl...
>> "Epinn" <someone@example.com.NO_SPAM> skrev i en meddelelse
>> news:%23wSSSyuwGHA.4220@TK2MSFTNGP06.phx.gbl...
>> >I see the need to use 101-111 for Subtotals; e.g. I want to ignore 
>> >hidden
>> > values.
>> >
>> > But, I don't see why I want to use Subtotals 1-11, instead of using the
>> > built-in functions like average, count, max etc. directly.
>> >
>> > I do understand that if I use Data>Subtotals, I can later modify the
>> > Subtotals function which may have 1-11.
>> >
>> > Please enlighten me.  Why would I choose Subtotals 1-11 over a more
> direct
>> > method?  Am I missing something obvious?
>> >
>> > Thanks.
>> >
>> > Epinn
>> >
>> >
>>
>> Epinn
>>
>> The important difference between Subtotal 1-11 and Subtotal 101 - 111 is,
>> that Subtotal 1-11 do not work on rows *hidden by filtering*, while
>> Subtotal 101 - 111 don't work on *any* hidden row (hidden by filtering OR
>> hidden manually).
>> The built-in functions you mention works on all rows, hidden or visible.
>>
>>
>> --
>> Best regards
>> Leo Heuser
>>
>> Followup to newsgroup only please.
>>
>>
>
> 


0
8/19/2006 9:20:26 AM
Reply:

Similar Artilces:

use multiple worksheets
I have a spreadsheet with 12 worksheets, one for each month. I would like to have cell names on each sheet that are identical to the others. I was able to create one sheet and then copy it entirely and the cells names copied properly, however, now I need to add new information, with new cell names and when I try to copy that portion of the worksheet to another, the name references change and won't be absolute to each worksheet. Help Please! Mike on the sheet you want to copy from do edit>replace, replace = with r= (replace the equal sign with lett_equal sign), that makes t...

Upgrade from CRM 1.0 to CRM 1.2
Hi we are new MS Cert Partner and just recived our action pact, it had CRM 1.0, we need 1.2 . Any thoughts ? call MS CRM sales at 800 456 0025 your account number is the phone number you registered the license under. They will ship one out but they are backordered till the end of Jan. They may also issue 1.2 keys as well. HTH GP "Phillip" <anonymous@discussions.microsoft.com> wrote in message news:61fe01c3e5d9$366330c0$a501280a@phx.gbl... > Hi we are new MS Cert Partner and just recived our action > pact, it had CRM 1.0, we need 1.2 . > Any thoughts ? ------=_...

Direct Push not pushing!!!
Hi Guys, i have exchange SP2 running and an imate jasjar with AKU2.0 installed. i have direct push working like a treat but not it only seems to sync when a heartbeat is transmitted...any ideas. have been through all the firewall setting and the knowledge base article about timeouts etc. i just cant work it out as nothing has changed yet it was working fine... Cheers JK Do you have the Security and Feature pack for the device installed?? Greetings "Jeremy Koch" wrote: > Hi Guys, i have exchange SP2 running and an imate jasjar with AKU2.0 > installed. i have direct pu...

RE: Setting up the Goal function in MOney 2004
Once I have setup my budget and money reconizes I have x amount of extra money every month I try and use the goal fuction by setting up furture large purchases and I keep getting the error that savings goal unreachable even though I have enough money to reach goal ...

Remote host said: 550 5.7.1
hi folks basically the situation is, I have an Exchange server which I have recently installed, MX records are in place on the ISP side, can send internally and externally. The problem arises when I send to any address in the domain and I get the following xxx.xxx.xxx.xxx does not like recipient Remote host said: 550 5.7.1.Unable to relay for j.bloggs@blahblah.com I have checked recipient policies and all is fine there, also I have used metaedit and deleted the LM\DS2MB key, then restarted the appropriate services Yet I am still getting this error when I try to email the domain in questio...

How do set up brochures on 8 1/2 x 14 paper
How do I set up a 4 panel brochure on 8 1/2 x 14 paper (landscape) in Publisher? Setup your printer first, if your printer doesn't say 8.5 x 14 it may have legal which is the same. If you are using a brochure template, once you select the template change the printer setup to legal, Publisher will automatically change the brochure to the correct size. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "TracksAll" <TracksAll@discussions.microsoft.com> wrote in message news:02E934C1-5FA1-44D3-9F03-78AC8F2B1ECB@micr...

Windows Media Player 11
Is it possible to embed the media player script or how can I place a cool video with singing on it on my personal web site? I have a video of Army beachboys that I want to place on my personal web site. Thanks. doug -- Oh woe is me... ...

Using wildcards in CString::Find() #2
Using wildcards in CString::Find() I’d like to use the wildcards(" * ", " ? " and other) in CString::Find(), but it does not work. Always it returns -1. Could you advice me ? I must do the complicated searching. Maybe there are other ways for the substring searching . Alur. ...

Deleting 1 out of 3 label sheets
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have an &quot;Avery Wizard&quot; label program copied from PC to AppleMac.It consist of 4 sheets with 33 labels <br> on each sheet. Sheet 2 is blank and I like to delete that sheet, but don't know how. <br> Can anyone please help. <br> John If I have understood correctly... Make a backup of your document, and ensure you know where the Undo button is. The chances are that this is a fairly ordinary 4-page document with a table (probably 11x3) on each sheet, in which case a simple appro...

Using an html signature
My partner has started a new job and has been emailed an html file of the email signature she should use. Consulting emails from other employees of the company, I see that this consists of a logo and personal information at the TOP of the email, with a standard disclaimer appearing BELOW the body text. I've opened the html file in Explorer, copied the contents and pasted them into a new signature, but this places the signature below the insertion point. I can reposition the insertion point between the signature block and disclaimer, but this is fiddly and means for each new e...

Delete PO using Econnect
Hi, I am trying to delete a PO using econnect but when i try to send the XML i get the following error: Microsoft.GreatPlains.eConnect.eConnectException: Sql procedure error codes returned: Error Number = 2969 Stored Procedure taPopPoDelete Error Description = PO Status is not NEW, PO can not be deleted Node Identifier Parameters: taPopPoDelete PONUMBER = PO00013400 <taPopPoDelete><PONUMBER>PO00013400</PONUMBER><RequesterTrx>0</RequesterTrx></taPopPoDelete> ---> Microsoft.GreatPlains.eConnect.eConnectExcept...

Replacing Contents of 1 Cell to Another.
My Spreadsheet looks like below. A B C 207 MDIM3030 3 TRUE 208 MDIM3030 FALSE 209 MDIM3034 1 TRUE 210 MDIM3034 FALSE 211 MDIM3038 6 FALSE 211 MDIM3039 4 FALSE I have sorted by Column "A" and done an EXACT on them. Now I need to know how to, for example on cell, B:207 (3) and move i to the blank cell below and so on through the Column. There ar hundreds of lines on the column, so I am looking for a...

Top x per criteria (Function)
Lets say I have 50 sheets, one for each state. I also have a sheet that has a years worth of data. So the data sheet has something like this (simplified) State Month Amount User RI Jan 5100 1234 RI Jan 5101 1235 RI Jan 5001 1244 RI Feb 5100 1234 RI Feb 5111 4343 RI Feb 5122 2343 RI Feb 5122 2343 RI Feb 4900 3234 RI Mar 5100 2344 TX Jan 5100 2345 TX Feb 8999 09...

Use this critical package from Microsoft Corporation
--loqupmfieinxbpmz Content-Type: multipart/related; boundary="equgsnfuxtguux"; type="multipart/alternative" --equgsnfuxtguux Content-Type: multipart/alternative; boundary="xgzsfgnzvgjkbv" --xgzsfgnzvgjkbv Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "September 1997, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install...

Required Resources
Hello, In scheduling resources, we would like to be able to schedule at LEAST one individual for a service call. Howerver, if we need to have two people scheduled the rules do not work. It seems that if you put a numeric value in, then you have to choose only that number of resources. I suppose I can do a schedule one resource, schedule two resource, schedule three resource etc group, but that seems kinda silly. Anyone have any other suggestions? On Aug 21, 9:52 pm, Nangard <Nang...@discussions.microsoft.com> wrote: > Hello, > > In scheduling resources, we would li...

How do I write a IF function formula?
I need to write a formula for the following but am confused as to how to do it. Can someone help? 1. determine gross pay in column F as follows: a. In cell F4, enter an IF function that applies the following logic and then copy it to the range F5:F10. If hours <= 40, then Rate * Hours, otherwise Rate * Hours + 0.5 * (Hours - 40) b. In cell I4, enter the IF function that applies the following logic: If (Gross Pay - Dependents * 38.46) > 0, then 20% * (Gross Pay -Dependents * 38.46), otherwise 0 What columns are used for Rate, Hours, Gross Pay and Dependents? "JulieA60"...

Sales for Outlook 07-11-05
The main CRM folder has lost it's homepage. Does anyone know the address I need to put in here. This is the only SFO I have installed and I really do not want to reinstall this app. Any help would be great thanks Try http://localhost:2525/_root/stage.aspx?url=%2FHome%2Fhome_home.aspx (Sorry, I don't have easy access to a 1.2 install, but I don't think we've changed it in 3.0.) If this is wrong, maybe someone who has 1.2 can provide the URL - it's under the Microsoft CRM folder's properties, on the "Home Page" tab. This posting is provided "AS...

Date() not functioning as expected
Hi, I'm working with Access 2003 at work, and we have reports that show projects with all associated milestones, and employees are supposed to enter the date of completion when the milestone is met. One of our reports is called 'Missing Dates', and it returns a list of all projects with associated milestones ONLY if there is a proposed milestone date between 1/1/2001 and the current date. The funtion is currently set up as Between #1/1/2001# And [Enter Date mm/dd/yy]. This works perfectly. I'm trying to get it where the user doesn't even have to enter the cu...

Problem while using Word as Outlook-mail-editor
I am using Outlook in windows-xp platfarm. Also I have selected WORD as the mail editor. It was working well. Now, if I open new-mail, word is not responding and it just hangs. I have to press ctrl-alt-del to come out from outlook. If I disable 'word' as editor, I can able to prepare new mails,can make reply & forward etc. kindly suggest a solution regards Selvaraj What version of Office are we talking about here? Does Word work without issues on its own? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Cr...

Outlook 2000 SR-1 #3
Is there supposed to be a drop-down menu next to the Send button, so I can choose which account to send emails from? I don't see it anywhere. Outlook is configured in Corporate|Workgroup mode TIA. That's why... Corporate/Workgroup mode in OL2000 doesn't support sending easily by selected account. "noname" <noname@news.net> wrote in message news:uXE_e.194632$wr.122194@clgrps12... > Is there supposed to be a drop-down menu next to the Send button, so I can > choose which account to send emails from? > I don't see it anywhere. Outlook is configured ...

Importing function
Is there any way to get the procedure address using GetProcAddress which is exported by __declspec(dllexport) not as extern "C" __declspec(dllexport) . I don't want to use full decorated (e.g. GetProcAddress(hPluginDll, "?Initialize@@YAPAVTMyProtocol@@XZ") ) name while loading function. I don't want to statically link the my dll. The 'decorated' name is the name of the function. Why would you not want to use it? - Alan "Manish Agarwal" <manishkrishan@hotmail.com> wrote in message news:OhlkDADjDHA.556@TK2MSFTNGP11.phx.gbl... > >...

Re-using Contacts/PAB file
A user had to have Windows XP re-installed. Outlook 2003, POP3 email. I saved the PST file and the PAB file. - Re-installed Windows. - Setup Outlook, and imported the old PST. Set the old PST as the default file, and removed the mailbox/PST that was created when I initially setup Outlook. - Found instructions on the net on importing the PAB file for her Contacts. Everything seem to go ok, it appeared to import, gave the status bar going across the screen, but when it was done, no Contacts. I thought maybe the profile was corrupt. Deleted the profile, created a new ...

XmlSchemaException in App.config using UIP Application Block
Does anyone know why I might be getting an XmlSchemaException in App.config configured as below to use the UIP Application Block? I get the following error for every element, sub-element and attribute within the <uipConfiguration> element: Message 1 Could not find schema information for the element 'uipConfiguration'. Message 2 Could not find schema information for the element 'objectTypes'. Message 3 Could not find schema information for the element 'iViewManager'. .... etc. etc. My App.config file: <?xml version="1.0" encoding="utf-8&...

Trace Dependents Type Functions Hushed
On Jan 7th at 8:33 PM I posted a question as follows (but received no help yet and fear it will be lost in antiquity so let me post it again): I received an EXCEL file from someone else and, when I open it, most of the functions on the auditing toolbar (trace dependents, etc) are all hushed for every cell. I thought that, maybe, the workbook or sheet was protected but it is not. What else could be prevenbting my access to these features? Thanks Tools>options>view and make sure the Objects have show all checked. If that doesn't help copy the sheet(s) to a new workbook.. ...

IIF Function 03-12-08
I need to use an If statement in a field in my query, here's what I need it to do: If field name "PYE" equals "12/31" then insert "1/1" or If field name "PYE" equals "11/31" then insert "12/1" or If field name "PYE" equals "10/31" then insert "11/1". So far I have this Exp: IIF([PYE]= "12/31", "1/1", "") it works for the PYE's that are 12/31, but I can't figure out how to use the or part. Any suggestions? Use nested IIFs like this --- IIF([PYE]= "1...