Excel 2007 : If then formula

[stevetothink]

Hi Folks,

I have a column with fields such as PA12345 and I want to write an if then formula that says "if the first 2 letters of this field equal "PA" then display $21.12."

Is it possible to have the formula only look at the first 2 letters of the data for an if/then statement?

Thanks in advance.

Steve

[zbor]

You can try LEFT function:

=IF(ISNA(LEFT(A1,2)="PA"), "", 21.12)

[DonkeyOte]

zbor - time for a coffee

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[zbor]

Won't that give you error if there is no PA in word?

Didn't test thou... Even if so, I'm not sure is it NA() error...

[DonkeyOte]

No, the test is comparative so it will simply return a Boolean (either left 2 chars are PA or they are not - of course if underlying value is an error....)

ISNA is not really a valid test here (edit: you're also missing a closing parenthesis... go make that coffee zbor this is not like you!).

edit: the missing parenthesis is nonsense - maybe I need a coffee too !!

[zbor]

Or rather scotch... So I can have excuse for not thinking sober

[stevetothink]

[QUOTE=DonkeyOte;2401901]zbor - time for a coffee

Please Login or Register  to view this content.

Thanks for this. It worked. I really appreciate the quick reply.

Can I take it one step further? Some of the values in the same column are P12345 instead of PA12345. When it is just "P" I want to return $19.

Is there a way to differentiate between PA & P since those are the only characters that are consistent in this column?

I tried to create something that said if A1,1="P" AND A1,2 <>"PA" then $19 but I couldn't get it to work properly. Here is the exact code I couldn't get to work.

=AND(IF(LEFT(I48,1)="P",IF(LEFT(I48,2)<>"PA","19","")))

[DonkeyOte]

You can embed the IFs

Please Login or Register  to view this content.

[stevetothink]

Thanks that worked but I got an error after entering all of my code. What does this mean?

"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

=IF(LEFT(I4,2)="PA",'BCV Prices'!$C$1,IF(LEFT(I4,2)="TA",'BCV Prices'!$C$6,IF(LEFT(I4,2)="TR",'BCV Prices'!$C$8,IF(LEFT(I4,2)="PD",'BCV Prices'!$C$3,IF(LEFT(I4,2)="PR",'BCV Prices'!$C$4,IF(LEFT(I4,2)="TD",'BCV Prices'!$C$7,IF(LEFT(I4,1)="T",'BCV Prices'!$C$5,IF(LEFT(I4,1)="C",'BCV Prices'!$C$9))))))))

[DonkeyOte]

It means you're trying to embed too many IFs and can generally be translated as:

"Excel thinks there's a better way to do what you want"

Post what you are trying to do in full - we can help you streamline ... edit: you have

[DonkeyOte]

=IF(LEFT(I4,2)="PA",'BCV Prices'!$C$1,IF(LEFT(I4,2)="TA",'BCV Prices'!$C$6,IF(LEFT(I4,2)="TR",'BCV Prices'!$C$8,IF(LEFT(I4,2)="PD",'BCV Prices'!$C$3,IF(LEFT(I4,2)="PR",'BCV Prices'!$C$4,IF(LEFT(I4,2)="TD",'BCV Prices'!$C$7,IF(LEFT(I4,1)="T",'BCV Prices'!$C$5,IF(LEFT(I4,1)="C",'BCV Prices'!$C$9))))))))

First things first ... do the values PA,TA,TR,PD,PR,TD,T and C appear on 'BCV Prices' sheet by any chance adjacent to the values you're looking to retrieve ?

(eg B3:B9 contain the terms and C3:C9 the values being retrieved)

[stevetothink]

Yes - that is exactly how they appear on the 'BCV Prices' sheet. B1:B9 (the terms) and C1:C9 (the values I want).

I REALLY appreciate your help!

[DonkeyOte]

OK - if we assume this is not being run on XL2007 then:

Please Login or Register  to view this content.

If you would be happy for a 0 return instead of Null for those values that are not found in the listing then there are alternatives.

If you're using XL2007 the above can be shortened a little.

[stevetothink]

I am using XL2007. How does that affect the code?

Thanks

[DonkeyOte]

It means you can use IFERROR rather than double evaluate (albeit partially)

Please Login or Register  to view this content.

Best to update your profile (I appreciate you posted in 2007 forum but people generally give greater weight to profile info.)

Note: IFERROR is not backwards compatible - so if you intend to run in earlier versions in addition to XL2007 the above would not work (revert to prior suggestion in that instance)

(please note: no need to quote prior posts in their entirety just those parts necessary to maintain a logical flow (if any) - excess quotes simply clutter your thread and the board in general)

[stevetothink]

Thank you, Thank you, Thank you!!!!

I don't exactly understand the code yet but it works. I really want to understand it all so I may hit you with some questions once I go through it and play around for a while.

I REALLY appreciate your help.

Gonna go update my profile with excel2007 now.

Thanks,
Steve