Skip to main content
# Table 1 Excel Formulae used in spreadsheet analysis

From: A new spreadsheet method for the analysis of bivariate flow cytometric data

Formula A (average)
| {=(IF(D3<4,"x",AVERAGE(IF($A$3:$A$60000=$A2,$B$3:$B$60000))))} |

Formula B (cell count)
| =COUNTIF($A$3:$A$60000,$A2) |

Formula C (standard deviation)
| {=IF(B3="x","x",(STDEV(IF($A$3:$A$60000=$A2,$B$3:$B$60000))))} |

Formula D (variance)
| =IF(B3="x","x",C3^2) |

Formula E (99.9% confidence interval)
| =IF(B3="x",0,CONFIDENCE(0.001,C3,D3)) |

Formula F (paired students t-test)
| =IF((AND(B3<>"x",N3<>"x")),(ABS(B3-N3))/(SQRT((F3/D3)+(R3/P3))),"x") |

Formula G (degrees of freedom)
| =IF((AND(B3<>"x",N3<>"x")),((D3+P3)-2),"x") |

Formula H (critical t value 99.9%)
| =TINV(0.001,T3) |

Formula I (Significance at 99.9%)
| =IF((AND(B3<>"x",N3<>"x")),(IF((H3>(LOOKUP(I3,$T$3:$T$1002,$V$3:$V$1002))),980,-50)),-50 |