Subtotal - SUM, COUNT NUMBER, 'Live' Formula

OK, so I have a list of orders.

Some orders have just one row of data, some have two rows of data,
some have three, some have four, etc.

Each row represents either a delay or an activity. (So, obviously, an
order can have more than one of either.)

I need to aggregate into one row:
Order | Type of Order | Num. of Delays | Total Length of Delays

HERE'S THE MANUAL WAY I'M DOING THIS NOW:
======================================
My initial columns are:

Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
(D)

I added a "marker column" - Delay Length (E):
      =IF(C= a delay, D, "Activity")
In other words, if the Action Code indicates a delay, put the length
of delay in cell E. Otherwise, put in text.

Then I click the subtotal button, At Each Change in Column 'Order
Number (A)', Use Function.... SUM  to Delay Length (E). Then I click
it again, NOT replacing existing, and Use Function... COUNT NUMBER to
Delay Length (E).

So that gives me a table of
* First Work Order
       - Number of delays
       - Sum of delay time
* SecondWork Order
       - Number of delays
       - Sum of delay time
* Nth Work Order
       - Number of delays
       - Sum of delay time

THEN I copy Visible Cells and post into another worksheet and then
start manipulating.




So.... Is there a spiffy way to do this with formulas? I could crack
this if every order had the same number of rows, but it does NOT.











-----
EXAMPLE DATA TABLE
Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
(D) | Delay Length (E)

111 | Rebuild | Delay | 0:10 | 0:10
111 | Rebuild | Shipping | 0:15 | Activity
112 | New | Manuf | 0:50 | Activity
112 | New | Packing | 0:40 | Activity
112 | New | Delay | 0:12 | 0:12
113 | Scrub | Prep | 0:20 | Activity
113 | Scrub | Delay | 0:10 | 0:10
113 | Scrub | Delay | 0:16 | 0:16
113 | Scrub | Packing | 0:05 | Activity
113 | Scrub | Billing | 0:07 | Activity

EXAMPLE SUBTOTAL
Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
(D) | Delay Length (E)
111 Count 1
111 Total 0:10
112 Count 1
112 Total 0:12
113 Count 2
113 Total 0:26


What I'd like?
A worksheet (can I pivot this?) where it automagically takes my data
table and presents/reads:

111 | Rebuild | 1 | 0:10
112 | New | 1 | 0:12
113 | Scrub | 2 | 0:26


0
RJB
4/9/2010 5:04:35 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
589 Views

Similar Articles

[PageSpeed] 21

If your data is set-up correctly you can use a PivotTable or Subtotal 
feature;
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/subtotal.htm


-- 
Regards
Dave Hawley
www.ozgrid.com
"RJB" <rjbinney@gmail.com> wrote in message 
news:5b3bc4b0-649e-41d3-9a4f-2373a7c521db@30g2000yqi.googlegroups.com...
> OK, so I have a list of orders.
>
> Some orders have just one row of data, some have two rows of data,
> some have three, some have four, etc.
>
> Each row represents either a delay or an activity. (So, obviously, an
> order can have more than one of either.)
>
> I need to aggregate into one row:
> Order | Type of Order | Num. of Delays | Total Length of Delays
>
> HERE'S THE MANUAL WAY I'M DOING THIS NOW:
> ======================================
> My initial columns are:
>
> Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> (D)
>
> I added a "marker column" - Delay Length (E):
>      =IF(C= a delay, D, "Activity")
> In other words, if the Action Code indicates a delay, put the length
> of delay in cell E. Otherwise, put in text.
>
> Then I click the subtotal button, At Each Change in Column 'Order
> Number (A)', Use Function.... SUM  to Delay Length (E). Then I click
> it again, NOT replacing existing, and Use Function... COUNT NUMBER to
> Delay Length (E).
>
> So that gives me a table of
> * First Work Order
>       - Number of delays
>       - Sum of delay time
> * SecondWork Order
>       - Number of delays
>       - Sum of delay time
> * Nth Work Order
>       - Number of delays
>       - Sum of delay time
>
> THEN I copy Visible Cells and post into another worksheet and then
> start manipulating.
>
>
>
>
> So.... Is there a spiffy way to do this with formulas? I could crack
> this if every order had the same number of rows, but it does NOT.
>
>
>
>
>
>
>
>
>
>
>
> -----
> EXAMPLE DATA TABLE
> Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> (D) | Delay Length (E)
>
> 111 | Rebuild | Delay | 0:10 | 0:10
> 111 | Rebuild | Shipping | 0:15 | Activity
> 112 | New | Manuf | 0:50 | Activity
> 112 | New | Packing | 0:40 | Activity
> 112 | New | Delay | 0:12 | 0:12
> 113 | Scrub | Prep | 0:20 | Activity
> 113 | Scrub | Delay | 0:10 | 0:10
> 113 | Scrub | Delay | 0:16 | 0:16
> 113 | Scrub | Packing | 0:05 | Activity
> 113 | Scrub | Billing | 0:07 | Activity
>
> EXAMPLE SUBTOTAL
> Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> (D) | Delay Length (E)
> 111 Count 1
> 111 Total 0:10
> 112 Count 1
> 112 Total 0:12
> 113 Count 2
> 113 Total 0:26
>
>
> What I'd like?
> A worksheet (can I pivot this?) where it automagically takes my data
> table and presents/reads:
>
> 111 | Rebuild | 1 | 0:10
> 112 | New | 1 | 0:12
> 113 | Scrub | 2 | 0:26
>
> 

