Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

**thaphthia** · 04-08-2013, 07:26 PM

Hi everyone,

I made a very annoying error when programming a cognitive task for my research. Basically, I need some help making the following formulas with specific range criteria, as due to my mistake creating these formulas are beyond my Excel ability!

If anyone could help me figure out any of the following formulas that would be very awesome.

1. Formula to calculate the AVERAGE of K43:K82 IF:
L43:L82,"TRUE"
G43:G67,"neutralf"
G68:G72,"fat"
G73:G77,"neutralf"
G78:G82,"fat"

2. Formula to calculate the STDEV of K43:K82 IF:
L43:L82,"TRUE"
G43:G67,"neutralf"
G68:G72,"fat"
G73:G77,"neutralf"
G78:G82,"fat"

3. Formula to COUNT the number of cells within K43:K82 IF:
L43:L82,"TRUE"
G43:G67,"neutralf"
G68:G72,"fat"
G73:G77,"neutralf"
G78:G82,"fat"

4. Formula to compute the AVERAGE within K43:K82 IF:
L43:L82,"TRUE"
G43:G67,"neutralf"
G68:G72,"fat"
G73:G77,"neutralf"
G78:G82,"fat"
>=(C114-(2*C113))
<=(C114+(2*C113))

Thank you in advance!

**FDibbins** · 04-08-2013, 07:34 PM

for 1, use =averageifS()
for 3, use =countifS()
4 seems the same as 1? If not, use the same function

Not sure about the stddev, sorry

**Tony Valko** · 04-08-2013, 07:40 PM

All of the ranges have to be the same size:

K43:K82 = 40 cells
L43:L82 = 40 cells
G43:G67 = 25 cells
G68:G72 = 5 cells
G73:G77 = 5 cells
G78:G82 = 5 cells

**thaphthia** · 04-08-2013, 07:50 PM

Thank you both. Here is what I came up with, will these formulas work? If all the ranges have to be the same size, does this mean that I won't be able to do these calculations?

1.
AVERAGEIFS(K43:K82,L43:L82,"TRUE",G43:G67,"neutralf",G68:G72,"fat",G73:G77,"neutralf",G78:G82,"fat")

2.
=STDEV(IF(L43:L82=TRUE,IF(G43:G67="neutralf",IF(G68:G72,"fat",IF(G73:G77,"neutralf",IF(G78:G82,"fat",K43:K82))))))

3.
COUNTIFS(K43:K82,L43:L82,"TRUE",G43:G67,"neutralf",G68:G72,"fat",G73:G77,"neutralf",G78:G82,"fat")

4.
AVERAGEIFS(K43:K82,L43:L82,"TRUE",G43:G67,"neutralf",G68:G72,"fat",G73:G77,"neutralf",G78:G82,"fat",K43:K82,">="&(C114-(2*C113)),K43:K82,"<="&(C114+(2*C113))))

**Tony Valko** · 04-08-2013, 07:58 PM

will these formulas work?

No

All the ranges have to be the same size.

There has to be a 1:1 relationship between the criteria ranges and range to be calculated.

**thaphthia** · 04-08-2013, 08:03 PM

Alright, I see. So in that case, do you think that there is no way to make these calculations?

**SDCh** · 04-08-2013, 08:10 PM

See my attachment, may be this what you looking for

Note : array formula, use ctrl+shift+enter

**Tony Valko** · 04-08-2013, 08:14 PM

Can you post a SMALL sample file and tell us what results you expect for each of your formulas?

We only need to see the data in the referenced ranges. I won't look at sample files that are 100's of kbs in size.

**thaphthia** · 04-08-2013, 08:57 PM

SDCh, thank you for your help. Those formulas work well, though I'm not sure if they are entirely what I am looking for, because "count" should = 20 in my dataset but this formula gives me a different value.

Thank you both for persisting with me

see my data attached.

Basically, I need to compute the aforementioned formulas for my data, but the problem is that my values for the variable "congruent" are miscoded. So I need to calculate each of the below formulas selecting only those responses where G43:G67="neutralf", G68:G72="fat", G73:G77="neutralf", G78:G82="fat".

1. Formula to calculate the mean reaction time (RT column K43:K82), when the response is correct (L43:L82 is "TRUE")

2. Formula to calculate the standard deviation of the reaction time (RT column K43:K82), when the response is correct (L43:L82 is "TRUE")

3. Formula to count the number of correct responses (L43:L82 is "TRUE") within the range K43:K82

4. Formula to calculate the mean reaction time (RT column K43:K82), when the response is correct (L43:L82 is "TRUE"), and when the RT values are within plus or minus 2 standard deviations of the mean (1 and 2 above)

**thaphthia** · 04-08-2013, 09:19 PM

Sorry, I think "count" should = 19 actually, if the formula is correct.

**Tony Valko** · 04-08-2013, 09:45 PM

See if this is what you had in mind for the average. If it's correct then we can use the same syntax for the other calculatios.

Array entered**:

=AVERAGE(IF(G43:G67="neutralf",K43:K67),IF(G68:G72="fat",K68:K72),IF(G73:G77="neutralf",K73:K77),IF(G78:G82="fatk",K78:K82))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**thaphthia** · 04-08-2013, 10:03 PM

Thanks Tony Valko

That seems close, however, I also need a stipulation that the response is correct (L43:L82 is "TRUE"). I'm not sure where to fit that into the formula though!

