Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting data in column to Phone Format | Excel Discussion (Misc queries) | |||
Converting a matrix of data into a single column | Excel Discussion (Misc queries) | |||
Converting an array of data into a single column | Excel Discussion (Misc queries) | |||
Converting functions into numbers | Excel Worksheet Functions | |||
How to reverse the order of data in a single column in Excell? | Excel Discussion (Misc queries) |