• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

EXCEL Question

T

timcheng00

Member
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?
 
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.


 
Originally posted by: CrimsonChaos
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.
Click to expand...
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}

 
Originally posted by: CrimsonChaos
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).
Click to expand...

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.
 
Originally posted by: Hector13
Originally posted by: CrimsonChaos
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).
Click to expand...

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.
Click to expand...


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 🙂.
 
Originally posted by: DeeKnow
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...
Click to expand...

yep
 
Originally posted by: CrimsonChaos
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.
Click to expand...



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.
 
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 🙂.
Click to expand...

use nested ifs.
 
Originally posted by: HomeBrewerDude

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.
Click to expand...

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 🙂.
Click to expand...

I'm not really sure what your question is. Can you be more specific what you are trying to accomplish?


 
Originally posted by: CrimsonChaos

I'm not really sure what your question is. Can you be more specific what you are trying to accomplish?
Click to expand...

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.


 
Originally posted by: Night Blade
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.
Click to expand...

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...))

 
Originally posted by: CrimsonChaos
Originally posted by: Night Blade
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.
Click to expand...

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...))
Click to expand...

If there are more than 7 possibilities, this will not work.

I suggest using a VLOOKUP table instead.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top