Column To Rows
I often get lists of contacts that are generated in one long column. They are
often may be 4 or 5 rows per record. In order to be able to use the data I
need to transpose the rows in each record (one column) to new columns. It may
include Company Name in Row A1 and contact name in Row B1 and perhaps Phone
or Web Site in C1 etc. The records are always the same amount of rows. I have
tried copy, transpose, and text to rows and anything else I can think of. Can
anyone make a suggestion ?? Thanks in advance Tony Pass
If desired, send your file to my address below. I will on...Fill bar/column charts with patterns in Excel 2007?
Is there anyway that I can fill a bar/column chart with patterns as I can do
with Excel 2003?
I know that MicroSoft removed this feature in Excel 2007. But I don't
understand why they do it. Lots of academic journals prefers black and white
charts than shaded or charts with gradient. Also, it's hard to tell one
series of data from another series if they are printed in black and white.
Any help is appreciated.
Excel 2007 doesn't use these patterns anymore. Did you really find them
useful in Excel 2003? They printed at such a fine resolution that different
patters were ind...Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank
cells, dashes and < entries. Is it possible to average only the numbers
ensuring that the divsor is the number of cells that contain number entreis
rather than all cells containing an entry (eg for below example answer should
On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote:
>I have a column of data as illustrated below than contains numbers, blank
>cells, dashes and < entries. Is it possible to average only ...Insert Columns Diabled!!!!
Can somebody help me with this. On any workbook that I open the
"Insert-Columns" menu item is disabled. This also effects the right
click context menu as well. I have even opened excel with the standard
workbook with 3 worksheets in it and its still disabled.
As I pressed the post button I realized that I had not included what
version of Excel. This is Excel 2003.
> Howdy All,
> Can somebody help me with this. On any workbook that I open the
> "Insert-Columns" menu item is disabled. This also eff...Average of Averages
Another seemingly simple process which I can't figure. :)
I have a list of averages, and I want to get the average of them.
I need a formula to average something like the above.
=AVERAGE(A1:A4) is one possible solution.
Hope it helps.
goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838
View this thread: http://www.excelforum.com/showthread.php?threadid=513937
It really depen...averaging less than values
How do I average a column of numbers where less than values are calculated as
In the average I want the<2 to = 2. So the answer should be 3
Assuming source data as posted in A2:A7
Paste this in say, B2,
then press CTRL+SHIFT+ENTER to array-enter the formula:
Adapt the ranges to suit
Downloads:17,500 Files:358 Subscribers:55
> How do I average ...line column Pivot Charts
Access 2003 does not support combined Line/Column Charts, but Excel does. Has
the capability been added to Access 2007?
Access does support a series displayed as a bars and another series as line.
MS Access MVP
Help me support UCP http://www.access.hookom.net/UCP/Default.htm
"John" <John@discussions.microsoft.com> wrote in message
> Access 2003 does not support combined Line/Column Charts, but Excel does.
> the capability been added to Access 2007?
...column headings in numbers vs letters
For some reason all my excel files now display columns as numbers rather than
letters. How can I change back to letters? Why are all my old files also
now reading in numbered columns?
This is a setting of Excel, not of your files.
Tools>Options>General, uncheck R1C1 Reference style
Microsoft MVP - Excel
"garyflood" <firstname.lastname@example.org> wrote in message
> For some reason all my excel files now display columns as numbers rather
> letters. How...Adding a column...very bizarre behavior
I have an MPP file given to me by one of my users. When she tries to add a
column to the Gantt Chart view, we get a little screen flicker, then several
new columns appear on the view. I don't know what to tell her. It doesn't
do this on any other view, and it only does this in this particular MPP. If
I open another project file, it doesn't behave this way.
Is it possible that this file has just been corrupted somehow?
This is a multi-part message in MIME format.
I have an interesting situation. I basically inherited an older
database that is used to enter dates, usage and charges for accounts.
The situation is that we are attempting to do averages filtered by
account, and averaged against the number of days that data has been
entered. So, for example, we had 7 months of data entered, our query
should total the number of days in the seven months (x), then average
that with the total usage number (y). This data currently resides in
the same table labeled "data" in the form of from and to dates
(6/1/1999 to 7/1/1999) and usage (32...Sum of column #2
I have a worksheet containing 25 columns
In the column A I have categories and the other columns B to Z
Looks like this
PLB 1 2 3 1 2 2 2 3 ..................
ADD 3 4 2 4 1 5 6 4 ..................
AVL 2 1 4 2 3 7 1 4 ...................
PLB 2 3 2 1 5 2 4 3 .....................
I am looking for a formula that will add each columns for the same
So the result for the categorie PLB should be 1+2+3+1+2+2+2+3 (first
row) + 2+3+2+1+5+2+4+3 (last row) = total = 38
ADD = 29
AVL = 24
thanks for helping
=SUMPRODUCT((A2:...Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts
weekends, or only business days?
The average lead time is calcualted as follows: Enter the average number of
days that pass between the time you place an order with this vendor and the
time you receive the order. If you're using Purchase Order Processing, this
field will be updated each time a shipment or shipment/invoice is posted in
Purchase Order Processing. The average lead time is calculated using the
following formula: [(Number of Receipts) * (Average Lead Time) + (Received
Date - Ordered Date)] / (...How to automatically fill a column?
How can I express this?
Assume 2 columns, A and B.
In A, a col of numbers: 1, 2, 3, etc.
In B, now empty, I need to have a col of words that describe in words
what the numbers stand for: apples (for No 1), oranges (for No2),
lemons (for No 3), etc.
I already have the col with the numbers, Column A. I need a way to
tell Excel to fill in col B with the word "apple" wherever it sees a
"1" in col A. And to fill in col B with the word "oranges" whereever
it sees the number "2" in col A. etc, etc.
Is this do-able?
Think VLOOKUP wou...Average Function
I have a formula in a cell that takes the average Sales of
the 12 rows (for Jan to Dec)
like so: =Average(A1:A12)The problem is that for example,
since therea are no sales figures from August to Dec, the
average is underestimated because it averages it over the
entire range of cells (though they are zero from Augus to
How can I make this Average function to be smarter and
only average those months that the sales is not zero
(August to Dec is Not BLANK but its zero)
Average is a very simple thing: SUM/COUNT. So
should do for all years with 12 mont...How to end a column, type a paragraph/title, then start back?
I am typing a document that has titles for each section, but the section
themselves are in 2 columns. I can't figure out how to make the column "end"
so I can type the new title (lengthy) and then restart. I set the column
back to 1 and that works while typing the title. However, when I restart
columns, it puts my title in the first column (even though there are hard
returns between). Clear as mud? Any suggestions?
You need a continuous section break at each point where you want to change
the number of columns. This is covered in the "Multicolumn sections ...Combining 2 columns to 1
I have 2 columns A and B. In column A i have one word like "hello". In
column B i have a time "19:00" even though the column is pure
What i would like to do is combine these two columns to one column, so
that i have column C "hello 19:00".
I try to do this using the formula =A1&" "&B1 in column C, but when i
do that i get the result "hello 0,791666666666667" which is not what i
How can i do this?
Trying to work out the best way to get an average answer based on a
set of answers
The answer will be 1 - 10 across a number of questions
B C D E F
Answer Q1 Q2 Q3 Q4
1 1 1 3 3
2 1 0 1 2
3 0 1 3 3
4 1 0 1 1
5 1 1 6 7
6 2 2 5 2
7 2 3 3 4
8 2 1 7 6
9 1 2 1 3
10 2 2 8 7
Total 13 13 38 38
Average ? ? ? ?
What is the best way to get the average response to each question.
i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure
this is right....find formula that will look up a value in a specific column and .
I am looking for a formula that will find a certain text value (open) in a
column (J) and pull that row (or row #) to a new sheet. Does anyone know if
there is a way to do that?
Providing the data starts in J1 the formula below will return the row
number. Bear in mind that a formula can only return a value, not move data.
You may also be looking for a VLOOKUP, it was just the way your question was
phrased that made me return a row number
Microsoft MVP - Excel
...Adding columns #4
I have a spreadsheet with values in column E an I want to add them to column
F with the first f2=e2.So as I add a value to column E the value in F changes
to sum all with the blank cell showing 0.
Column E2 Column F2
Okay, so in F3, type: =if(isblank(E3),"",F2+E3)
"bbc1" <email@example.com> wrote in message
> I have a spreadsheet with values in c...Maximum number of column
I am working on a program that would import 400 columns of data into
Microsoft Excel. The data is a text file in comma/tab delimited
format. Excel has an allowable range of at most 256 columns. I was
wondering how would I goes about importing this text file into excel.
I'm using visual basic to progam the code. How would I split the file
up so that I would be able to import it into Excel or how would I go
about overcoming this limitation, beside having to utilize a text
editor and row by row shorten the number of columns. The typical text
file is about 2000 rows of data. I appreciat...Sum on top of stacked columns #2
Is it possible in some way to add the sum of series on top of each
column in a stacked column diagram?
I am often using this feature in ThinkCell for my powerpoint slides.
However, need to insert a stacked column graph from Excel 2007 into
Word 2007. Currently, I've been using a text box with a reference.
However, the reference in a text box is static, which makes it a
source for error when inserting or deleting rows in Excel.
You can add a new series with the total sum and then:
Change the chart type to line ONLY for this series.
Format the series without lines or markers...Newbee
I am working with a sheet that has several columns that contain "True" or
"False" data in the cells. I have been able to change all "True" to "Yes"
and "False" to space, wherever they exist in the sheet. I now want to
change any "Yes" values, within a column to a space. The column is "G" and
has "CardReq" in the first cell position.
I use the following code to make the current changes and would like to add
the new code to this routine. Can anyone assist??
' setup the filename and open it...
...Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly.
I have a spreadhseet:
How can i calculate the average of every month between 1999 and 2005?
In other words, i need something which looks up all the weeks in a
particular month (ie Jan 2001) and averages them out into a table:
1999 2000 ......... 2005
Jan 2.45 6.42
Feb 3.64 etc etc.
I was thinking vlookup, but don't know how to average in a vlookup.
I would ...tagging rows to columns???
I just created an excel catalog for my cd jukebox as I loaded the tray (300
discs). Now I want to alphabetize column A (artist) and have column B
(album title) follow column A. Is there a way to do this?
Thanks for all help.
Try on a duplicate copy of your sheet:
Assuming your lists are in cols A and B,
data from row2 down, viz.:
Select cols A and B
Click Data > Sort
Check "Header row" under "My list has"
under "Sort by" are: Artist > Ascending
For the sample data ab...column list
can someone help? i need to export a list to a word-
wrapped with commas text document
My spreadsheet has 500 such numbers:
Try this - you'll need to select the cells in your worksheet first, and
obviously subsitute an appropriate path and filename in the save as line
Hope this helps
Dim ListCounter As Integer
Dim ConCatString As String
ConCatString = ""
For ListCounter = 1 To Selection.Cells.Count
ConCatString = ConCatString & CStr(...