Pivot Table Calculated Formula If statement

Hi all,
  In a pivot table I am trying to add this formula:

In the insert calculated field-->Formula field i type
=IF('FIELD NAME'="STRING TO COMPARE", 1,0)

Any Idea why this does not work?

0
4/5/2006 3:15:37 PM
excel 39879 articles. 2 followers. Follow

2 Replies
575 Views

Similar Articles

[PageSpeed] 33

The calculated field operates on a sum of the field, and the sum of a 
text string is zero. The zero result won't equal the "String to 
compare", so the calculated field will return a zero.

Perhaps you could do the calculation in the source data instead, and add 
that field to the pivot table.

Dan McCollick wrote:
> Hi all,
>   In a pivot table I am trying to add this formula:
> 
> In the insert calculated field-->Formula field i type
> =IF('FIELD NAME'="STRING TO COMPARE", 1,0)
> 
> Any Idea why this does not work?
> 


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/6/2006 12:26:53 AM
I was able to achieve what I wanted by rearranging the data, but
none-the-less:

I thought that calc fields would do a count if it was non-numerical
data?


I maybe wrong.....

Thank you for the reply though.

0
4/6/2006 1:50:44 PM
Reply:

Similar Artilces:

calculate date of birth with an end date and age
I have a sheet with a retirement date which will be the x birthday, and the age (x) of the client at that date. I need to get a date of birth for each of these people. Anyone know a formula that will achieve this?? Thanks Worker Retires Age DOB Fred 15-Mar-10 65 15-Mar-45 "Fred" is in A2, retirement date in B2, age in C2, DOB in D2 In D2: =DATE(YEAR(B2)-C2,MONTH(B2),DAY(B2)) This assume the retirement date exactly corresponds (month & day) to Fred's DOB I have used a dd-mmm-yy date format to save confusion (international vs USA) but the dat...

apply the formula sign
Hi, I=B4ve imported data from other application and some cells have some formulas like 10*12 but without the =3D sign. How can I apply the =3D sign to all the cells quickly without going one by one so that it calculates the formula. Example, =3D10*12, should be 120. Thanks!! Copy this UDF to a general module in your workbook. Function EvalCell(RefCell As String) Application.Volatile EvalCell = Evaluate(RefCell) End Function In an adjacent cell enter =EvalCell(cellref) Gord Dibben MS Excel MVP On Thu, 28 Jan 2010 07:12:14 -0800 (PST), canvas <spyele123@g...

Audit Trails
Hi All, I have noticed that when using "Audit Trails", you cannot add tables with text columns (notes fields) into the Auditable tables section of the Audit Trail Maintenance screen. How can i get around this problem? I would have thought that all tables could be made auditable? regards, TP ...

Look up data from a table in a text box
Hello, I have a text box on a form and I would like data to be viewed from a table. In the properties of the text box on the control source I place =[Date Ran]![DT_TM_RAN]. I thought this would show the data in the DT_TM_RAN field in the text box. But when I open the form it shows #Name? Can someone please direct me in the right direction to fix this issue. Thanks for your time. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 I think the Dlookup function is what you want. Help on that function should tell you e...

Creating formula for timeline
I am trying to find a formula in Excel that will help me manage the following report: I have 2 columns...the first gives a list of months...ex. Jan, Feb, March, etc. I want a formula that will automatically populate the second column with a month that is 3 months past the first column...ex. 1st column is Jan 2nd is April. 1st column is Feb 2nd is May... Is this possible. I don't want specific dates in the month...just the month itself. Lynne This should do the trick for you. 'Borrowed' from a post a month or so back Remember they are text so no date calculations can be...

MATCH and OFFSET with dynamic range
Hello, I would like to do the following with excel Formula. these are the information Sheet 2 A M 01/12/2001 24 01/06/2002 23 01/12/2002 25 01/06/2003 52 01/12/2003 53 there are already 2 range define in sheet 2 rngDate, rgnValues How to obtain something like this with Excel formula and not VBA Sheet1 Jun Dec tot 2003 52 53 105 2002 23 25 48 2001 24 24 I tried to use Match and Offset (but I have a problem with the offset) Any help will be really apreciate :D Ina I noticed that ...

Converting Access 2000 table to Access 97
How do you link an Access 2000 table to an Access 97 database? Thanks for your time. ...

