The craziest question I have ever asked!!!

[Jeff Mattero]

Hello:
I am wondering if someone her might be able to help me
with this problem.

I have a huge spreadsheet (33000 rows), which was sent to
me from a broker, on which I have to do some detailed
calculations. Columns are labeled P/S (for purchase or
sale), Date, # Shares, Price per share, total. Since the
number of shares is always listed as a positive number, I
am having a problem. I would like PURCHASES of stock to be
a POSITIVE number, and SALES of stock to be a NEGATIVE
number. (All of the data I have is for the trading in one
stock). This is a very simple problem, which I normally
fix by use of the IF WORKSHEET FUNCTION, and, in words, I
say "If the P/S column is a "P", use the number of shares
as stated, otherwise, use the inverse of the number of
shares (IF A2="P",C2,-(C2)). Pretty simple right???

However, when I do this on the worksheet that was
electronically sent to me, ALL results are coming out as
negative numbers. To try to figure this out, I changed the
above formula to read IF A2="P",C2,"xxx", and all results
are xxx. This suggests to me that A2 does not = P.
However, I am looking at the cell A2, and it DOES show P.
I have copied this formula down the entire sheet, and, in
all cases, I get the xxx, which is telling me that none of
the entries in column are P. However, as stated before,
the only entries in that column are P for purchases, and S
for sales. Is there some way that I can see EXACTLY what
is in the cell? I thought I simply had to click on it,
but, apparantly, I am wrong about that, as the result of
the formula seems to prove. Am I missing something here?

I have re-created a small spreadsheet, where I have typed
in the data manually, done the calculation and it works
perfectly. However, on the spreadsheet the broker sent me,
the SAME formula does not work the same way. Any ideas on
how to check/fix this problem?

While I am far from a computer expert, I know this type of
elementary calculation in Excel, and have used it many
times previously.

If A2 = spaceP rather than P, would that make the formula
work as I am describing???

If I am not making myself clear, please e-mail me and I
will send you a small sample of the spreadsheet. My e-mail
is listed below, but please remove the NOSPAM on both
sides of the @ sign. Thanks.

Jeff Mattero
(e-mail address removed)

 

[Patti]

Does this help?

=IF(Trim(A2)="P",C2,-C2)

 

[Ron Rosenfeld]

If A2 = spaceP rather than P, would that make the formula
work as I am describing???

Several thoughts.

Leading or trailing spaces.

Trailing CHR(160) -- a non-printing space frequently used in web-based tables
to keep the characters from being right "at the edge".

Solution

=IF(COUNTIF(A3,"*P*")=1,"There is a P","No P")


--ron

 

[Guest]

Ron:
I understand the concept of what you are saying, but I am
not sure I understand the formula you state. What I am
asking for is, in words, this ... If A2="P" (meaning this
is a Purchase of stock)then use the number of shares as
stated in cell B2, otherwise, use the INVERSE of the
number of shares in cell B2. thus, my formula states =IF(
A2="P",B2,-(B2)). How would I write the same formula
assuming that there are, in fact, leading or trailing
spaces in the data? Should the formula be adjusted as
follows =IF(A2="*P*",B2,-(B2)). In other words, what I am
asking is does the *character on either side of the P act
like a wildcard character? Thanks.

 

[Guest]

Patti:
WOW!!!!!!!!!!!!!!! I have never heard of that Trim
operator. It worked. I have been fighting with this for
hours, and you answered me in 5 minutes!!!!! Thanks so
much!!!

 

[Myrna Larson]

Have you checked the length of the data in this column, i.e. =LEN(A2)

If it's 2, then try this formula =CODE(MID(A2,1,1)). If the 1st letter is P,
the code is 80. Then try =CODE(MID(A2,2,1)). If the 2nd char is a space, the
result will be 32. If this data is coming from the web, you may find it's 160.
Whatever it is, used Search and Replace to remove the character. To remove
character 160, in the Find What box, hold down the ALT key and type 0160 on
the numeric key pad. Leave the Replace With box empty, and click on Replace
All.

If you don't want to meddle with the data, and the first character is P or S,
modify your formula to

=IF(LEFT(A2)="P",C2,-C2)

 

[Ron Rosenfeld]

Ron:
I understand the concept of what you are saying, but I am
not sure I understand the formula you state. What I am
asking for is, in words, this ... If A2="P" (meaning this
is a Purchase of stock)then use the number of shares as
stated in cell B2, otherwise, use the INVERSE of the
number of shares in cell B2. thus, my formula states =IF(
A2="P",B2,-(B2)). How would I write the same formula
assuming that there are, in fact, leading or trailing
spaces in the data? Should the formula be adjusted as
follows =IF(A2="*P*",B2,-(B2)). In other words, what I am
asking is does the *character on either side of the P act
like a wildcard character? Thanks.

Substitute my logic test for yours, in your formula:

=IF(COUNTIF(A2,"*P*")=1,B2,-B2)

And in the COUNTIF formula, the asterisks are a wild card character. This is
not the case for the simple statement =A2="*P*"

COUNTIF will return a 1 if it finds a "P", and a 0 if it does not.

So COUNTIF(A2,"*P*")=1 will evaluate as TRUE if there is a P, and FALSE if
there is no P. (or if there is more than one P, but that should not be a
possibility).

You could use other formulas to search for P, but COUNTIF seems the simplest.


--ron

 

[Guest]

Gidday - just a thought - if the column has anything in
it to indicate a purchase you could filter on that -
stilck in a clumn to allow the value to be =*-1 for the
one you want to change to the negative then copy and
paste special value over the original values and get rid
of the extra column added.
Gary
Oz

 

[Myrna Larson]

In a direct comparison statement as you've written it, A2="*P*", the asterisk
is NOT a wildcard. Do do it that way, you need to use COUNTIF, which DOES
accept wildcards.