I'm doing some data analysis on mouse sleep data. Basically, i have a 24-hour recording in 10-second increments, with each labeled as "NR," "R," or "W" (non-REM sleep, REM sleep, or wake). Using COUNTIF, it's relatively easy to go down the column and count the total number of each stage, but I also need to count the number of each transition (NR-->R, W-->R), etc. Is there a way to tell Excelt to count if cell=R AND the cell directly below it =NR, for example. The lab did it by hand last time they had to do this, but it wasn't 8,000 cells of data. Can anyone help?
EXCEL Question
- Thread starter timcheng00
- Start date
Whitecloak
Diamond Member
- May 4, 2001
- 6,074
- 2
- 0
you could try using subtotals to arrive at the no of counts of each data range. This would result in the creation of a new column with the subtotals occuring at each transition. You could then figure out the no of such transitions by counting the no of entries in the new columns - 1.
insert a column. in that column, use a formula that says transition=if(current cell = R AND cell below = NR), then count the number of cells in this column that are TRUE. done...
The best solution for this is to use an array-type function. This allows you to total your categories without having to add an extra column.
Skip at least one cell after the column you are adding up, and type in the function below. Once you are done typing it, be sure to hit CTRL-SHIFT-ENTER so Excel views it as an array-type function.
=SUM(IF(A1:A30="NR",IF(A2:A31="NR",1,0)))+SUM(IF(A1:A30="R",IF(A2:A31="R",1,0)))+SUM(IF(A1:A30="W",IF(A2:A31="W",1,0)))
Obviously this assumes that your data is in column A1:A30. This formula would therefore be entered in cell A32 -- NOT A31. Change these numbers as you need to. Just increase the 2nd data-set by 1 (i.e. A2:A31).
* REMINDER: Be sure to hit CTRL-SHIFT-ENTER after typing in the formula, else it will not work.
Skip at least one cell after the column you are adding up, and type in the function below. Once you are done typing it, be sure to hit CTRL-SHIFT-ENTER so Excel views it as an array-type function.
=SUM(IF(A1:A30="NR",IF(A2:A31="NR",1,0)))+SUM(IF(A1:A30="R",IF(A2:A31="R",1,0)))+SUM(IF(A1:A30="W",IF(A2:A31="W",1,0)))
Obviously this assumes that your data is in column A1:A30. This formula would therefore be entered in cell A32 -- NOT A31. Change these numbers as you need to. Just increase the 2nd data-set by 1 (i.e. A2:A31).
* REMINDER: Be sure to hit CTRL-SHIFT-ENTER after typing in the formula, else it will not work.
That's what I would have suggested. You'll know it's an array forumula because instead of the references being surrounded by parenthesis (A2:A14), they'll be surrounded by brackets like this {A2:A14}
Are you sure about your formula? Too lazy to check, but it seems like that would just be counting consecutive entries that are the same (not the number of transitions).
Also, since it is an array formula, you don't need the IF statements. You could just make it somthing like =SUM((A1:A30="R")*(A2:A31="NR"))
Best thing to do would be to setup a transition matrix (just a small 3x3 table with the column and row headings of N, R, and NR -- or whatever states you have). Then, in each cell, use the formula =SUM((A1:A30=ROW_HEADING)*(A2:A31=COLUMN_HEADING)). This will give you the full transition matrix for your data.
As mentioned above, hit CNTRL-SHIFT-Enter for these formulas.
Sorry, I mis-read the question. I thought he wanted consecutive cells that were the same. The formula will work, but you just have to alter it so it will only count them if they are different. This is very easy to do, just change the "0's" to "1's" and "1's" to "0's".
=SUM(IF(A1:A30="NR",IF(A2:A31="NR",0,1)))+SUM(IF(A1:A30="R",IF(A2:A31="R",0,1)))+SUM(IF(A1:A30="W",IF(A2:A31="W",0,1)))
Now this will total all "transitions", where the next row does NOT equal the preceding row. Hopefully I'm not still misunderstanding the question.
Piggyback sorry...
Another Excel question, all I want to do is take a dollar ammount & add, or times it by a certain ammount. Ie...
B (cost) - C (sell)
5 - $9
15 - $20
85 - $95
115 - $126.50
So on $5 say, it's add $4, i'd use =B1+4, same goes for 15 or 85, for 115 it's =B4*1.1.
Now, is there a formula I can use to make this easier? Examples?
Not very good at Excel as you can see
.
Another Excel question, all I want to do is take a dollar ammount & add, or times it by a certain ammount. Ie...
B (cost) - C (sell)
5 - $9
15 - $20
85 - $95
115 - $126.50
So on $5 say, it's add $4, i'd use =B1+4, same goes for 15 or 85, for 115 it's =B4*1.1.
Now, is there a formula I can use to make this easier? Examples?
Not very good at Excel as you can see
.
HomeBrewerDude
Lifer
- Jan 18, 2001
- 14,465
- 1
- 0
if this stuff doesnt help....try http://www.mrexcel.com theyve helped me with everything I need to know!
HomeBrewerDude
Lifer
- Jan 18, 2001
- 14,465
- 1
- 0
this is a good solution but I would suggest that the OP use DeeKnow's solution (put in a new column that has conditional values based on other cells contents) because it is easier to visually confirm that every thing is working the way it is supposed to.
Whitecloak
Diamond Member
- May 4, 2001
- 6,074
- 2
- 0
Originally posted by: Night Blade
Piggyback sorry...
Another Excel question, all I want to do is take a dollar ammount & add, or times it by a certain ammount. Ie...
B (cost) - C (sell)
5 - $9
15 - $20
85 - $95
115 - $126.50
So on $5 say, it's add $4, i'd use =B1+4, same goes for 15 or 85, for 115 it's =B4*1.1.
Now, is there a formula I can use to make this easier? Examples?
Not very good at Excel as you can see
use nested ifs.
Easier visually, yes. But from a logistics standpoint, I'd have to disagree. Only because that setup is not as dynamic.
Using my formula, you can continue to insert new rows (and thus data) and the formula should automatically adjust itself. Using DeeKnow's solution, the user will have to remember to copy/paste the formula to each new row added. I realize this isn't a major undertaking, but I like things to be as automated and simple as possible.
Originally posted by: Night Blade
Piggyback sorry...
Another Excel question, all I want to do is take a dollar ammount & add, or times it by a certain ammount. Ie...
B (cost) - C (sell)
5 - $9
15 - $20
85 - $95
115 - $126.50
So on $5 say, it's add $4, i'd use =B1+4, same goes for 15 or 85, for 115 it's =B4*1.1.
Now, is there a formula I can use to make this easier? Examples?
Not very good at Excel as you can see
I'm not really sure what your question is. Can you be more specific what you are trying to accomplish?
Simply want to add, or multiply field A (cost) to field B (sell), critera is IF ammount over $0 or below $4.99 than add $4, next IF ammount over $5 or below $9.99 than add $5, etc, etc.
Then yes, you would just use nested if() functions. For example:
=IF(OR(A1>0,A1<5),A1+4,IF(OR(A1>5,A1<10),A1+5,etc...))
If there are more than 7 possibilities, this will not work.
I suggest using a VLOOKUP table instead.
TRENDING THREADS
-
Discussion Zen 5 Speculation (EPYC Turin and Strix Point/Granite Ridge - Ryzen 9000)- Started by DisEnchantment
- Replies: 25K
-
Discussion Intel Meteor, Arrow, Lunar & Panther Lakes Discussion Threads
- Started by Tigerick
- Replies: 21K
-
Discussion Intel current and future Lakes & Rapids thread- Started by TheF34RChannel
- Replies: 23K
-
-
Facebook
Twitter