SumProduct Round IF

[1FJef]

Greetings! I've boldened the portion of my formula in question, below. The columns [Unit Value] and [Mean] primarily contain numbers with 8 decimal places. My First Formula works well - it basically looks for each instant where the data in column Unit Value equals or exceeds the data in column Mean. However, I needed for the comparison to be based only on the rounding of each number to 2 decimal places. Thus, I created the Second Formula.
=IF(Y$11="","",SUMPRODUCT(--(Table2[Unit Value]>=Table2[Mean]),--(Table2[Measure]='HM_Teaching MEAN'!Y$11),--(Table2[Year]='HM_Teaching MEAN'!$B$7),--(Table2[Indicator]=LOOKUP("zzz",$C$10:Y$10)),Table2[Unit Participation Lookup]))

• Added ROUND Function: SUMPRODUCT(--(ROUND(Table2[Unit Value],2)>=ROUND(Table2[Mean],2))

Here is my problem. Both the Unit Value and Mean columns also contain text. This text is erring out because the ROUND function cannot round text.

• Question: Where can I insert an IF function in my formula to tell the formula to only try to round the values in these columns where those values are not text?

I should note, for other calculations, I cannot remove the text fields from the columns; I have to find a way to ignore them. Thanks!

 

[1FJef]

Joeu2004 and Aladin - can't tell you how appreciative I am. Ended up using the 2nd formula you provided, Joeu, which you shared would be more efficient. Works perfectly - thanks a ton!

 

[Aladin Akyurek]

I don't think the suggestion I made is inefficient or less efficient at all.

 

[joeu2004]

I don't think the suggestion I made is inefficient or less efficient at all.

I concur to the extent that any difference probably is not noticeable. I think the more significant issue is the handling of "numeric" text and whether or not it makes a difference to the user.