Is there a way to use an IF or SUMIF function in a
calculated field in a pivot table? When I try, I get all
0s as the result.
Can you type a small sample of the pivot table layout and data, and
explain what you'd like to calculate with a SUMIF formula?
Jim C. wrote:
> Is there a way to use an IF or SUMIF function in a
> calculated field in a pivot table? When I try, I get all
> 0s as the result.
Excel FAQ, Tips & Book List
> Is there a way to use an IF or SUMIF function in a
> calculated f...Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help
with a complex calculation.
In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells
B1-B100, I have percentages ranging from 0%-100%. I want to break out the
cells in A1-A100 in several groups, like follows: $1-$99, $100-$199,
$200-$299, and so on up to $1000. Then, I need to calculate the average
percentage for jobs in those categories. So, for the category of $1-$99,
lets say there are two cells with amounts in that range, A1 and A2. Their
percentages in B1 and B2 are 40% and 6...Money using wrong date to calculate ROI YTD
I have opened a new file in Money to track investments for
a stock club. I entered all transactions, which go back
to Jan. 2002. When I look at performance for the account,
the ROI YTD is not accurate, as Money is using values for
the stocks going back only 6 months form the date I enterd
the transactions rather than the values as of 1/1/03.
What to do?
In microsoft.public.money, Brooks wrote:
>I have opened a new file in Money to track investments for
>a stock club. I entered all transactions, which go back
>to Jan. 2002. When I look at performance for the account,
&g...Analytical Accounting #4
Has anyone encounter issue with multilevel query with more than 3 accounting
class? and how you found Analytical Accounting in general?
...How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion
dates by a certain percentage per year, how do I write the formula. For
example if my project starts May 26, 2010 and lasts until may 26 2014 I want
to add escalation to midpoint at 4% per year.
...If/then to print text.
I want to use a formula so that when a value over zero is put into one cell,
a line of text prints in another cell. I know it is an if then function, but
I've forgotten the exact formula and the way to encode the text message in
> I want to use a formula so that when a value over zero is put into one cell,
> a line of text prints in another cell. I know it is an if then function, but
> I've forgotten the exact formula and the way to encode the text message in
> the formula.
=IF(A1>0,"print this text","prin...using A5 paper as a booklet in Publisher 2007 i.e 4 A6 sides
In 2003 I made greeting cards on A5 card by using the booklet I cannot make
this work in 2007
Use one of the Avery A4/A5 templates. C9355 works... If you use the A5 folded
down to A6 it makes a small publication.
Mary Sauer MSFT MVP
"mumbles" <firstname.lastname@example.org> wrote in message
> In 2003 I made greeting cards on A5 card by using the booklet I cannot make
> this work in 2007
> mum...Validation list based on criteria
I want to create a data validation list based on certain conditions.
Let me try to explain by example.
A1 = oranges B1 = oranges
A2 = apples B2 = no record
A3 = pears B3 = pears
A4 = pears B4 = pears
A5 = grapes B5 = grapes
A6 = apples B6 = no record
B:B value is based on a relative formula determined by the value in A
A:A can have duplicate values
I want to create a validation list based on B:B where the formula result is
“no record” The list needs to be populated with the relative value in A
Since B2 and B6 = “no record” then the validat...Unable to calculate STD DEV for any value with 6 or more digits to the left dec.
I am unable to obtain the standard deviation for any value
with six or more digits to the left of the decimal.
The values are coordinates, usually with six or seven
digits to the left of the decimal and three to the right.
Found four types of standard deviation in the Microsoft
Excel help menus.
1) STDEV - will work with values up to five places left of
the decimal point.
2) STDEVA - will work with values up to five places left
of the decimal point.
3) STDEVP - will work with values up to five places left
of the decimal point.
4) STDEVPA - will work with values up to five places left
My computer crashed... I'm recovering my files from my
hard drive, but I never did backups to save my email. I
had thousands of emails in Outlook... is there any way to
By the way, I had to connect my old hard drive to another
PC and access my hard drive in that manner.
>My computer crashed... I'm recovering my files from my
>hard drive, but I never did backups to save my email. I
>had thousands of emails in Outlook... is there any way to
Look for file(s) with a *.PST extension. Location...Text color
When I open windows explorer and click on any word or folder way on the left
, it is highlited in a very light blue, how can I change that color to say
like dark blue like windows classic .
You would have to revert to a classic theme and then change the color for
"selected items" in the advanced color properties of Personalization/Windows
Color and Appearance. It's not customizable when running an aero theme.
Best of Luck,
Rick Rogers, aka "Nutcase" - Microsoft MVP
Windows he...Full-Text Indexing #3
Is there a way to enable full-text indexing in an outlook
client for Public Folders when the client is working
...Creating a chart with text not numbers
How do I create a chart that has text values (freshman, sophomore, junior,
senior) instead of numbers (1, 2, 3, 4)?
I have a column labeled "What is your class standing?" and below it is 166
rows where people selected "Freshman," "Sophomore," etc. I want to create a
bar chart that counts how many people selected each of the four classes.
First of all, you will have numbers to plot. The words are your category
Read how to turn a column of words like this into a histogram:
- Jon...on_change() event for text fields
Is there really no way to assign an event to a text field?
We want to be able to have the format saved correctly for
phone numbers when they're entered in (###)###-####
I've read some threads about this already and it looks
like it cannot be done unless you write an app to
constantly check and replace the phone numbers within the
I can do this but it would be nice to know if there was
any simpler way of going about this.
Thanks in advance.
nope, not at all,.....only picklists
its kinda stupid too because under text boxes they have the ability to
select &qu...Pasting email address into text field
I am attempting to copy/paste several rows of data from an excel spreadsheet
into an Access form. I receive an error: "The value you have entered into
this field is invalid" when the data contains an email address. The email
address field is set for text with a field length of 255 characters in the
DB. I can copy the individual email address into the field without any
problems. the problem occurs only when I am copying the whole row or rows of
data from the excel spreadsheet into the Access DB.
Any ideas about getting around this problem?
this is exactly wh...Help with "base unit" in chart axis
I have two charts. In one, when I go to "format axis" I can enter the min
and max scale as dates and the entry remains as dates when I come back. I
can also set the "base unit" a major and minor grid line in days, weeks,
The second chart, I do not have a base unit drop down box, and although I
can enter dates in the max and min in date format and they work, when I come
back the entries are in days since the start of time (38353 in 1/1/2005)
And I cannot specify the units of major and minor grid lines, they are just
units. Which makes it awkward for minor gi...Re: Is there a way to HIDE a row based on a value of a cell ?
Dim rng As Range
Dim i As Long
If LCase(Sheets("Ctrl").Range("A1").Value) _
<> "yes" Then
For i = 5 To .UsedRange.Rows.Count Step 2
If Not rng Is Nothing Then
Set rng = Union(rng, .Cells(i, "A"))
Set rng = .Cells(i, "A")
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
I want to be able to have a resource, say 'electrician', with 5 available for
the day shift and 2 available for the night shift. This would allow e to
just assign the 'electrician' resource and have project level based on unit
If I setup 'electrician Day' and 'electrician Night', then I need to
carefully select which to use and if the schedule shifts it is a nightmare.
Any thought on how to do this?
On Jan 6, 10:43=A0pm, John K <John K...@discussions.microsoft.com>
> I want to be able to have a resou...Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and
deposits. Current balance is determined by subtracting withdrawals and
adding deposits to the balance in the line above.
Suddenly I have started getting a cell entry
in the current balance cell.
A typical entry in the cell is for G43 would be:
=G42-E43+F43 and the sudden new result is #VALUE.
All three columns have cells formatted as numbers.
On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote:
> I have a spreadsheet with three columns: Current balance, withdrawals, and
> deposit...howto: convert from xml to text ?
If anyone knows how to convert a input xml (getting from a url querry)
and convert it into a flat, csv text file ??
hmm... but can you point some examples...??
"Dimitre Novatchev" <email@example.com> wrote in message
> This can be done using XSLT.
> Dimitre Novatchev.
> http://fxsl.sourceforge.net/ -- the home of FXSL
> "Gopinath Varadharajan" <firstname.lastname@example.org> wrote in message
> news:exXDxHzQDHA.402...msg template. Text sent not what typed!
I have had the strangest issue with Outlook. I sometimes use msg files to
send emails to colleagues. Normally I have a template set up and I may
change the to, cc, subject and body of the email slightly to reflect the
topic of the email. However the bulk of the email remains the same.
The other day I was sending an email and had amended the template (not saved
as need to use the template again), and sent it out. Whilst the to,cc and
subject fields were correct the text i had entered in the body of the email
had gone and other text had been entered and my signature had dissappea...Inventory #4
The item shows 4 in stock 4 available 0 allocated 0 everything else.
When I try to put 4 on a sales order it shows 4 in inventory 3 available; I
found 1 at another location
I have run inventory reconcile, 0 errors on the report.
There no other sales with this item on an order, only on a quote.
This is where it gets odd. I varianced the 4 items into stock. ( I have a
printout it says 4) where would I look to see where the missing item would be?
Did you validate the site? The item may be distributed on two sites, one
holds the three pieces and another one ...Loading PivotCache directly from a text file
According to the Excel online help, the PivotCache.Connection property can be
set to "the path to and file name of a text file". I tried setting it to the
path and file name of a .csv file and it returned an error. Is there a way to
load a PivotCache directly from a flat file without having to load into a
spreadsheet or database? (The flat file has more than 1 million rows.)
in MP 2007-
I have selected all columns, all rows. I cleared all formats.
I go to set text styles. Set all items to automatic then,
I set critical path items to Fuchsia or lime and OK...
these tasks are still black (automatic) and will not retain that fuchsia
My 2003 schedules this works great!
Is fuchsia just not working as a color in 2007?
Huh, that would appear to be a bug. Red works fine. Fuschia doesn't seem
to stick for some reason. I can replicate that on my machine with no difficulty.
I guess you should avoid fuschia. It's such a 90s color anywa...Extend text to next cell
I am trying to get my text to run over to the next cell only in display not
actual value of that cell. So if A1 says, "How now brown cow.", it should
show over cells A2 and A3. However, I also need the carriage returns to be
picked up. If I use the "Wrap Text" property it only shows the sentence in
that cell. If I don't use it I get the  (brackets) in place of the carriage
Maybe you could merge those cells (A1:A3 or did you really mean A1:C1?)
But be aware that merging cells causes trouble with sorting, pasting, and lots
of other stuff.