0
ozgrid
4/9/2010 5:13:11 AM
On Apr 8, 11:13=A0pm, "ozgrid.com" <d...@ozgrid.com> wrote:
> If your data is set-up correctly you can use a PivotTable or Subtotal
> feature;http://www.ozgrid.com/Excel/excel-pivot-tables.htmhttp://www.ozgr=
id.com/Excel/subtotal.htm
>
> --
> Regards
> Dave Hawleywww.ozgrid.com"RJB" <rjbin...@gmail.com> wrote in message
>
> news:5b3bc4b0-649e-41d3-9a4f-2373a7c521db@30g2000yqi.googlegroups.com...
>
> > OK, so I have a list of orders.
>
> > Some orders have just one row of data, some have two rows of data,
> > some have three, some have four, etc.
>
> > Each row represents either a delay or an activity. (So, obviously, an
> > order can have more than one of either.)
>
> > I need to aggregate into one row:
> > Order | Type of Order | Num. of Delays | Total Length of Delays
>
> > HERE'S THE MANUAL WAY I'M DOING THIS NOW:
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > My initial columns are:
>
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D)
>
> > I added a "marker column" - Delay Length (E):
> > =A0 =A0 =A0=3DIF(C=3D a delay, D, "Activity")
> > In other words, if the Action Code indicates a delay, put the length
> > of delay in cell E. Otherwise, put in text.
>
> > Then I click the subtotal button, At Each Change in Column 'Order
> > Number (A)', Use Function.... SUM =A0to Delay Length (E). Then I click
> > it again, NOT replacing existing, and Use Function... COUNT NUMBER to
> > Delay Length (E).
>
> > So that gives me a table of
> > * First Work Order
> > =A0 =A0 =A0 - Number of delays
> > =A0 =A0 =A0 - Sum of delay time
> > * SecondWork Order
> > =A0 =A0 =A0 - Number of delays
> > =A0 =A0 =A0 - Sum of delay time
> > * Nth Work Order
> > =A0 =A0 =A0 - Number of delays
> > =A0 =A0 =A0 - Sum of delay time
>
> > THEN I copy Visible Cells and post into another worksheet and then
> > start manipulating.
>
> > So.... Is there a spiffy way to do this with formulas? I could crack
> > this if every order had the same number of rows, but it does NOT.
>
> > -----
> > EXAMPLE DATA TABLE
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D) | Delay Length (E)
>
> > 111 | Rebuild | Delay | 0:10 | 0:10
> > 111 | Rebuild | Shipping | 0:15 | Activity
> > 112 | New | Manuf | 0:50 | Activity
> > 112 | New | Packing | 0:40 | Activity
> > 112 | New | Delay | 0:12 | 0:12
> > 113 | Scrub | Prep | 0:20 | Activity
> > 113 | Scrub | Delay | 0:10 | 0:10
> > 113 | Scrub | Delay | 0:16 | 0:16
> > 113 | Scrub | Packing | 0:05 | Activity
> > 113 | Scrub | Billing | 0:07 | Activity
>
> > EXAMPLE SUBTOTAL
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D) | Delay Length (E)
> > 111 Count 1
> > 111 Total 0:10
> > 112 Count 1
> > 112 Total 0:12
> > 113 Count 2
> > 113 Total 0:26
>
> > What I'd like?
> > A worksheet (can I pivot this?) where it automagically takes my data
> > table and presents/reads:
>
> > 111 | Rebuild | 1 | 0:10
> > 112 | New | 1 | 0:12
> > 113 | Scrub | 2 | 0:26

I forgot to mention it's 58,000 rows...
0
RJB
4/9/2010 6:16:21 AM
On Apr 8, 11:13=A0pm, "ozgrid.com" <d...@ozgrid.com> wrote:
> If your data is set-up correctly you can use a PivotTable or Subtotal
> feature;http://www.ozgrid.com/Excel/excel-pivot-tables.htmhttp://www.ozgr=
id.com/Excel/subtotal.htm
>
> --
> Regards
> Dave Hawleywww.ozgrid.com"RJB" <rjbin...@gmail.com> wrote in message
>
> news:5b3bc4b0-649e-41d3-9a4f-2373a7c521db@30g2000yqi.googlegroups.com...
>
> > OK, so I have a list of orders.
>
> > Some orders have just one row of data, some have two rows of data,
> > some have three, some have four, etc.
>
> > Each row represents either a delay or an activity. (So, obviously, an
> > order can have more than one of either.)
>
> > I need to aggregate into one row:
> > Order | Type of Order | Num. of Delays | Total Length of Delays
>
> > HERE'S THE MANUAL WAY I'M DOING THIS NOW:
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > My initial columns are:
>
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D)
>
> > I added a "marker column" - Delay Length (E):
> > =A0 =A0 =A0=3DIF(C=3D a delay, D, "Activity")
> > In other words, if the Action Code indicates a delay, put the length
> > of delay in cell E. Otherwise, put in text.
>
> > Then I click the subtotal button, At Each Change in Column 'Order
> > Number (A)', Use Function.... SUM =A0to Delay Length (E). Then I click
> > it again, NOT replacing existing, and Use Function... COUNT NUMBER to
> > Delay Length (E).
>
> > So that gives me a table of
> > * First Work Order
> > =A0 =A0 =A0 - Number of delays
> > =A0 =A0 =A0 - Sum of delay time
> > * SecondWork Order
> > =A0 =A0 =A0 - Number of delays
> > =A0 =A0 =A0 - Sum of delay time
> > * Nth Work Order
> > =A0 =A0 =A0 - Number of delays
> > =A0 =A0 =A0 - Sum of delay time
>
> > THEN I copy Visible Cells and post into another worksheet and then
> > start manipulating.
>
> > So.... Is there a spiffy way to do this with formulas? I could crack
> > this if every order had the same number of rows, but it does NOT.
>
> > -----
> > EXAMPLE DATA TABLE
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D) | Delay Length (E)
>
> > 111 | Rebuild | Delay | 0:10 | 0:10
> > 111 | Rebuild | Shipping | 0:15 | Activity
> > 112 | New | Manuf | 0:50 | Activity
> > 112 | New | Packing | 0:40 | Activity
> > 112 | New | Delay | 0:12 | 0:12
> > 113 | Scrub | Prep | 0:20 | Activity
> > 113 | Scrub | Delay | 0:10 | 0:10
> > 113 | Scrub | Delay | 0:16 | 0:16
> > 113 | Scrub | Packing | 0:05 | Activity
> > 113 | Scrub | Billing | 0:07 | Activity
>
> > EXAMPLE SUBTOTAL
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D) | Delay Length (E)
> > 111 Count 1
> > 111 Total 0:10
> > 112 Count 1
> > 112 Total 0:12
> > 113 Count 2
> > 113 Total 0:26
>
> > What I'd like?
> > A worksheet (can I pivot this?) where it automagically takes my data
> > table and presents/reads:
>
> > 111 | Rebuild | 1 | 0:10
> > 112 | New | 1 | 0:12
> > 113 | Scrub | 2 | 0:26

