Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
stressing about formulas
Hi! I'm sorry if this is the wrong forum to post in, but I'm a bigtime newbie in distress. I've been driving myself crazy here for quite a few hours trying to figure out how to calculate mean and mode based on restricted criteria. The problem is that the version of Excel I'm working in is in Spanish, since I'm working from Barcelona and don't have the English version on my PC, so it's difficult to grasp some of the mathematical jargon. 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. 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) 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. Sorry about the lengthiness, thanks in advance for your help!! Adeu! -- Sareta ------------------------------------------------------------------------ Sareta's Profile: http://www.excelforum.com/member.php...o&userid=36237 View this thread: http://www.excelforum.com/showthread...hreadid=560182 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
stressing about formulas
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy only cells with formulas in another row? | Excel Worksheet Functions | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
How to change column letters to correct ones in many formulas automatically? | Excel Worksheet Functions | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |