The following are some items in a body of data that I am analyzing: you see
the text description on the left and the $ amount on the right. I am trying
to use IF formulas to return the $ amounts corresponding to the
"Miscellaneous Credit~Amex..." and "Miscellaneous Debit~Amex..." in another
column. (read below the data for the issues I am having).

MISCELLANEOUS CREDIT~AMEX BANK OF CANADA~AMEX 9300861813~ 180.79
NIGHT DEPOSIT~ 2222006 ~505665156 2400.75
DEPOSIT~ 2222006 ~504724733 2000.72
SERVICE CHARGE~NIGHT DEPOSIT ANN FEE~FEBRUARY 2006 460.10
MISCELLANEOUS CREDIT~FIR8690100~~ 3696371.48
MISCELLANEOUS CREDIT~MC8698333~~ 1889330.39
MMS SERVICE CHARGE~ 37.00
MISC DEBIT~AMEX BANK OF CANADA~AMEX 9300862001~ 57.49

The two formulas I have created below are a start, but here are the problems
I am having:
=IF(LEFT(C2,10)="MISC DEBIT",-D2,0)
=IF(LEFT(C2,20)="MISCELLANEOUS CREDIT",D2,0)

1. I want to combine the two formulas to create one formula...I think I have
to use nested IF's but I am not sure how.
2. The formulas are returning all MISC DEBIT and MISC CREDIT - even those
that are not AMEX related. This is because I have not specified AMEX in the
formula because I am not sure how because: in the body of data, there is the
character "~" and I don't think a formula can read this.

If someone can help, it would be greatly appreciated! Thank you in advance.
(:

Use two ~

=IF(COUNTIF(C2,"*MISC DEBIT~~AMEX*")0,-D2,IF(COUNTIF(C2,"*MISCELLANEOUS
CREDIT~~AMEX*")0,D2,0))

--
Regards,
Tom Ogilvy




"Lisa" wrote:

The following are some items in a body of data that I am analyzing: you see
the text description on the left and the $ amount on the right. I am trying
to use IF formulas to return the $ amounts corresponding to the
"Miscellaneous Credit~Amex..." and "Miscellaneous Debit~Amex..." in another
column. (read below the data for the issues I am having).

MISCELLANEOUS CREDIT~AMEX BANK OF CANADA~AMEX 9300861813~ 180.79
NIGHT DEPOSIT~ 2222006 ~505665156 2400.75
DEPOSIT~ 2222006 ~504724733 2000.72
SERVICE CHARGE~NIGHT DEPOSIT ANN FEE~FEBRUARY 2006 460.10
MISCELLANEOUS CREDIT~FIR8690100~~ 3696371.48
MISCELLANEOUS CREDIT~MC8698333~~ 1889330.39
MMS SERVICE CHARGE~ 37.00
MISC DEBIT~AMEX BANK OF CANADA~AMEX 9300862001~ 57.49

The two formulas I have created below are a start, but here are the problems
I am having:
=IF(LEFT(C2,10)="MISC DEBIT",-D2,0)
=IF(LEFT(C2,20)="MISCELLANEOUS CREDIT",D2,0)

1. I want to combine the two formulas to create one formula...I think I have
to use nested IF's but I am not sure how.
2. The formulas are returning all MISC DEBIT and MISC CREDIT - even those
that are not AMEX related. This is because I have not specified AMEX in the
formula because I am not sure how because: in the body of data, there is the
character "~" and I don't think a formula can read this.

If someone can help, it would be greatly appreciated! Thank you in advance.
(: