Conditional in frequency function! ...can it be done? 1

[ETID]

Is there a way to write a frequency function to calculate the frequency of "value1" only if "lot" is equal to "select_lot"?


bin frequency value1 lot select_lot
90 ? 93 4 4
91 ? 92 5
92 ? 90 4
93 ? 91 4
94 ? 92 5
95 ? 95 5
96 ? 99 5
97 ? 96 4
98 ? 96 5
99 ? 90 4
100 ? 93 5

 

[DaleWatson123321]

By "calculate the frequency", do you mean: extract the records from a dataset, where those records extracted records for each value in "value1" where "lot" is equal to "select_lot" ?

OR, do you want to "add up" or "average" all the "frequency" numbers in the "frequency" column which meet this same criteria ?

Trying to understand... hope you can clarify.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ETID]

Thanks for you response Dale,

Sorry,...those labels lined up better in the edit box

Normaly, the frequency function would return the counts of each row in the bin (col 1) that reside in col. 3,...(a histogram)

but i would like it to count only those in col 3 that have a corresponding value in col 4 that matches the single cell marked select_lot in this case 4

so that if you would change the value of "select_lot" to 5 you would get a different frequency distribution.

I hope this makes things a little clearer

 

[GeekGirlau]

Hi ETID,

Have you tried this as a pivot table?

Let's assume the sample data you have above is in cells A112.

- Select C112
- Select Data, Pivot Table Report
- Select Next (Excel list) and Next to accept the cell range
- Drag "Value" from the right hand side and drop it in the Row section
- Drag "Lot" from the right hand side and drop it in the Column section
- Drag "Lot" from the right hand side and drop it in the Data section
- Double-click on "Lot" in the Data section and change Sum to Count
- Select Next, Finish

Hope this is what you were after.

 

[ETID]

To geekgirlau:

Thanks for the suggestion, but the number of records I am dealing with exceeds the pivot table max number of unique records. I've been using a pivot table to manage data so far, but this limitation is forceing me to go another route.

:-(

 

[DarkSun]

ETID,
You will need to use an array formula so that you can specify more than one criteria.

I can't seem to get the Count array formula to give me the right results at the moment.

You can cheat and use Sum array formula but it means you will need to add a column with ones in.

Here is the forumala. Place this in B2.

=SUM(IF(C2:C12=A2,IF(D212=$E$2,F2:F12,0),0))

Where:
C is column with Values.
D is column with Lots
E2 is Select_lot
F is column with 1's.

You must enter this formula by pressing CTRL+ALT+ENTER. This will make it an array formula.

You can then copy this formula down for all other values.

 

[ETID]

Thanks to All...especially Darksun,

It's a very usable solution to my problem, odd that the count or countif functions do not work :-( , but I was able to use what you wrote and build yet another parameter into it.

Thanks Darksun...
& Thanks again to everyones for your input.