see attachment "john.xls"
see the formulas F2 and H2 which are copied down
also see formulas in G2 and I2
I have set a spreadsheet up containing data relating to individuals. Each row is a different person.
In the first four columns I have numerical data which relates to a persons score, ie 0.100, 0.250 etc
In the fifth column I have text, which relates to an outcome for a person.
A B C D E
0.100 0.250 0.125 0.150 Winner
0.050 0.075 0.100 0.050 Loser
So I have around 300 rows, = 300 people.
So first off what I want to do is to add up the no. of people whose scores were all under 0.100. So I only want it to count that row / person if all their scores are under 0,100 in the forst four columns.
I tried using countifs function but it keeps giving me an error.
The next thing I would want to do is to count up the number of people who had all there scores under 0.100 but were also winners.
How do I go about doing this
Thanks in advance
Thanks for the reply.
With the countif function you used on that spreadsheet, in column G, it doesnt work correctly, as it is counting the rows in the range if only one score is under 0.100.
I only want it to count that person / row if all there scores are under 0.100
change the formula in G2
this means count rows whose even maximum in that row is less than(not even equal to) less than .1
in your case it is 0
because even your second person is having one .1 thoputhothers are less.
is this ok
the trick is to get max and min in each row and manipulate
Excellant, working fine now, thank you.
If I were to work out the standard deviation of each persons (row) data against some "norm" data and put that in a column next to the four scores,
Would I be able to tell excel to work out the overall stadard deviation for the persons that had all there scores less than 0.100.
So basically if all four scores are under 0.100, please take the standard deviation from that person, group them all together and then workout a mean SD.
john.xls is again attached
see the formula in column J
this gives std dev of data in those rows whose value <0.1
to get std dev for all the data in all the rows whose all values are less than 0.1 I have a macro "test" in the module
the result of the macro is in K2
There are currently 1 users browsing this thread. (0 members and 1 guests)