Excell functions for converting column data
 

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?

Excell functions for converting column data
 

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

On Apr 15, 4:35*pm, MegM wrote:
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?

Excell functions for converting column data
 

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" wrote:

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

On Apr 15, 4:35 pm, MegM wrote:
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?

Excell functions for converting column data
 

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

On Apr 15, 6:17*pm, MegM wrote:
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" wrote:
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

On Apr 15, 4:35 pm, MegM wrote:
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?- Hide quoted text -

- Show quoted text -

Excell functions for converting column data
 

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

"Pete_UK" wrote:

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

On Apr 15, 6:17 pm, MegM wrote:
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" wrote:
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

On Apr 15, 4:35 pm, MegM wrote:
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?- Hide quoted text -

- Show quoted text -

Excell functions for converting column data
 

Found it Pete. Thank you for your help.

"Pete_UK" wrote:

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

On Apr 15, 6:17 pm, MegM wrote:
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" wrote:
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

On Apr 15, 4:35 pm, MegM wrote:
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?- Hide quoted text -

- Show quoted text -

Excell functions for converting column data
 

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

On Apr 15, 7:46*pm, MegM wrote:
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



"Pete_UK" wrote:
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

On Apr 15, 6:17 pm, MegM wrote:
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" wrote:
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

On Apr 15, 4:35 pm, MegM wrote:
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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Excell functions for converting column data
 

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

Pete

On Apr 15, 8:00*pm, MegM wrote:
Found it Pete. Thank you for your help.



"Pete_UK" wrote: