Thanks to June 7th, we see where we were off. Classic case of overthinking the problem.
This is the working code to get the sum.
Code:
=IIf([txtPFOS]<>"ND" And [txtPFOS]>2,[txtPFOS],0)+IIf([txtPFOA]<>"ND" And [txtPFOA]>2,[txtPFOA],0)+IIf([txtPFHxS]<>"ND" And [txtPFHxS]>2,[txtPFHxS],0)+IIf([txtPFNA]<>"ND" And [txtPFNA]>2,[txtPFNA],0)+IIf([txtPFHpA]<>"ND" And [txtPFHpA]>2,[txtPFHpA],0)+IIf([txtPFDA]<>"ND" And [txtPFDA]>2,[txtPFDA],0)
To finish off, we need to either put "ND" in the txtbox for txtSumof6Compounds or round the sum (if there is a sum) to 3 significant digits.
Note - we use a function I think I found on this site to put numbers into sig figs. Called sigrnd.
I got as far as figuring out how to put ND or the word Round in the txtSumof6Compounds text box:
Code:
=IIf((IIf([txtPFOS]<>"ND" And [txtPFOS]>2,[txtPFOS],0)+IIf([txtPFOA]<>"ND" And [txtPFOA]>2,[txtPFOA],0)+IIf([txtPFHxS]<>"ND" And [txtPFHxS]>2,[txtPFHxS],0)+IIf([txtPFNA]<>"ND" And [txtPFNA]>2,[txtPFNA],0)+IIf([txtPFHpA]<>"ND" And [txtPFHpA]>2,[txtPFHpA],0)+IIf([txtPFDA]<>"ND" And [txtPFDA]>2,[txtPFDA],0))=0,"ND","Round")
I tried replacing "Round" with code for summing, and keep getting errors.
I imagine I want to use the code for summing in the first code snippet above to replace "Round".
What I would really like to do is something like
Code:
sigrnd(txtSumof6Compounds,3) 'of course a circular reference.
So...How do I replace "Round" with the sum rounded to 3 sig figs (using the sigrnd function)