Excell functions for converting column data

[MegM]

I have a column with posting codes (numeric values) in it and I want to
convert another column to a negative number (dollar amount) if the number in
the first column is equal to several different numeric values. How do I
structure such a function?

 

[Pete_UK]

You could use a MATCH function to see if the posting code exists in a
pre-defined set of codes elsewhere in the worksheet and if it does
then return your dollar amount. If that dollar amount depends on the
code, then you can use an INDEX function lloking at corresponding
dollar amounts, which in turn uses the MATCH function.

If you supply some further details of what you have, then I can give
you a more specific answer.

Hope this helps.

Pete

 

[MegM]

This is what my column information imports as now. Both dollar amount reflect
as positive number values.

A B
200.00 1
250.00 1
265.00 1
25.00 13
612.00 13

What I want it to import to read as
A B
200.00 1
250.00 1
265.00 1
-25.00 13
-612.00 13

If column B value is 13 or 7 I want it to convert column A to a negative
number.

 

[Pete_UK]

Use a helper column - eg put this in C1:

=IF(OR(B1=13,B1=7),-A1,A1)

Copy down for as many rows as you need.

Hope this helps.

Pete

 

[MegM]

When I use the formula as you have it the function helper indicates there is
a problem and asks #VALUE for the -A1 and comes back with no figures in the
helper column but a header that reads TR AMT

 

[MegM]

Found it Pete. Thank you for your help.

Use a helper column - eg put this in C1:

=IF(OR(B1=13,B1=7),-A1,A1)

Copy down for as many rows as you need.

Hope this helps.

Pete

 

[Pete_UK]

I don't understand the header you are getting, unless that is already
the header in A1. The other problem is probably because you have
imported text values that just look like numerics. If you have
imported them from an HTML source, then you may also have <space> or
<non-breaking space> characters in there. Try highlighting the columns
and Edit | Replace (or CTL-H):

Find what: alt-0160
Replace with: leave blank
Replace All

CTRL-H again:
Find what: <space>
Replace with: leave blank
Replace All

Does the formula work now? (You don't need it in C1 if you have
headers on row 1).

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - I think our last two posts have overlapped.

Pete