6 Replies Latest reply on Oct 3, 2018 7:18 PM by Hari Ankem

# Percentage of aggregated data, exclude from denominator if numerator is null

I am trying to create an antibiogram that aggregates data from multiple facilities. The numerator data is the number of bacterial isolates tested that were susceptible to a given antibiotic. The denominator data is the total number of bacterial isolates tested against that antibiotic. This is displayed as a percent susceptible.

The problem I am encountering is when I have a null value in the numerator for a facility (they did not test that bacteria against that antibiotic), my calculation still adds the total number tested to the denominator - Percent Susceptible Calculated field in the workbook. I thought I could get around this by stating that if the numerator was null then add the total number tested to the numerator, but that skews the data - Percent Susceptible with Logic Calculated field.

I would really like to be able to aggregate the data but exclude the total number tested from the denominator if the numerator is null.

I have attached a packaged workbook and would appreciate any help that can be offered.

Thanks!

• ###### 1. Re: Percentage of aggregated data, exclude from denominator if numerator is null

Does this look right to you?

The updated workbook is attached.

1 of 1 people found this helpful
• ###### 2. Re: Percentage of aggregated data, exclude from denominator if numerator is null

Not exactly sure, but could you check the result s with this one?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: Percentage of aggregated data, exclude from denominator if numerator is null

Thank you so much - you solved this so quickly! I really appreciate your help. You used a simple, elegant calculation to make this work. I don't know why it was so elusive to me. Thanks again!

• ###### 4. Re: Percentage of aggregated data, exclude from denominator if numerator is null

Thank you so much. I appreciate your help. You used a little different approach, more like what I was trying to do - but I was focusing on the numerator, and should have used my logic toward the denominator. Thank you!

• ###### 5. Re: Percentage of aggregated data, exclude from denominator if numerator is null

You are very welcome.

Thanks,

Shin

• ###### 6. Re: Percentage of aggregated data, exclude from denominator if numerator is null

You are welcome Amanda.