I calculated the mean by hand (K43+K45+K47+K49+K51+K53+K55+K57+K59+K61+K64+K66+K68+K70+K74+K76+K78+K80+K82)/19). The AVERAGE formula should = 654.3684211 if it is done correctly.

Any ideas?

**Tony Valko** · 04-08-2013, 10:13 PM

Oops!

I forgot about the "TRUE" condition.

Well, I'm getting ready to call it a day. I'll stop by tomorrow and see if anyone else has chimed in. If not, we should be able to work in the TRUE conditon.

**thaphthia** · 04-08-2013, 10:21 PM

Ok thanks so much for helping! I'm going to also take a break and I'll be back tomorrow

**Tony Valko** · 04-09-2013, 09:47 AM

Try this array formula for the average:

=AVERAGE(IF(G43:G67="neutralf",IF(L43:L67=TRUE,K43:K67)),IF(G68:G72="fat",IF(L68:L72=TRUE,K68:K72)),IF(G73:G77="neutralf",IF(L73:L77=TRUE,K73:K77)),IF(G78:G82="fatk",IF(L78:L82=TRUE,K78:K82)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

With that formula I get a result of 668.875.

**thaphthia** · 04-09-2013, 07:22 PM

Wow, yes! That formula is exactly what I need. There was a typo, the last "fat" was typed as "fatk". When I fixed the error, I got 654.3684211, which is the correct answer.

Based on this, I think the following formulas will work:
1. =AVERAGE(IF(G43:G67="neutralf",IF(L43:L67=TRUE,K43:K67)),IF(G68:G72="fat",IF(L68:L72=TRUE,K68:K72)),IF(G73:G77="neutralf",IF(L73:L77=TRUE,K73:K77)),IF(G78:G82="fat",IF(L78:L82=TRUE,K78:K82)))

2.
=STDEV(IF(G43:G67="neutralf",IF(L43:L67=TRUE,K43:K67)),IF(G68:G72="fat",IF(L68:L72=TRUE,K68:K72)),IF(G73:G77="neutralf",IF(L73:L77=TRUE,K73:K77)),IF(G78:G82="fat",IF(L78:L82=TRUE,K78:K82)))

3.
=COUNT(IF(G43:G67="neutralf",IF(L43:L67=TRUE,K43:K67)),IF(G68:G72="fat",IF(L68:L72=TRUE,K68:K72)),IF(G73:G77="neutralf",IF(L73:L77=TRUE,K73:K77)),IF(G78:G82="fat",IF(L78:L82=TRUE,K78:K82)))

4.
For the last formula, I need to calculate the mean reaction time (RT column K43:K82), when the response is correct (L43:L82 is "TRUE"), and when the RT values are within plus or minus 2 standard deviations of the mean (1 and 2 above). Again, selecting only those responses where G43:G67="neutralf", G68:G72="fat", G73:G77="neutralf", G78:G82="fat".

I have pasted the mean from 1 above in cell C114, and the SD from 2 above in cell C113, so I'm looking to add something like this command to our average formula 1: ">="&(C114-(2*C113)) and "<="&(C114+(2*C113)

Any thoughts on this last formula?

**Tony Valko** · 04-10-2013, 10:19 PM

Originally Posted by thaphthia

4.
For the last formula, I need to calculate the mean reaction time (RT column K43:K82), when the response is correct (L43:L82 is "TRUE"), and when the RT values are within plus or minus 2 standard deviations of the mean (1 and 2 above). Again, selecting only those responses where G43:G67="neutralf", G68:G72="fat", G73:G77="neutralf", G78:G82="fat".

I have pasted the mean from 1 above in cell C114, and the SD from 2 above in cell C113, so I'm looking to add something like this command to our average formula 1: ">="&(C114-(2*C113)) and "<="&(C114+(2*C113)

Any thoughts on this last formula?

Yes, that's what you have to do.

Add this to each of the other IF expressions:

IF(range>=(C114-(2*C113)),IF(range<=(C114+(2*C113)),range)

That's going to be a gigantic formula!

**thaphthia** · 04-11-2013, 11:46 AM

Ok! So the formula worked!! As far as I can tell, it is producing the result I am looking for

This was the final product:
AVERAGE(IF($G$43:$G$67="neutralf",IF($L$43:$L$67=TRUE,IF($K$43:$K$67>=(C114-(2*C113)),IF($K$43:$K67<=(C114+(2*C113)),$K$43:$K$67)))),IF($G$68:$G$72="fat",IF($L$68:$L$72=TRUE,IF($K$68:$K$72>=(C114-(2*C113)),IF($K$68:$K$72<=(C114+(2*C113)),$K$68:$K$72)))),IF($G$73:$G$77="neutralf",IF($L$73:$L$77=TRUE,IF($K$73:$K$77>=(C114-(2*C113)),IF($K$73:$K$77<=(C114+(2*C113)),$K$73:$K$77)))),IF($G$78:$G$82="fat",IF($L$78:$L$82=TRUE,IF($K$78:$K$82>=(C114-(2*C113)),IF($K$78:$K$82<=(C114+(2*C113)),$K$78:$K$82)))))

I am so happy, thank you so much for all of your help!!!

You helped to save my thesis

Lauren

**Tony Valko** · 04-11-2013, 05:22 PM

You're welcome. Thanks for the feedback!

Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

LinkBack

Thread Tools

Rate This Thread

Display

Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Re: Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions