Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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?


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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 -


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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 -





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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 -



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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 -


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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:

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting data in column to Phone Format Prasad Gopinath Excel Discussion (Misc queries) 1 February 15th 08 11:13 PM
Converting a matrix of data into a single column Hosley Excel Discussion (Misc queries) 6 April 19th 07 06:07 AM
Converting an array of data into a single column Raj Excel Discussion (Misc queries) 0 August 15th 06 09:21 PM
Converting functions into numbers vesuvius Excel Worksheet Functions 1 November 5th 05 12:37 AM
How to reverse the order of data in a single column in Excell? vpedchenko Excel Discussion (Misc queries) 5 August 17th 05 05:00 PM


All times are GMT +1. The time now is 11:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"