Nested If Statements

[Chomper]

This statement works fine:
=IF(MID(F28,5,2)="PR",G28,IF(MID(F28,5,2)="PF",G28,0))

But I needed to add some extra checks so I wrote this:
=IF(MID(F28,5,2)="PR",G28,IF(MID(F28,5,2)="PF",G28,if(mid(f28,4,2)=”PR”,g28,if(mid(f28,4,2)=”PF”,g28,0))))

But now I get the #Name? error.

Any idea why?

I'm trying to find entries that have a .PF or .PR in them:
ie.
return true for BMO.PR.L
return true for BMO.PF.C
return true for BAM.PR.T
return true for CM.PR.O
return false for ENB
return false for DRA.UN
return false for DH.DB

the additional formula was to catch the CM.PR.O as the first formula did not recognize this as being true as it was looking at the wrong 2 characters (with my formula it would see 'R.' instead of the 'PR').

Thanks,

 

[zJenkins]

Hi Chomper,

Quick question: What is in cell G28?

 

[Jonmo1]

=IF(MID(F28,5,2)="PR",G28,IF(MID(F28,5,2)="PF",G28,if(mid(f28,4,2)=”PR”,g28,if(mid(f28,4,2)=”PF”,g28,0))))

I don't know why it happens, but I see it quite often here
You've got some weird ”quote” marks in there

Should be

=IF(MID(F28,5,2)="PR",G28,IF(MID(F28,5,2)="PF",G28,IF(MID(F28,4,2)=[B][COLOR=#b22222]"[/COLOR][/B]PR[B][COLOR=#b22222]"[/COLOR][/B],G28,IF(MID(F28,4,2)=[B][COLOR=#b22222]"[/COLOR][/B]PF[B][COLOR=#b22222]"[/COLOR][/B],G28,0))))

 

[Mackers]

Hi

Try this on for size:

=IF(SUM(COUNTIF(F28,{"*.PR*","*.PF*"})),G28,0)

Mackers

Edit: changed PR to .PR in line with your initial post

 

[Chomper]

Hi Chomper,

Quick question: What is in cell G28?

A number. It's the dollar amount of the position in F28

 

[Chomper]

I don't know why it happens, but I see it quite often here
You've got some weird ”quote” marks in there

Should be

=IF(MID(F28,5,2)="PR",G28,IF(MID(F28,5,2)="PF",G28,IF(MID(F28,4,2)=[B][COLOR=#b22222]"[/COLOR][/B]PR[B][COLOR=#b22222]"[/COLOR][/B],G28,IF(MID(F28,4,2)=[B][COLOR=#b22222]"[/COLOR][/B]PF[B][COLOR=#b22222]"[/COLOR][/B],G28,0))))

I see. I wrote the original formula in excel. Then we I was adding to it I had copied it and pasted to a word document, added the additional formula and then copy and pasted back into excel.

I changed the weird quotes in excel and it works perfect.

Thanks.

 

[Chomper]

Hi

Try this on for size:

=IF(SUM(COUNTIF(F28,{"*.PR*","*.PF*"})),G28,0)

Mackers

Edit: changed PR to .PR in line with your initial post

This also works nicely and much shorter than my formula.

Thanks,

 

[Jonmo1]

You're welcome