If statement...copy values of cells on another worksheet.
I need a formula that looks at a drop down list on a different worksheet and if it is "Sold", then the data values from T2:AC2 would show up on the other worksheet. Is this possible? I figured it out. "heater" wrote: > I need a formula that looks at a drop down list on a different worksheet and > if it is "Sold", then the data values from T2:AC2 would show up on the other > worksheet. Is this possible? I still need help. I need it to look at S2:S86 (drop down list), if it is "Sold", then T2. I beleive I have to write a formula for e...

Copying tables with hidden cells
I have a table in Excel 2000 with hidden columns and I want to copy it to Word without the hidden columns reappearing. Is there anyway I can do this? Not to worry. I've worked out how to do it eventually. >-----Original Message----- >I have a table in Excel 2000 with hidden columns and I >want to copy it to Word without the hidden columns >reappearing. Is there anyway I can do this? >. > ...

Copy formula between workbooks
I have a number of workbooks which are identical in form. Each uses multiple sheets. If a make a formula change in one and then copy the formula to a different workbook the new formula references the original workbook and sheet. How can I turn off this "feature" so that the copied formula will not make any reference to the workbook, only the sheet? Thanks -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- Hi Ken, To enter the same formula i...

Paste special function lacks possibility for copying formulas in '
When creating worksheets, you often need to copy a specific function from one area to another in the same worksheet (or to another), without changing the original formulas. This is not possible as a 'paste special funtion'. When copying formulas in 'paste special' mode, the relative references in the formula are still changed. In short, when copying the 'formula' =A25' to another cell, the formula in the copy to cell should still be '=A25'. Suggestions of changing the orginating formula from relative to absolute reference (e.g. $A$25) before copying ...

IF statement to change font?
Is there an IF statement or other way to change the appearance of data? I would like the font color to change if an output goes over a said amount. As an example if we had an hours total out put and wanted it to change to red when over 40. Thanks in advance, Louis You can use conditional formatting to do this: Assume the cell of interest is cell A1 If you're using Excel 2007 Select cell A1 Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format Enter this formula in the box below: =AND(COUNT(A1),A1>40) ...

calculating by color
Is there a way to calculate various total depending on the color of the data entered scenario: row of mixed numerical data. there is no pattern for the data. at the end of the row there are four cells that need totals to be placed in them from the cells in the row with certain colors. take for example a time sheet for a job...one color for sick days, another for vacation, holidays, etc...is it possible? Tamesh, See my reply a couple of days ago at http://tinyurl.com/379lx -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email ad...

Does nyone have a list of Statement Variables
I am trying to add quantities and unit prices to the items listed under the account activity. -- NateS Hello Nate- I have been attempting to do the same task without much luck, I am currently working with crystal reports to rewrite the AR statement. It is a pain to have to rewrite the report just to add Item quantity and unit price, but MS support tells me the built in AR functions wont do it. Have you had any luck? "nates@sunrisepos.com" wrote: > I am trying to add quantities and unit prices to the items listed under the > account activity. > -- > NateS On ...

-- in formula
I got a formula from this group recently that looked like this: =SUMPRODUCT(--(MONTH(A$3:A$87)=2)) What does the -- do in this function? MS help was not very helpful on this. TIA George George, The -- is double negation. If you do that to a number, it effectively does nothing ( a positive number is made negative, then positive, and vice versa). But if you do it to text, it forces excel to convert it to a number (if the text is a number, like 1, or 2, etc.). If you do it to boolean values (TRUE or FALSE), it coerces them to 1 and 0. That's what applies here. In this case, MONTH(...

% Formulas
Does anyone know how to translate a value (�) into a percentage of a larger value (�). e.g Cell A �500 Cell B �50 Cell C 10% Any help would be great! Anna, =B2/A3 This will yield 0.1. Format for % (% button on formatting toolbar or Format - Cells - Number - Percent) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Anna" <anna.speirs@severntrent.co.uk> wrote in message news:c1a6f9ca.0309220543.7399207e@posting.google.com... > Does anyone know how to translate a value (�) into a percentage of a > larger value...

Format only parts of a formula result?
I created a formula composed mostly of text, including a small formula. This formula: ="The products would be $"&ROUND(E28/1000,0)&"K but since new revenues are enabled almost instantly," is intended to produce: The products would be $1335K but since new revenues are enabled almost instantly, Is there a way to BOLD or ITALICIZE the "$1335K" result but leave the other text unbolded? Scott -- sdubose99 ------------------------------------------------------------------------ sdubose99's Profile: http://www.excelforum.com/member.php?action...

Date Formula Needed-Business Days
I need a formula that calculates business days, but the NETWORKDAYS function is not adequate. I need the formula to return the number of days between two dates, excluding a range of holidays. Since my store is open seven days a week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in the past, it had a BDAY function, and it accepted parameters to indicate if weedends were to be included or not. Is there a comparable function in Excel? I am trying to calculate the number of days the store is open every week. I thought of using NETWORKDAYS and simply adding 2 (Satu...

Access 2000 IIF statement
Hello all, I am having problems trying to figure out how to write a statement that only shows a column if there is something in the column. Right now I get a bunch of blank records and then the one record with the stuff in it that I need. I have a report that works off a query. The query works off a table and has a field in it called "other_new_specify" and one with "other_pending_specify". What I want to do is have the field show up on the report only when there is something populating it those two fields. The following statement gives me errors Iif(IsNull(other_pendi...

How to get a ChartTitle formula
Hi, With an object ChartTitle, one can assign to the property Text a reference to a cell: ChartTitle.Text = "=Sheet1!$C$3" What I would like to know is if there is a way to read this reference from the object ChartTitle, because all I can see is the result of the formula and not the formula itself. Thanks, DaH ...

calculating time formula
Hi, i want to set up a time summation calculation in excel. for example i put in how many hours i've working on something over a number of days down one column and a "Total hours and mins worked" cell somewhere else just adds all my inputs in hour and min format. eg. 03:20 (ie. 3 hours 20 mins) - thanks for any comments. Hi just enter your time values in Ex^cel's time format and use a formula such as =SUM(A1:A10) Important: Format your target cell with the custom format [hh]:mm -- Regards Frank Kabel Frankfurt, Germany "Nick" <ad@asd.com> schrieb im ...

Formula and field types for calculating times
Hello, I don't get it how to format fields and apply some formula to calculate with time-differences. Example: I want the difference of two time fields expressed as decimal-number. 12:30 - 08:15 = 4.25 (4 hours and 15 minutes) =24*("12:30"-"8:15") and format as General -- Gary''s Student - gsnu2007d To learn all about calculating with time, visit Chip Pearson's site: http://www.cpearson.com/excel/datetime.htm#AddingTimes -- Kind regards, Niek Otten Microsoft MVP - Excel "Hans-Martin Burger" <MartHof@gmx.de> wrote in message ne...

Search and replace formula?
Hi, I have a selection of data in this typical format: f$\Data\Sharedf$\data1\Campaigns\BTCAT e$\Data\Sharedf$\data5\Campaigns\BT EMail j$\Data\Sharedj$\Business h$\Data\Citrixf$\c1111111 f$\Data\Citrixf$\C222222 I want to be able to replace all instances where a letter is followe by a '$' sign to a constant different letter for example 'e$'. *f$*\Data\Shared*f$*\data1\Campaigns\BTCAT Is there a formula I can use that can search and replace to give me th correct output? Thanks Fran -- fastfran ----------------------------------------------------------------------- fastf...

pivot table #18
Hello, I have an excel file created daily which keeps 3 columns data. What I need is: each time the users open it they should see data in a pivot table, not as three-columns. Can I do that? The file is ready only and user does not have to write it back. Thanks, Jim. Couldn't you create the pivottable the same time that the workbook is created. If no, then maybe you can record a macro that creates the pivottable once. Then either run this macro after the creation--or give the macro to the users...In fact, maybe you could give the users a macro that would open the newly created file a...

Pivot Table Data Filter Problem
I have created a pivot table with people's names in the ROW, year group and academic year in the COLUMN and weight/height in the DATA area. Below is a copy of the pivot table... Year Group Academic Year 4 5 NAME Data 2004 2005 NAME 1 Height 1.44 1.44 Weight 51.00 51.00 NAME 2 Height 1.44 1.44 Weight 51.00 51.00 The DATA field has a drop down box to filter weight/height but if I use the filter to only show Height, I cannot get weight back (unless I undo). Why does the data column not have the same functionality (ie; show all, filter, then go back to show all) as, say, the NAME colu...