averaging across pivot tables?

mtsmts Dr. Robot KingRegistered User regular
So I am trying to consolidate data from multiple pivot tables into one output table and can't seem to figure it out

I am looking at a range of data that has both biological and technical replicates - each subject has three slides. 3 subjects per group

The data is cross sectional area of a fiber across a field of view of a microscope.
However since there are variations between samples, not every sample has the same values

I have all the data grouped the same (0-250, 251-500...) but not every field has values in every range

I would like to take the per-subject average and then get the group average but they don't have to be the same table and reckon the method would be the same regardless.

i tried averageif statements and the multiple consolidation thing in the wizard but couldn't get either to work.

camo_sig.png

Posts

  • DivideByZeroDivideByZero Social Justice Blackguard Registered User regular
    Do you want to return a value of zero if it's not found in the range?

    You can use a vlookup inside an IFERROR function to return a default value instead of a #N/A

    First they came for the Muslims, and we said NOT TODAY, MOTHERFUCKERS
  • mtsmts Dr. Robot King Registered User regular
    basically what I want is it to get the average value for the ones that have an entry and ignore zeros/no-entry rather than spitting back a div error

    e.g.

    if 3 samples

    5 , 4, 0

    it would give me 4.5

    the averageif spit back a #div if there was a non value. wonder if having it put zeros instead of no value will fix it?

    camo_sig.png
  • ElvenshaeElvenshae Registered User regular
    You could do a sum (range) / countifs (range, > 0)

    omgbfz5lzi1s.png
    Steam: Elvenshae // PSN: Elvenshae // WotC: Elvenshae
    The Disappearance of Inigo Sharpe: Tomas à Dunsanin
    Jebus314Pailryder
  • mtsmts Dr. Robot King Registered User regular
    I think I figured it out. you can use the multiple consolidation thing in the pivot wizard and go from the pivot table itself it just needs to be very specific on the source to get the right labels

    camo_sig.png
Sign In or Register to comment.