stressing about formulas

[Sareta]

Hi! I'm sorry if this is the wrong forum to post in, but I'm a bigtim
newbie in distress. I've been driving myself crazy here for quite
few hours trying to figure out how to calculate mean and mode based o
restricted criteria. The problem is that the version of Excel I'
working in is in Spanish, since I'm working from Barcelona and don'
have the English version on my PC, so it's difficult to grasp some o
the mathematical jargon.

So, can anyone tell me how to calculate averages and modes among
subset of a group of participants? For instance, if column C lists th
sex of each participant, and columns N - Z, let's say, represent thei
various responses to each of the questions on a survey (the column
represent fields or responses to questions, the rows participants), ho
would I calculate the average response for females to the question i
column N, and then copy the same formula through Z for each of th
responses, without altering that the restrictive criteria should b
taken from column C, that is, by sex? I've come up with a sort o
system by using SUMIF divided by COUNTIF, but this becomes a proble
when I attempt to fillin the formula for several columns, as th
offsetting automatically moves the restrictive criteria to the right
so that it seems like I'd have to go in manually and rewrite column
cell references in every single formula. The problem is it's a hybri
formula - the criteria has to stay in one particular column, by th
SUMIF should change depending on the column where I paste the formula.

And how about mode? Can you do the same song and dance for mode, mode
based on one condition of restriction derived from a single column (e.g
column D is place of birth - I want to calculate the most frequen
response for each question (each column) among only those participant
born in Catalunya (found in column D)

Finally, is there any way to calculate mode by selecting several cell
instead of choosing a range (e.g. mode of N3 Q3 U3 AA3 etc), even i
some of those cells have N/A as their value? This is another on
that's been giving me a headache.

Sorry about the lengthiness, thanks in advance for your help!!

Adeu

 

[Bernie Deitrick]

Sarteta,

See my comments in-line.

HTH,
Bernie
MS Excel MVP

I'm working from Barcelona ....

Lucky you - I was just in Barcelona and had a wonderful visit.

So, can anyone tell me how to calculate averages and modes among a
subset of a group of participants? For instance, if column C lists the
sex of each participant, and columns N - Z, let's say, represent their
various responses to each of the questions on a survey (the columns
represent fields or responses to questions, the rows participants), how
would I calculate the average response for females to the question in
column N, and then copy the same formula through Z for each of the
responses, without altering that the restrictive criteria should be
taken from column C, that is, by sex? I've come up with a sort of
system by using SUMIF divided by COUNTIF, but this becomes a problem
when I attempt to fillin the formula for several columns, as the
offsetting automatically moves the restrictive criteria to the right,
so that it seems like I'd have to go in manually and rewrite column C
cell references in every single formula. The problem is it's a hybrid
formula - the criteria has to stay in one particular column, by the
SUMIF should change depending on the column where I paste the formula.

You need to change part of your referencing to absolute addresses:

=SUMIF($C:$C,"Female",N:N)/COUNTIF($C:$C,"Female")

Copy that to the right, and the N:N will change to O:O but $C:$C will remain the same.

And how about mode? Can you do the same song and dance for mode, mode
based on one condition of restriction derived from a single column (e.g.
column D is place of birth - I want to calculate the most frequent
response for each question (each column) among only those participants
born in Catalunya (found in column D)

This array formula - entered using Ctrl-Shift-Enter, will do that.

=MODE(IF($D$2:$D$100="Catalunya",N2:N100))

Note that you cannot use entire columns with array formulas - thus the 2 / 100 - you need to have
matched ranges, expanded to meet your data table size.

Also, you can use a cell reference like

=MODE(IF($D$2:$D$100=$AA2,N2:N100))

where AA2 contains Catalunya. This is helpful if you want to create a table, with

Andalusia
Aragon
Asturias
Balearic Islands

running down column AA...

Finally, is there any way to calculate mode by selecting several cells
instead of choosing a range (e.g. mode of N3 Q3 U3 AA3 etc), even if
some of those cells have N/A as their value? This is another one
that's been giving me a headache.

Not sure what you mean - you could use a macro....