Pivot Table Report: Getting average of difference of two date columns
I have data in excel sheet as:
WORK_ID PROJECT START_DT END_DT
1001 P1 1/1/2006 1/10/2006
1002 P1 1/25/2006 2/20/2006
1003 P1 3/15/2006 3/20/2006
I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...How to lock a column
I want to lock column B in my worksheet (meaning I want it hidden and no one
should be able to unhide it). And also, I have two tabs in my worksheet that
I don't want anyone to see but they need to be in that file.
Message posted via http://www.officekb.com
These would depend on the security of a protected worksheet/workbook.
The bad news is that worksheet/workbook protection (under tools|protection) in
excel is not made for this kind of thing. It can be broken in a matter of
If you really have secure data that other shouldn't see, don't put it in
excel...Cells not adjusting to new Column
Currently, I have the following formula in Column J of the audit sheet:
If I insert a column in the audit sheet, the formula in Column J is now in
Column K, but the formula stays the same.
I would want the formula to adjust to:
All three sheets (audit, upload, and control) are in the same workbook.
Is there a way to make the formulas adjust automatically when a column is
deleted or ...Stacked Line-Column Graph
I was wondering if anyone could help me make a Line-Column
Graph that has stacked columns.
There are to be 3 data series that are stacked column
format and 1 that is line format
Create a stacked column chart from all the data.
Right-click on the series that you want as a Line
In the shortcut menu, choose Chart Type
Select the Line chart type, and one of the subtypes
Jon Peltier has information on creating your own combination charts:
Lucas Guarino wrote:
> I was wondering if anyone could help me make a...vLookup but choose either of 2 columns
I have a scenario where I want to lookup a value in column A but would like
to choose either column 4 or 5 from the resultant row in a drop down based on
other criteria I know. Is something like that possible? If so is it possible
to have a heading on each column to better identify which is which?
How would I attempt such a manouver?
Will look for a match between B1 of sheet2 column A.
If A1 = "asdf", then column 4 (D) will be returned. Otherwise, column 5 (E)
will be returned.
You can make that ...arrange columns alphabetically in Excel
Hie need help a bit stuck on this pleaaaaaaaaaaase!
Im a bit of a dodo, so plese help this dodo out.
Select the columns you want to "SORT".
Data -> Sort
Options -> Sort left to right. Click OK
Select the row that you want to sort on.
> Hie need help a bit stuck on this pleaaaaaaaaaaase!
> Im a bit of a dodo, so plese help this dodo out.
...Returning row and column headers
I have a spreadsheet that contains soccer player positions for our
team. I have the positions layed out in a range that has the field
positions as column headers and the period played as the row headers.
The names of all the players are filled in for each of the four
periods and for each position. I would like to be able to extract from
this range, each boys position and period by using a formula. i.e.
Jake is playing center forward in the second period. I would like to
have a seperate range that has the players name and all 4 periods
listed and be able to ectract from the data range, the posi...Why is my tab key moving my cursor from column A to column k?
In all of my excel worksheets, when I push Tab, my cursor goes straight from
Column A to Column K or the next column not shown to the right of the sheet.
From there, if I hit Shift-Tab It always goes back to column A, even if
that's not where I started out. Please help, it's driving me crazy!!!
Turn off Lotus 1-2-3 Transition options
Tools, Options, Transition (tab), turn off all transition options
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvp...How do I set up moving averages in exel
...Using arrow keys to move between cells
Sorry for dumb question, but how do I move between cells w/o using my
mouse and rather using arrow keys? Do you have use any of shift, alt,
ctrl keys at same time?
If pressing the arrow keys are not moving you between cells, check and see
if scroll lock is on on the key board, here is some info from help
Move one cell up, down, left, or right.
Move to the edge of the current data region.
Move to the beginning of the row.
Move to the beginning of the worksheet.
Move to the last cell on the worksheet, in the bottom-most us...E2K3
We currently have a mixed mode environment with Exchange 5.5 on NT 4.0
and Exchange 2003 on Windows 2000. We have completed the migration of
our mailboxes and public folders to E2K3 and are in the process of
decommissioning our 5.5 Site.
We have a policy setup on our mail stores to send a warning when a
user reaches 95MB and to prohibit sending at 100MB. We also have
standards in place regarding the storage group/mail store location of
our users on the E2K3 servers.
The first problem we are experiencing is ESM is reporting a mailbox
size that is significantly larger than what Outlook 2002 ...Move message to folder
How can I get a message that I create from scratch go to a folder (other
than "Sent Items") automatically after I send it?
Hi. Me again.
I don't think the above question was very clear. Let me explain.
I can make a rule that will check the message after I send it and move a
SINGLE COPY of that message to a certain folder. This is fine if I create
the message by typing in the email address from a business card, for
example. But if I "reply" to a message I get TWO COPIES of it in the folder
because I've also checked the box that says:
"In folders other than ...Modifying the "Required Attendee" lookup columns
Is there a way to modify which columns appears in the lookup view of the
multi-select "Required Attendees" lookup on the Appointemnt? This special
windows does not seem to take the usual "account" or "contact" lookup view.
Right now it only shows the name and full name of the account or contact.
Did you happen to get the solution to your query as i am with the same
problem and looking for someone to help....:(
"Fred Bang" wrote:
> Is there a way to modify which columns appears in the...Deleting/Moving E-Mail Messages
My company is currently useing MS Office XP, 2000, and 97
(most people are using 97). When some users try to
delete or move messages in Outlook (it does not matter
which release) they receive a message that it can't be
done. I think it may be because they have many messages
on the Exchange Server and their account is very large,
but I'm not sure.
Does anyone have any information on how to correct this
problem? Users are having a difficult time deleting
messages or moving them into another folder.
Thanks for any help you can give.
...Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between
Sort order doesn't matter actually.
Data is coming by the use of simple cell reference of "another sheet
Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558
View this thread: http://www.excelforum.com/showthread.php?threadid=27144
you may use the following addin to filter out blank cells:
&g...ranking numbers in a column
I have 100 (100 rows) with different numbers all in one column. I have
ranked them in another column by largest to smallest in the past. The 100
numbers change from time to time. I would like the rankings to automatically
update. I wish not to sort. Is there a formula?
Try the RANK Function.
If my comments have helped please hit Yes.
> I have 100 (100 rows) with different numbers all in one column. I have
> ranked them in another column by largest to smallest in the past. The 100
> numbers change...Printing column area
I would like to print a column area in repeated pages (not the whole column). Any ideas?
It's not totally clear to me what you're asking. But if it is, how do you
limit the print area to a specific range, first select the range and then
use File, Print Area, Set Print Area.
Well that is how to print a certain range but what I am asking is that I want a certain part of the column to be repeated in all the pages. By clicking Page Setup/Sheet and ticking the option "columns to repeat at left" it takes the whole column and not a part of it. I just wa...sort in multiple columns
how can i sort lists of names in several columns eg: c3:c15,
e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3 and the
last (...Z) end in e31?
Does this mean you have 13+13+14+14=54 names spread over 4 ranges?
If yes, I'd add a temporary worksheet, copy those ranges to A1:A54 and sort
there. Then copy|Paste the rows back to the 4 areas in that range (if you need
> hi guys,
> how can i sort lists of names in several columns eg: c3:c15,
> e3:e15,c18:c31,e18:e31. so that the first names (A.....missing columns
Please help, I have an a 11 column spreadsheet, I need to copy this into a
powerpoint presentation but when I copy it onto a blank sheet the last 2
columns are not transferred. I tried to copy the blank form and it went ok
but after putting text in it does not work. Please explain a cure in simple
terms. Thanks in advance
PowerPoint has its limitation when it comes to copying an Excel table or
Here is a link that will explain a lot better than me.
"greg" <greg@ford .com> wrote in message
news:bonmo...Regarding Sorting Columns
I made a worksheet of a list of books for a classroom library. The first
column I named "Title"; the second one- "Author"; the third one "Genre";
....and so on. Now, once I finished, I clicked the first column (A- "Title"),
and then clicked the A to Z function; this sorted all the Titles in
alphabetical order, but not any of the other columns. Now, what I then did
after undoing this is to highlight all the rows of the columns to the right
of "Title", and then clicked the A to Z function, this then put all the
Titles in alphabetical or...Columns to rows #2
I need to take information from a column and put it in a row. Th
column for each customer is 8 lines and then repeats itself. I need t
know how to get like the information from all line 1's into the sam
MileHigh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=503
View this thread: http://www.excelforum.com/showthread.php?threadid=26934
Not sure I see the scenario exactly but look into
(A) Copy followed by Edit|Paste Special->Transpose, OR
(B) the TRANSPOSE functi...Copy column header to next column, delete & delete every nth colum
Well SNPid Call SamID Well SNPid Call SamID Well SNPid Call SamID
A01 rs10090154 C 1 A01 rs1016343 C 1 A01 rs10486567 AG 1
A02 rs10090154 C 2 A02 rs1016343 TC 2 A02 rs10486567 AG 2
A03 rs10090154 C 3 A03 rs1016343 TC 3 A03 rs10486567 G 3
My workflow was dramatically streamlined thanks to a previous question
posted and the generous reply and help of members here. I have another step
in my data-cleanup that requires reformatting my excel table and I would be
very appreciative if I can get some help. I myself do not have the Visual
Basic abilities to do this.
Here is the table structur...Flipping column and row headings
I need to flip the column and row headings.
Is this possible?
Sorry...I found my answer.
> I need to flip the column and row headings.
> Is this possible?
...excels 2007 columns run right to left
i have a problem, the columns in my excel blank template run
right-left...i have tried everything but they all result in changing the
direction of the text within the cells but not the direction of
columns.......i need help urgently, have a project coming up and am
running out of time.i have attached a screen from the worksheet.
|Filename: Untitled.jpg |
+---------------------------------...column and rows meet
Aviation chart for takeoff field length.
I have 2 columns and 1 row.
1st column are elevations. Sea level to 14,000 ft in 1000's of ft.
(Sea level, 1000,2000, etc.)
2nd column are weights . 14,000 to 21,000 lbs in 1000's of lbs
(14,000, 15,000, etc)
first row across the top are temperatures. -40C to 50C in 10c
increments (-40, -30, etc).
I will have cells labeled Elevation, WT, and Temp which will be
manually inputed (keyboard). end result will be distance required for
Below is a fraction of the actual table.
ALT / FT WT / LB �C -40 -30
14000 2800 2900
15000 2840 2940