How to average values in a column that has differnent categories

[Aura Perez]

I have looked at all of the examples that resembles what I want to do and none of them applied or at least I can not figure it out from the examples. My data looks like the sheet below (abut 15,000 or so rows). This should be really simple but it does not seem to be. I need to average how many counts (column 1) there are for each of the binned area sizes (column titled Binned area(3)). I binned the particle sizes in 6-7 categories, in other cases there will be 15 bins. Under label, there are four different categories: 4 images from the same tissue slide lableled ....-1,....-2 etc. I need to average counts from all four images that fall within the same bin area range for each bin. I have tried every possible combination of the OVER statement, which I really don't understand and I can not get what I need. I have done the cross over table, but that only gives you the sum either by row or by column but not the average. Ifit is was only this graph, I could go to excel but I have a lot of images to analzyse and it will not be productive to do that way, so I am hoping there is awwa I can do it all here. Thanks for any help you can provide

 

 

 

Column 1 Label Area Binned Area (3)

1 D-50122 OR no bar 40x-1.jpg (Lab):a 0.388 x 0.50

6 D-50122 OR no bar 40x-1.jpg (Lab):a 0.582 0.50 < x 1.00

6200 D-50122 OR no bar 40x-2.jpg (Lab):a 1.843 1.00 < x 2.00

9914 D-50122 OR no bar 40x-3.jpg (Lab):a 0.291 x 0.50

11330 D-50122 OR no bar 40x-4.jpg (Lab):a 0.534 0.50 < x 1.00

[Fabian Duerr]

Just click on the small X-axis icon of your cross table and change the aggregation method. You'll find many more methods then just 'sum'.

Or use a bar chart to solve that. You don't even need your calculated column. You can bin directly on the axis of the chart. On the y-axis use the aggregation you need. Many tasks like that can be solved without any programming but simple clicking the right things in the chart. :)

Next time you provide data please put it in a format that can be used by other users easily (e.g. dxp file, excel sheet, table...)

[Aura Perez]

Sorry I am new to your wiki plataform and nto sure if my second inquiry got through so I am doing it again. I tried what you suggested and realized that I have done all of that before and none of that worked. I uploaded a simplified version of the file. The first page shows all of the different graphs that I have tried and none of them gives me what I want. The first groph shows how many instances per each of the images files that particualr range of particule sizes occures, that number is shown in the cross table as numbers. What I want is that average, the graph does nto allowed me to choose that, and when I tried to change the aggregation method in the cross table, either says I am not allwoed to change it or it gives me the average of the sizes in that range, NOT the average of how many times it occurres in that range. Forgot to say that I need especific bin ranges, so that is why I created my own.

[Fabian Duerr]

Sorry, can't see the uploaded file... Please check

[Aura Perez]

Sorry I am new to your wiki plataform and nto sure if my second inquiry got through so I am doing it again. I tried what you suggested and realized that I have done all of that before and none of that worked. I uploaded a simplified version of the file. The first page shows all of the different graphs that I have tried and none of them gives me what I want. The first groph shows how many instances per each of the images files that particualr range of particule sizes occures, that number is shown in the cross table as numbers. What I want is that average, the graph does nto allowed me to choose that, and when I tried to change the aggregation method in the cross table, either says I am not allwoed to change it or it gives me the average of the sizes in that range, NOT the average of how many times it occurres in that range. Forgot to say that I need especific bin ranges, so that is why I created my own.

[Fabian Duerr]

Okay, you are on the right track. You have to use the RowCount. And to get the average count you just have to divide by the number of labels, which is 4

 

So you either do:

 

count() / 4

 

Or more general:

 

count() / UniqueCount([label])

 

Is this what you were looking for

 

(PS: give it a meaningful display name)

 

[Aura Perez]

Thank you thank you, that is what I need. I did not realized I could change the axis using expressions, again thank you very much

[Fabian Duerr]

Sure. You are welcome.