[allison79]

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]

=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]

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]

Try this:

=IF(A1<1,MOD(B1,2000),IF(A1>9,MOD(B1,2000),""))

[allison79]

Super genius! It worked! You rock! Thank you so so much... yay!!

[Capuchin]

Make sure you understand how it works so you can do it in future.