I don't see what in the Subtotal link you sent is any different from
what I'm doing...?

As for Pivots, I showed how my data was set up... Do you think a pivot
table would work? I'm also going to experiment with Access; it's a lot
of data. Access was having trouble reconciling data and text in same
column, and only doing math on the data and skipping the text - which
is easy-peasy for Excel.
0
RJB
4/9/2010 6:19:27 AM
It looks like a pivottable should work ok to me.

But it sounds like you haven't tried it (yet).

There may be formulas that accomplish the same thing (=countifs(), =sumifs() or
=sumproduct()), but I wouldn't want to use them with data this large.  I find
that pivottables calculate faster and only on demand (refreshed by clicking a
button).

You may want to experiment with pivottables with a subset of your data -- just
to make it easier to try things.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

RJB wrote:
> 
> OK, so I have a list of orders.
> 
> Some orders have just one row of data, some have two rows of data,
> some have three, some have four, etc.
> 
> Each row represents either a delay or an activity. (So, obviously, an
> order can have more than one of either.)
> 
> I need to aggregate into one row:
> Order | Type of Order | Num. of Delays | Total Length of Delays
> 
> HERE'S THE MANUAL WAY I'M DOING THIS NOW:
> ======================================
> My initial columns are:
> 
> Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> (D)
> 
> I added a "marker column" - Delay Length (E):
>       =IF(C= a delay, D, "Activity")
> In other words, if the Action Code indicates a delay, put the length
> of delay in cell E. Otherwise, put in text.
> 
> Then I click the subtotal button, At Each Change in Column 'Order
> Number (A)', Use Function.... SUM  to Delay Length (E). Then I click
> it again, NOT replacing existing, and Use Function... COUNT NUMBER to
> Delay Length (E).
> 
> So that gives me a table of
> * First Work Order
>        - Number of delays
>        - Sum of delay time
> * SecondWork Order
>        - Number of delays
>        - Sum of delay time
> * Nth Work Order
>        - Number of delays
>        - Sum of delay time
> 
> THEN I copy Visible Cells and post into another worksheet and then
> start manipulating.
> 
> So.... Is there a spiffy way to do this with formulas? I could crack
> this if every order had the same number of rows, but it does NOT.
> 
> -----
> EXAMPLE DATA TABLE
> Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> (D) | Delay Length (E)
> 
> 111 | Rebuild | Delay | 0:10 | 0:10
> 111 | Rebuild | Shipping | 0:15 | Activity
> 112 | New | Manuf | 0:50 | Activity
> 112 | New | Packing | 0:40 | Activity
> 112 | New | Delay | 0:12 | 0:12
> 113 | Scrub | Prep | 0:20 | Activity
> 113 | Scrub | Delay | 0:10 | 0:10
> 113 | Scrub | Delay | 0:16 | 0:16
> 113 | Scrub | Packing | 0:05 | Activity
> 113 | Scrub | Billing | 0:07 | Activity
> 
> EXAMPLE SUBTOTAL
> Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> (D) | Delay Length (E)
> 111 Count 1
> 111 Total 0:10
> 112 Count 1
> 112 Total 0:12
> 113 Count 2
> 113 Total 0:26
> 
> What I'd like?
> A worksheet (can I pivot this?) where it automagically takes my data
> table and presents/reads:
> 
> 111 | Rebuild | 1 | 0:10
> 112 | New | 1 | 0:12
> 113 | Scrub | 2 | 0:26

-- 

Dave Peterson
0
Dave
4/9/2010 11:42:16 AM
On Apr 9, 5:42=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> It looks like a pivottable should work ok to me.
>
> But it sounds like you haven't tried it (yet).
>
> There may be formulas that accomplish the same thing (=3Dcountifs(), =3Ds=
umifs() or
> =3Dsumproduct()), but I wouldn't want to use them with data this large. =
=A0I find
> that pivottables calculate faster and only on demand (refreshed by clicki=
ng a
> button).
>
> You may want to experiment with pivottables with a subset of your data --=
 just
> to make it easier to try things.
>
> If you've never used pivottables, here are a few links:
>
> Debra Dalgleish's pictures at Jon Peltier's site:http://peltiertech.com/E=
xcel/Pivots/pivottables.htm
> And Debra's own site:http://www.contextures.com/xlPivot01.html
>
> John Walkenbach also has some at:http://j-walk.com/ss/excel/files/general=
..htm
> (look for Tony Gwynn's Hit Database)
>
> Chip Pearson keeps Harald Staff's notes at:http://www.cpearson.com/excel/=
pivots.htm
>
> MS has some at (xl2000 and xl2002):http://office.microsoft.com/downloads/=
2000/XCrtPiv.aspxhttp://office.microsoft.com/assistance/2002/articles/xlcon=
PT101.aspx
>
>
>
> RJB wrote:
>
> > OK, so I have a list of orders.
>
> > Some orders have just one row of data, some have two rows of data,
> > some have three, some have four, etc.
>
> > Each row represents either a delay or an activity. (So, obviously, an
> > order can have more than one of either.)
>
> > I need to aggregate into one row:
> > Order | Type of Order | Num. of Delays | Total Length of Delays
>
> > HERE'S THE MANUAL WAY I'M DOING THIS NOW:
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > My initial columns are:
>
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D)
>
> > I added a "marker column" - Delay Length (E):
> > =A0 =A0 =A0 =3DIF(C=3D a delay, D, "Activity")
> > In other words, if the Action Code indicates a delay, put the length
> > of delay in cell E. Otherwise, put in text.
>
> > Then I click the subtotal button, At Each Change in Column 'Order
> > Number (A)', Use Function.... SUM =A0to Delay Length (E). Then I click
> > it again, NOT replacing existing, and Use Function... COUNT NUMBER to
> > Delay Length (E).
>
> > So that gives me a table of
> > * First Work Order
> > =A0 =A0 =A0 =A0- Number of delays
> > =A0 =A0 =A0 =A0- Sum of delay time
> > * SecondWork Order
> > =A0 =A0 =A0 =A0- Number of delays
> > =A0 =A0 =A0 =A0- Sum of delay time
> > * Nth Work Order
> > =A0 =A0 =A0 =A0- Number of delays
> > =A0 =A0 =A0 =A0- Sum of delay time
>
> > THEN I copy Visible Cells and post into another worksheet and then
> > start manipulating.
>
> > So.... Is there a spiffy way to do this with formulas? I could crack
> > this if every order had the same number of rows, but it does NOT.
>
> > -----
> > EXAMPLE DATA TABLE
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D) | Delay Length (E)
>
> > 111 | Rebuild | Delay | 0:10 | 0:10
> > 111 | Rebuild | Shipping | 0:15 | Activity
> > 112 | New | Manuf | 0:50 | Activity
> > 112 | New | Packing | 0:40 | Activity
> > 112 | New | Delay | 0:12 | 0:12
> > 113 | Scrub | Prep | 0:20 | Activity
> > 113 | Scrub | Delay | 0:10 | 0:10
> > 113 | Scrub | Delay | 0:16 | 0:16
> > 113 | Scrub | Packing | 0:05 | Activity
> > 113 | Scrub | Billing | 0:07 | Activity
>
> > EXAMPLE SUBTOTAL
> > Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
> > (D) | Delay Length (E)
> > 111 Count 1
> > 111 Total 0:10
> > 112 Count 1
> > 112 Total 0:12
> > 113 Count 2
> > 113 Total 0:26
>
> > What I'd like?
> > A worksheet (can I pivot this?) where it automagically takes my data
> > table and presents/reads:
>
> > 111 | Rebuild | 1 | 0:10
> > 112 | New | 1 | 0:12
> > 113 | Scrub | 2 | 0:26
>
> --
>
> Dave Peterson

