Ask Experts Questions for FREE Help !
Ask
    allison79's Avatar
    allison79 Posts: 7, Reputation: 1
    New Member
     
    #1

    Apr 27, 2007, 01:34 AM
    excluding data from column
    hi. Excel question. I have a number of column's, but I am mainly concerned with 2 columns, and the generation of new column. One column is a series of data where values equal, 0, 1000, -1000, 2000, -2000, and 1,2,3... 9. the other column has modulus values corresponding to this data (0, 1000, etc). I would like to generate a new column whereby my modulus data appears ONLY for values 0, 1000,-1000,2000,-2000. I want to exclude data for modulus values corresponding to 1,2,3... 9. I want empty cells to appear in the new column where those values were to appear. Would someone please be able to advise me. Thank you!!
    Capuchin's Avatar
    Capuchin Posts: 5,255, Reputation: 656
    Uber Member
     
    #2

    Apr 27, 2007, 01:41 AM
    =IF(A1<1,MOD(A1,2000),IF(A1>9,MOD(A1,2000),""))

    Something like that?

    It checks if A1 is less than 1, if it is then it works out the mod (change this to whatever). If it isn't then it checks if it's greater than 9, if it is then it works out the mod, and if it isn't then it sets the cell empty.

    Give it a go and tell me what happens...

    I'm not sure I've got what you're trying to do right :)
    allison79's Avatar
    allison79 Posts: 7, Reputation: 1
    New Member
     
    #3

    Apr 27, 2007, 02:10 AM
    Hi. So it worked, but just not in the way I needed it to... maybe a visual example would be easier:

    Col 1. mod(col 2) what I need to create(col3)
    0... 1787... 1787
    0... 1915... 1915
    1000... 1943... 1943
    -1000... 32... 32
    5... 41... empty cell (appearance of "5" in col 1)
    0... 63... 63
    0... 225... 225
    1000... 1927... 1927
    0... 1945... 1945
    -1000... 30... 30
    6... 45... empty cell (appearance of "6" in col 1)
    0... 55... 55

    So, the appearance of anything from 1-9 in col 1, needs to cause the "not" appearance of the corresponding modulus (from col 2) in the new column (col 3). Confusing... thank you for being so helpful...
    Capuchin's Avatar
    Capuchin Posts: 5,255, Reputation: 656
    Uber Member
     
    #4

    Apr 27, 2007, 02:19 AM
    Try this:

    =IF(A1<1,MOD(B1,2000),IF(A1>9,MOD(B1,2000),""))
    allison79's Avatar
    allison79 Posts: 7, Reputation: 1
    New Member
     
    #5

    Apr 27, 2007, 02:30 AM
    Super genius! It worked! You rock! Thank you so so much... yay!!
    Capuchin's Avatar
    Capuchin Posts: 5,255, Reputation: 656
    Uber Member
     
    #6

    Apr 27, 2007, 02:34 AM
    Make sure you understand how it works so you can do it in future.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Data entry or data processing from home. [ 1 Answers ]

Hello from Florida, My name is Don and I am looking for a free data entry from home website that I can find. It is not easy to find! Please help!

What is a water column [ 10 Answers ]

My question is actually two questions in one. I believe that in my studies in school I learned that a water column is a stand of water in a pipe to a certain height. If the pipe ended at the bottom of a tank then the water column would extend up through the water in the tank to the water level but...

4 Column Accounts [ 3 Answers ]

Ok here is the information I have and what I have done with it thus far, I guess I am not sure exactly what it is asking of me? Account # Account Title Debit Credit 11 Cash... $ 4,900 12 Accounts Receivable... 15,310...

Modulus by column [ 5 Answers ]

hi. I am trying to apply the modulus function to an entire column of data. I am obviously doing something wrong though, as I wind up getting the modulus value for the first cell in the column only. How can I apply the modulus function to an entire column of data? Thanks.

Error Message - Column Not Allowed Here [ 3 Answers ]

I tried to create a table and got the following message: COLUMN NOT ALLOWED HERE. What is the solution for this? Regards MATHEW


View more questions Search