If Statement in the Calculated Field section of the Pivot able

[Kalief]

To the Excel wizards

I am trying to create a IF statement which contains a text such "-(I)" in it. The IF STATEMENT will be used in the Calculation Field within the Pivot table. Any suggestion? I am really stumped.

[FDibbins]

Hi and welcome to the forum

From the sounds of it, you need to use a wild-card...=if("*-(I)*..............

Cant be more specific without more detail, sorry

[Kalief]

The pivot table contains data such as the example below. What is interesting, all the Locator codes is designated with a (D) (A) or (I). What I am trying to do is to write an IF Statement , which will search for the - (I) in the Locator Code cell, if it is there put the replicate the HOUR value, if not, multiply HOUR value by 2. Is this possible?

Locator Code 1qtr YTD hour current units YTD unit cost
ZZ12356 - (I) Asset Costs 2982 3695 436 436 7.69

[Kalief]

Locator Code 1qtr YTD hour current units YTD unit cost
ZZ12356 - (I) Asset Costs 2982 3695 436 436 7.69

[FDibbins]

Did you try my suggestion of using a wild-card?

if("*-(I)*",cell_with_hour,cell_with_hour*2)

[Kalief]

Yes I did. It did not work. I used following: =IF(A7="*-(I)*",D7,D7*2)

Are there any other ways to write it?

[FDibbins]

As a test, instead of the wild-card, use the who name, just to see if it works

[Tony Valko]

Did you try my suggestion of using a wild-card?

if("*-(I)*",cell_with_hour,cell_with_hour*2)

The IF function doesn't support the use of wildcards.

[Tony Valko]

Functions that support wildcards:

AVERAGEIF - Excel 2007 and later
AVERAGEIFS - Excel 2007 and later
COUNTIF - all versions of Excel
COUNTIFS - Excel 2007 and later
HLOOKUP - all versions of Excel
MATCH - all versions of Excel
SEARCH - all versions of Excel
SUMIF - all versions of Excel
SUMIFS - Excel 2007 and later
VLOOKUP - all versions of Excel

[FDibbins]

Thanks for that Tony

So change the suggetsion to something like...

=IF(ISNUMBER(SEARCH("*-(i)*",a7,1)),1,2)

[Kalief]

I'll try it. I'll let you know if I was successful.

[Kalief]

It did not work. The - (I) is column A. I have the number 3000 in column B. If there is - (I) identifier in the text description in column A, I want 3000 to show up in column C. Otherwise take column B and multiply it by 2 and put it in Column C.

How do I write that statement? Keep in min the others didn't work.

[FDibbins]

ths bolded part does work...
=IF(ISNUMBER(SEARCH("*-(i)*",a7,1)),1,2)

All you need to do is replace ,1 and ,2 with what you want to see if -(I) is found (,1) or not found (,2)
=IF(ISNUMBER(SEARCH("*-(i)*",a7,1)),3000,B7*2)

[Kalief]

I think I am not doing it right. I up loaded a Word Doc with two screen shots on it. Take a look. I hope you can see it.

[Kalief]

HM568950 - (A) Perform functions 20000 FALSE

my entry =IF(ISNUMBER(SEARCH("*-(I)*",A2,B2)),B2*2)

Just in case you could not see screen the shots, above is my entry. My result was FALSE. Did I enter it correctly?