Calculated field in Pivot Table #5
I am having problems with a calculated field in a pivot table.
I believe the challenge is the in the way pivot tables summarizes then
10/1/05 task 1 = 2.00 hours
10/1/05 task 2 = 3.00 hours
10/3/05t task 3 = .50
thus, as it is occuring now (2.00+3.00+.50=5.5 * 50.00) * 3
Instead what i am looking for in the above example is 5.5 hours *50.00/hr.
Any idea, how i can accomplish this?
...Text to Columns...
Does anyone know the official function of the menu option
called "Text to Columns..." which is found under
the "data" option on the menu bar? How does it work?
What is it supposed to do? Answers to these questions
would be appreciated.
It allows you to separate text in a cell or column of cells either
by position (fixed) or based on a delimiter, and place the separated
data in one or more columns. It can also do some rudimentary data
manipulation, including parsing dates, converting numbers to text,
Th...Creating a summary from existing spreadsheet data ...
Looking for a solution to this little problem in Excel ...
Cells A1 to C7 contain the main data. What I would like to do is select all
of the items with a Condition of A and place them in a little summary as
shown in A9 to C12. Any ideas how to automate this please?
A B C
1 Pt No. Desc Condition
2 1 Apple A
3 2 Pear A
4 3 Banana B
5 4 Orange A
6 5 ...How do i use sumproduct if my lookup table is alpha or alpha num?
Can I use sumproduct formula if my table is as follows ?
Yr End Rating
Mid Yr Rating A B C D
A same 1 down 2 down 3 down
B 1 up same 1 down 2 down
C 2 up 1 up same 1 down
D 3 up 2up 1 up same
No, but you can if the cells contain numbers like -2, 0, or +2, where the
cell might use a custom format such as
0 "up";0 "down";"same"
to show display values like you show in your example.
"Angie" <Angie@discussions.microsoft.com> wrote in message
>...how do I add a goal line for a bar chart?
I have 8 bars that represent 0% to 100% standing vertically. I want to have
a line drawn horizontally that is at the 80% mark. Is there an easy way to
add a line? I have looked at all of the info and have yet to achieve the
process. I changed one bar i named "Goal" to a line, but it was just that, a
small line the width of the other bars and it did not show very well. I just
want a simple bar to show the goal.
Jon Peltier, Peltier Technical Services, Inc.
Advanced Excel Co...Text and Data for Axis Lables
Hi, I have a chart that I am working on and in it all of my X axis
lables are in my excel workbook except 1. How can I define the lables
using the data contained within my workbook and manually enter a text
lable for the one exception.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
You have to put them all into the worksheet. Could you just rearrange
the data to allow this?
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/...can you crop or select parts of a visio page
You can crop using the Crop tool on the Drawing toolbar
(View/Toobars/Drawing). For non-rectangular shapes (and for rectangular,
but you may as well just crop the image) you can define a custom fill
pattern based on an image and then select that custom fill pattern for your
Hope that helps.
"kev1n" <firstname.lastname@example.org> wrote in message
Nice job, John!
I've article-ified this on Visio Guy.
Folks can read ...Counting selected cells
How do I count how many cells the command Range(Selection,
Selection.End(xlToRight)).Select has selected?
Msgbox Range(Selection, Selection.End(xlToRight)).Cells.Count
> How do I count how many cells the command Range(Selection,
> Selection.End(xlToRight)).Select has selected?
After running that line it would just be
"Wendy" <Wendy@discussions.microsoft.com> wrote in message
I am working in a new company, they are using a excel with a 20 charts for a
20 charts from different data sourse. But the problem is every month, I need
to delete the first column data and add the new column in the end for the new
data for the new month. Then I need to update the charts, each chart has 10
series, the old way to do it is to update every series manually. I am trying
to use the programme to update the chart automated.
Is there any one can help me on this.
...Map charts in Excel 2000?
Just noticed that my Excel 2000 contains a map feature, for using maps
as charts. However the map set available does not include any world map,
or European maps. How can I extend teh maps available?
not sure on the answer to the question, but don't get too excited about the
feature - in version 2002 it has been removed although you can open
workbooks containing maps, however, in 2003 (according to help, and i've not
tested it) you can't open workbooks containing maps.
"Johan Myrberger" <johan.myrberger@remov...putting what is selected from an unbound combobox from main to sub
I am getting the below error
Invalid reference to the parent property
Me!Job = Parent.cbojoblocautonum.Column(1)
I am trying to automatically fill in the job(number) in the subform based
off of what is selected in the combo box(and unbound control) of the main
form - the job number is
the second field in the combo box on the main form and based on the
selection want that value put into the subform.
I am sure sytax is wrong somewhere - just not sure where??
The combo box of jobsloacautonum is actually an unbound combo box the
thing bound on ...Pivot Table Counting
I am having trouble trying to calculate fields through a pivot table.
Basically, I have a data set that looks like this:
Record Number State Value
1 CA 0
2 CA 12
3 CA 15
4 NJ 0
5 NJ 10
I want to set up a pivot table that will show 2 columns, one with th
total number of records and one with the total number of records wit
non-zero values. Both of the...3rd axis chart
i would like to create a complicated chart, with what i
believe would be 3 axis'. the x axis has 5 categories. the
y axis also has 5 categories, but they are split into two
parts, a percentage and a total number. i want the graph to
illustrate the percentage, and show the value for the
number, but i haven't been able to make this work.
finally i want to average the percentages from each
category on the x axis and create 5 separate lines that
illustrate each of those averages on the graph.
i'm using NT, excel 2000
any help would be much appreciated.
Excel doe...Print on two sides
I have an application that printed multiple pages and I have a printer
(BROTHER 5370DW) that allows this. I select the printer using the
CommonDialog1.ShowPrinter command and then click on the printer
preferences to select two-sided printing. However, when the actual
printing takes place is does not print two-sided.
Is there something in VB print methods that would over-ride this option
or not allow it?
On Jun 15, 2:40=A0pm, M Wade <nowh...@columbus.rr.com> wrote:
> I have an application that printed multiple pages and I have a printer
> (BROTHER 5370D...Change the size of the data labels
How do I change the size of a data label in a pie chart? I am getting
unusable wrapping of text when I have plently of room if I could only resize
the label box. All I've been able to do is move the label.
Unfortunately, Excel does not trust its users to resize data labels, chart titles,
or axis titles. If you need this level of control, you need to replace the data
labels with text boxes.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
> How do I change t...how do i link spreadsheets on common data points?
I am trying to link two separate spreadsheets with one common data column.
...How do I copy/paste subtotals without all the extra data?
I am trying to either do a vlookup or copy and paste the subtotals without
getting all the data in the hidden rows?
number of ways you can do this...i had the same question earlier.
1. highlight the subtotals results that you want to copy. hit F5
special and "visible cells only" then ctrl C, which sould highligh
with white lines the subtotals you want to copy. then ctrl V t
wherever you want. it'll get rid of all the data in the hidden row
and leave only the subtotal results.
2. copy and paste from Excel into Word and back again. That should d
3. Copy a...What is the meaning of error
I ported my .mdb project to .adp, but now I get that message when I try to
save a record from a form. Anyone know the cause?
I'm experiencing the same problem, which is really nasty because it happens
sometimes and sometimes not. After receiving the error (#1020) "column data
is unavailable" MS-Access crashes and generates a problem report (Access 2002
SP3 with MSDE SP4 on Windows XP SP2). I reduced the problem to one form with
3 fields and one table as the data source for the form - nothing complex -
entering a couple of values and Access crashes again.
I hope somebody ...Remove Data From Field
I have records in a TABLE that look like the following:
Jklebowski (Logged Out)
lawilliams (Logged Out)
I am looking for a way (within a QUERY) to remove the (Logged Out) from each
If it helps at all....The words (Logged Out) are always at the END of the
record....and always follows the Username and a single space
Can anyone help me with the syntax required to eliminated this "piece" of
Lookup the Split() function. Split the field at the "(" character.
HTH - Bob
>I have records in a TAB...pie/bar-of-pie charts
Please forgive two questions by a rank beginner:
1. Is it possible to have a central pie chart surrounded by a number of
either pie or bar charts, each of the satellite charts giving further
information about only one slice of the central pie chart?
2. Is it possible to change the colour of some columns only in the simplest
sort of column chart, that is, where the columns aren't clustered or
1. You can, of course, do pie chart that breaks out one
series in more detail, but I am not aware of any way to do
multiple "satellite" charts (unless perhaps this might be
p...How do I format a picture fill in my pie chart?
I've put a picture into a 3-D pie chart in Excel by clicking on the wedge I
want, going to fill effects, and inserting a picture. However, I don't want
the picture to show on the vertical sides of the chart - I want that just to
be white. There's a menu in the picture fill effects tab that has an Apply
to: menu and check boxes next to Sides, Front and End, but I'm not allowed to
Is there any way to take the picture off the sides of a 3-D pie chart while
leaving it visible on top? Thanks.
...find first non error value in a column
I have column filled either with #N/A or a number. I need a formula to
"extract" the first numeric value in that column.
the answer must be 15.
Thanks for your time.
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
"Mika" <email@example.com> wrote in message
> I have column filled either with #N/A or a num...How to write a VBA code to select certain rows based on certain conditions
I need help to write part of a VBA code in Excel. I am trying to do
calculations on rows that meeting certain requirements, but I do not
know how to established that in VBA. I know that if I use the
worksheet itself, I can do something like SUMIF() function. But, when
I put the same function in the VBA editor, it returns error. A simple
version of my data:
Group No Member No Amount spent
1 1 10
1 2 3
1 3 4
2 1 6
2 2 1
3 1 12
...line chart over time
My data looks like this:
Mon Tue Wed Thu Fri Sat Sun
06/26/06 5 4 3 6 3 1 1
07/03/06 1 2 4 9 0 0 2
07/10/06 0 1 1 1 2 2 1
07/17/06 8 6 4 3 0 1 0
07/24/06 5 5 3 0 0 2 3
I know how to chart data against time if I have just one column of
dates and one of data, but how can I chart the above data as one
Thanks for any help,
Maybe you could try to get the original raw data instead of this summarized
The row and column headings are hidden on my spreadsheet--can't find how to
Message posted via http://www.officekb.com
Check Row and Column Headers.
"jeremy via OfficeKB.com" wrote:
> The row and column headings are hidden on my spreadsheet--can't find how to
> Message posted via http://www.officekb.com
Message posted via http://www.officekb.com