Thanks, it's been years since I'd used Pivots, so before I dug in and
got frustrated, wanted to make sure that was the right direction.

I will practice first, of course!
0
RJB
4/9/2010 5:23:29 PM
Reply:

Similar Artilces:

phone that works with live calendar?
I have been using google for my business for a few years and hate it as it sometimes dups the event. I started to use windows live mail and love that the calendar syncs up from the desktop to online. But one issue I have found is I can not log on via the web when on the road to view the calendar. Is there a phone that allows this? I don't care about syncing my phone with the calendar if I can view it online that would be fine. I am also due for an upgrade on my phone so if there is a certain phone that will work that would be great. I am currently using a motorola cliq, that...

Add text to formula?
I have this formula kindly given to me in one of these Excel ngs: =COUNTIF(E:E,"You Won!*")&":"&COUNTIF(E:E,"You Lost*") The challenge is that any custom text I add to the cell formatting doesn't show up in the cell. Would it be possible to add something like this text below to the above (but also including line breaks)?: **************************************************************** Score --------- You:Computer = Formula result of: =COUNTIF(E:E,"You Won!*")&":"&COUNTIF(E:E,"You Lost*") *********************...

Counting Dates of Occurrences
I am developing a spreadsheet to show the date and time an event has occurred at a location. The cells indicating date and time are custom formated to display mm/dd hh:mm. The first occcrrence is shown in column G, the second occurence in column H, the third iin column I, and so on. This works fine, but I need to count the number of occurrences shown in each column. Can you help? Thanks =COUNT(A2:A200) assuming you are using excel dates and times -- Regards, Peo Sjoblom "wally" <wally@discussions.microsoft.com> wrote in message news:6EEBFCEB-6FAC-40F2-B8B7-04...

Creating macro for slightly complicated formula
I'm really new to the whole macro business so I really need help. I'm trying to create a macro to solve this equation D =(Sum from 1 to Nt ((X - Xmean)^2/Nt))^0.5 ......I hope it's clear. I don't know hope to format here. Anyway, I have a range of Xs that are Nt in number and Nt is unknown so it will have to be found first and this has to be in the code. I'm trying to avoid having another column containing the values of (X - Xt)^2/Nt. I just want D to be displayed in a designated cell, like F2. This code will be used for multiple workbooks, all the data al...

Adding formatting to inline formula..
Is there a way to merge text and a cell value such that the cell value has a particulat format. For example, suppose I want to display the following in a cell: Less 5% of (contents of another cell) = I could put something like the following in the cell: ="Less 5% of " &A1 "=" However, is there a way to format the inserted value from A1 as bold or italics or a particular number format? TIA, -GHB Bold,italic etc will not work in a formula but will in just text. In the formula bar, just highlite the desired and format as desired. But you can use TEXT (look in help...

How suppress chart points
I want to suppress chart points for a cumulative line graph of monthly telephone costs where the particular month's values have not yet been entered but there is a formula in the cell which currently returns null but will return the value once it is posted on the accountmaster sheet. Hi, Use NA(). The will cause the data marker to be omitted. Note the line will be interpolated between valid data points. Something like this, =IF( <test> , <value> , NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "sarcastix" <sarcastix@di...

Show/Hide Functions/Formulas
For some reason the keyboard shortcut (Ctrl+`) does not toggle show/hide function. Selecting Tools>Formula Auditing>Formula Auditing mode works fine. I am wondering if it is because I have regional (keyboard) settings set to UK English Extended. I know the `(grave) key works and have tried it with both Ctrl keys with no luck. Help please. "Smirnoff" <someone@nospam.invalid> wrote in message news:73972258-4ADC-4E40-B17B-A4E64E2B4B7F@microsoft.com... > For some reason the keyboard shortcut (Ctrl+`) does not toggle > show/hide function. > > Selecti...

How to increase the number of columns.
I need more columns in excel (I now it sounds crazy). If there is no solution, than how to transform columns in rows? Please � I�m desperate�����. :( -- istancutu ------------------------------------------------------------------------ istancutu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35749 View this thread: http://www.excelforum.com/showthread.php?threadid=555288 Nel post news:istancutu.29xf9n_1151186102.1322@excelforum-nospam.com *istancutu* ha scritto: > I need more columns in excel (I now it sounds crazy). If there is no > solution, th...

count ?'s
So i was wondering if there was a way to use the count w/ specifi letters like in 1 cell can i have it count all the a's, all the b's-g's. know i can you count(a) and it will count text but can i specif specific text for it to count -- Message posted from http://www.ExcelForum.com You can use the "*" wildcard. =COUNTIF(A1,"*a*") OR If you would like to use a specific cell to designate a character, so that it could be easily changed: =COUNTIF(A1,"*"&$B$1&"*") -- HTH, RD ============================================== Please ...

Formula to get date from text string
This is what the cell currently looks like: [10/01/09 11:30PM] I would like the formula to return only: 10/01/09 Does anyone know what formula I should use? Any help would be greatly appreciated. Thanks!! Sara Here's one way: =3D--MID(A1,2,8) though this will only work if the date is in the normal format for your region (does it mean 10th January 2009, or 1st October 2009 ?). A safer way might be: =3DDATE(2000+MID(A1,8,2),MID(A1,5,2),MID(A1,2,2)) or: =3DDATE(2000+MID(A1,8,2),MID(A1,2,2),MID(A1,5,2)) depending on the answer to my earlier question. ...

Is there a formula result that will not appear on a chart
I would like a chart that updates for daily sales statistics over the course of the month without dragging a formula, such that I enter the day's sales, and the chart updates for the new day (to be viewed against a target line). I do not want the chart to show the "zero" value for days which have not yet occured. I tried using a formula, but all formula results register on the chart (i.e. if the formula result is blank, or space, or a word, the chart plots a point at zero). I need it for a cummulative sales number day by day. Thanks! Jon Peltier has information on c...

sum function not actually summing??????
Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as ...

sum command do not display the result eg:=sum(c5:c8)-total not
sum command do not display the result eg:=sum(c5:c8)-total not display the result in the cell even when sigma is clicked the same What is displayed instead? If you see the formula, then format the cell as General (or anything but text) and reenter that formula. If you see the wrong answer, then maybe you have calculation set to manual. Tools|Option|calculation tab is how you'd change it in xl2003 menus. If that's not it, then maybe your values in those cells (C5:C8) aren't really numbers. Reformat that range as General (not text) and reenter the values. abbbalu wr...

Specifiying Successive Page Numbers for Workbooks with Multiple Tabs/Pages
Hi, Does anyone know how to make a workbook with several tabs print with successive page numbers from the first page of the first tab to the last page of the last tab? Thanks Hi Select all of the tabs and then print the lot in one go!! -- Andy. "M.P." <mpam@challiance.org> wrote in message news:103ca01c43f3b$2afcafc0$a501280a@phx.gbl... > Hi, > Does anyone know how to make a workbook with several tabs > print with successive page numbers from the first page of > the first tab to the last page of the last tab? > Thanks I have tried that before, but each...

Extract numbers from cell with Text and Numbers
Hello All, I hope someone can help. I have a column of cells. Each cell contains some text, and then a number in parenthases, then a comma, and then come text, and then a number in parenthases, This repeats probably as many as 5 times, though some cells have fewer then 5. I want to somehow, if possible, extract JUST THE NUMBERS. If I can automatically get a total so that I can average the total (dividing by a number in another column, but in the same row as the numbers extracted) that would be great. I am most concerned with not having to manually enter the numbers again. If anyone ha...

Sum #2
thanks Frank, I like your sumif solution -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14501 View this thread: http://www.excelforum.com/showthread.php?threadid=271816 ...

Limit number of CPUs?
Is there a way to set the number of CPUs SQL Server will use (SQL 2005 Enterprise)? I know you can do it per query, but is there a way to set it across the board (e.g., in the registry or in the configuration manager)? Thanks. Can you explain more of what your intent is? There is a setting called MAXDOP (Maximum degree of Parallelism) at the instance level. But you need to understand what it does and if that is really what you want. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Neil W" <neilw@netlib.com> wrote in message news:uAWNEedbKHA.16...

Formulas over multiple worksheets
Is it possible to apply a formula to multiple worksheets? The scenario a user has 52 worksheets, one for each week of the year. In A1 of th first worksheet, she's entered a date. She'd like to use a formula t title A1 in each of the 52 worksheets like A1+7, A1+14...etc. so sh doesn't have to enter 52 titles -- Message posted from http://www.ExcelForum.com Well,my stupid way goes like this: On Sheet2, A1, enter: =Sheet1!A1+7 On Sheet3,A1,enter: =Sheet1!A1+14 ... The initial time can be done manually or via vba codes(sorry I don' know how), but afterwards, she can change al...

Sum formula that stays constant
What I would like is to have a format that a date will be entered into A1. Cell A2:A... will have what will be the number of items that came in at a certain point of the day. In cell C2 I would like it to use a count formula if the date is whatever is in A1 and when the date in A1 changes, I would like the summed data to remain in C2 if that makes any sense. Thanks for the help. Todd "Todd" <anonymous@discussions.microsoft.com> wrote in message news:2a2f01c3afa4$c9fc7a50$a601280a@phx.gbl... > What I would like is to have a format that a date will be > entered i...

How to write this 4 condition nested formula?
I'm trying to figure out how to write a formula to satisfy the followin multiple conditions: If cell v4>1 and cell x4="FR",-1 and If cell v4=1 and cell x4="FR",3 and If cell v4>1 and cell x4="CL",2 and If cell v4=1 and cell x4="CL",-3 So I have 2 cells I'm working with here and 4 conditions that I want t assign: either a -1,3,2, or -3, depending on whether the user types th text FR or CL into cell X4. Thanks and happy holidays. Fran -- bort ----------------------------------------------------------------------- bortz's Profile: htt...

Excel : Counting on two columns
THIS IS AN MICROSOFT EXCEL QUESTION: URGENT Suppose I have two columns "A" and "B". "A" "B" Error Open Error Closed Not Error Open Error Open Error Closed ------------------ ------------------ ----- -> Represents blank rows.. How will I find Error which are open in columns 0-7 (including blank rows) Try putting this into C1 =SUMPRODUCT((A1:A65535="Error")*(B1:B65535="Open")) Hope it helps, -Bo -- Message posted from http://www.ExcelForum.com ...

count days in a date range
I am trying to count the number of days within a given date range but leave out saturday's and Sunday's. Is there a way to do this? Using the function Days360 seems to be the right path, and counts all the days between the range, but I am not sure how to customize from there, if it is even possible. cp Hi if your dates are in a and B1 use =NETWORKDAYS(A1,B1,list_of_holidays) Note: the Analysis Toolpak Add-in has to be installed for this -- Regards Frank Kabel Frankfurt, Germany cp wrote: > I am trying to count the number of days within a given > date range but leav...

Removing formulas
I have 2 worksheets, on worksheet 2 I created figures by using formulas using refrences to the other worksheet (worksheet1). I now need to use the new figures on worksheet 2 to create a new worksheet using different formulas to create yet a different set of figures. Every time I try to use the new formula on worksheet 2 it just puts a 0 in the cell and says something about Circular formula. I have tried to copy and paste the worksheet into a new excel window but it just copies the formula with it. I want to be able to get rid of all the formula on wsheet2 but still leave the figures...

Formula for getting values in File-Properties-Custom
Is there a formula for retrieving values stored in the variables in File-Properties-Custom? Thx ...

Why am I getting #VALUE! when working with a TIMEVALUE formula?
This formula is working on other pages within the same workbook, yet it does not work on a newly inserted worksheet. NOTE: this orginial workbook was created in an early version of Excel (95 or 98), now using 2000. Hi what is your exact formula and what is in the referenced cells? -- Regards Frank Kabel Frankfurt, Germany TIMEVALUE ERROR wrote: > This formula is working on other pages within the same workbook, yet > it does not work on a newly inserted worksheet. NOTE: this orginial > workbook was created in an early version of Excel (95 or 98), now > using 2000. The for...