Excel Forumla

[heymrdj]

I'm trying to come up with a formula to calculate the data in my Excel sheet

Female times:
If D* (where D* is blocks D2-D301) has value of FEMALE, then add to the average so that H4 is the AVERAGE value of the F blocks that correspondingly have D blocks of FEMALE.

I built it with Excel 2007.


I kinda need this by 2PM today x_x. (2 hours 15 minutes by now). Normally i can figure these things out but i'm fricken stumped.


Example:

D F H
1 Female 4
2 Male 10
3 Female 6
4 Female 7 8

This is what i'm after. The formula took the values of F1-2,4 because their D values were FEMALE. It then averaged their values and wrote that answer in H4. So please help me guys, I pray someone has a solution...or I'm going to have a long day of calculations (well over 300 data values in this excel sheet).

 

[CycloWizard]

Easiest way: create a new column. Enter the formula:
=if(D2="Female",F2,"")
Then simply take the average of the new column.

 

[heymrdj]

That gives me a new one yes, but i would have to work that formula 300 times to build the column to then average it.

Any other ideas?

 

[PottedMeat]

Originally posted by: heymrdj
That gives me a new one yes, but i would have to work that formula 300 times to build the column to then average it.

Any other ideas?

Huh? Can't you take CycloWizard's eqn and just cut'n'paste it into a column of equal size?

 

[CycloWizard]

Originally posted by: heymrdj
That gives me a new one yes, but i would have to work that formula 300 times to build the column to then average it.

Any other ideas?

Just use the fill->down option (double-click the square in the bottom right corner of the cell you initially type the equation into).

If you don't like that method, then there are alternatives.
=SUMIF(D😀,"Female",F:F)/countif(D😀,"Female")
should also work for a single cell formula. I just thought the other would be easier to understand.

 

[mayest]

Excel 2007 has a new function that you will find immensely useful. AverageIf(). It is the same as the old SumIf() function, but it calculates the average. Previously, you could have done this:

=SumIf(D2😀301,"Female", F2:F301)/CountIf(D2😀301,"Female)

Now, in Excel 2007, you can just do this:

=AverageIf(D2😀301,"Female", F2:F301)

Something like that anyway. You example kind of confused me about what you are after.

 

[CycloWizard]

Originally posted by: mayest
Excel 2007 has a new function that you will find immensely useful. AverageIf(). It is the same as the old SumIf() function, but it calculates the average. Previously, you could have done this:

=SumIf(D2😀301,"Female", F2:F301)/CountIf(D2😀301,"Female)

Now, in Excel 2007, you can just do this:

=AverageIf(D2😀301,"Female", F2:F301)

Something like that anyway. You example kind of confused me about what you are after.

Always wondered why that didn't exist before. I didn't realize they finally added it. 😛

 

[heymrdj]

Originally posted by: mayest
Excel 2007 has a new function that you will find immensely useful. AverageIf(). It is the same as the old SumIf() function, but it calculates the average. Previously, you could have done this:

=SumIf(D2😀301,"Female", F2:F301)/CountIf(D2😀301,"Female)

Now, in Excel 2007, you can just do this:

=AverageIf(D2😀301,"Female", F2:F301)

Something like that anyway. You example kind of confused me about what you are after.

THAT WAS IT!! Thank you so much mayest 😀. I put that in, and then used it again with MALE to get the male times. Saved me such a headache, I'm now well on my way to finish crunching the data our project people collected. Thx alot 🙂.

 

[bobsmith1492]

Yay, I remembered this thread from a couple of days ago and came back to find that function. I just used the SUMIF() function for my budget spreadsheet. Thanks! 🙂

 

[Stiganator]

Alternatively you can sort data in excel based on at least two simultaneously indepent criteria that I know of. Such as Sort By Male, Time ascending