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!

 

[joeu2004]

Array-enter (press ctrl+shift+Enter instead of just Enter) the formula that contains the following:

SUM(IF(ISNUMBER(Table2[Unit Value],IF(ISNUMBER(Table2[Mean],--(ROUND(Table2[Unit Value],2)>=ROUND(Table2[Mean],2))))

We cannot use AND() because it evaluates the entire range, not row-by-row as you need.

-----

The following might work (untested) when you normally-enter the formula (just press Enter as usual):

SUMPRODUCT(--(ROUND(N(Table2[Unit Value]),2)>=ROUND(N(Table2[Mean]),2))

IIRC, the N() function does not work as intended with SUMPRODUCT; at least, not when normally-entered. So test this carefully using Formulas > Evaluate Formula and a very small range for Table2[Unit Value] and Table2[Mean] so that you read the EF results.

 

[1FJef]

Thanks a ton for your help! I've replaced the portion of my old formula with your suggestion and performed Array-enter. Receiving the error "You've entered too many functions for this argument." Something else I'm missing?
Old 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]))

New Formula: {=IF(Y$11="","",SUM(IF(ISNUMBER(Table2[Unit Value],IF(ISNUMBER(Table2[Mean],--(ROUND(Table2[Unit Value],2)>=ROUND(Table2[Mean],2)))),--(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]))}

 

[Aladin Akyurek]

@ 1FJef


SUMPRODUCT can be invoked to do either a sum or a count. Which do you intend?

 

[1FJef]

Hi Aladin, Intention is to sum column [Unit Participation Lookup] column for all instances which meet the formula's criteria. The formula is doing this correctly. My only issue is needing to ROUND each of the individual [Unit Value] and [Mean] columns ONLY where the data in those two columns are not text. Trying to round the data in the columns which contain text is producing a rounding error.

Year | Unit Value | Mean | Indicator | Measure | Unit Participation Lookup
2018 | 2.62 | 2.78 | Staffing | Support | 1
2017 | 1.68 | Text | Staffing | Quality | 1
2017 | Text | 1.55 | Task | Support | 1

 

[Aladin Akyurek]

What is the desired result for the small sample of post #5 ?

 

[1FJef]

0 would be the desired result.

Breaking it down would show:
Row one. 2.62 [Unit Value] is neither greater than nor equal to 2.78 [Mean] - therefore does not sum the value in [Unit Participation]. Result = 0
Row two. Ignore, since the [Mean] value is text. Result = 0
Row three. Ignore, since the [Unit] Value is text. Result = 0

I'm adding additional rows for additional clarity:
Year | Unit Value | Mean | Indicator | Measure | Unit Participation Lookup
2018 | 2.6255 | 2.6241 | Staffing | Support | 1
2017 | 1.7895 | 1.7914 | Staffing | Quality | 1

Row four. 2.6255 [Unit Value] rounds to 2.63 and is therefore greater than 2.6241 [Mean] which rounds to 2.62 - therefore does sum the value in [Unit Participation]. Result = 1
Row five. 1.7895 [Unit Value] rounds to 1.79 and is therefore equal to 1.7914 [Mean] which also rounds to 1.79 - therefore does sum the value in [Unit Participation]. Result = 1.

Thanks for your help with this!

 

[joeu2004]

@1FJef.... Obviously, your intent with SUMPRODUCT(--(ROUND(Table2[Unit Value],2)>=ROUND(Table2[Mean],2)) in posting #1 is neither to count nor sum, but to excerpt that array of TRUE/FALSE values that is part of the greater SUMPRODUCT.

And to that extent, I erred in replacing SUMPRODUCT with SUM. Sorry about that.

The syntax error in the new formula in posting #3 was my fault. I was missing the closing parenthesis for both ISNUMBER functions, highlighted in red below.

The correct formula (I hope) is the following, still array-entered (press ctrl+shift+Enter instead of just Enter) despite the use of SUMPRODUCT. Also note some other changes, highlighted in red.

=IF(Y$11="","",
SUMPRODUCT(--IF(ISNUMBER(Table2[Unit Value]),IF(ISNUMBER(Table2[Mean]),ROUND(Table2[Unit Value],2)>=ROUND(Table2[Mean],2))),
--(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]))

Some additional improvements to consider.

1. If this formula is entered into worksheet 'HM_Teaching MEAN', the references to Y$11 and $B$7 can be just that, without the worksheet qualifier.

2. The use of separate array paremeters is inefficient in space, insofar as temporary memory must be allocated to each "conditional" parameter of the form --(comparison). The following is more efficient in space in that regard. It might be unimportant if the size of the tables is relatively small. Also, the following form might be less efficient in execution. That is unclear; and the difference might not be noticeable. It is your call to make.

=IF(Y$11="","",
SUMPRODUCT(IF(ISNUMBER(Table2[Unit Value]),IF(ISNUMBER(Table2[Mean]),ROUND(Table2[Unit Value],2)>=ROUND(Table2[Mean],2)))
* (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]))

 

[Aladin Akyurek]

0 would be the desired result.

Breaking it down would show:
Row one. 2.62 [Unit Value] is neither greater than nor equal to 2.78 [Mean] - therefore does not sum the value in [Unit Participation]. Result = 0
Row two. Ignore, since the [Mean] value is text. Result = 0
Row three. Ignore, since the [Unit] Value is text. Result = 0

I'm adding additional rows for additional clarity:
Year | Unit Value | Mean | Indicator | Measure | Unit Participation Lookup
2018 | 2.6255 | 2.6241 | Staffing | Support | 1
2017 | 1.7895 | 1.7914 | Staffing | Quality | 1

Row four. 2.6255 [Unit Value] rounds to 2.63 and is therefore greater than 2.6241 [Mean] which rounds to 2.62 - therefore does sum the value in [Unit Participation]. Result = 1
Row five. 1.7895 [Unit Value] rounds to 1.79 and is therefore equal to 1.7914 [Mean] which also rounds to 1.79 - therefore does sum the value in [Unit Participation]. Result = 1.

Thanks for your help with this!

Thanks tons for the expected result and procedural clarification.

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(Table2[[ Unit Value ]]*Table2[[ Mean ]]),IF(ROUND(Table2[[ Unit Value ]],2)>=ROUND(Table2[[ Mean ]],2),Table2[[ Unit Participation Lookup]])))

You can easily expand this formula for more conditions.

 

[joeu2004]

IF(ISNUMBER(Table2[Unit Value],IF(ISNUMBER(Table2[Mean]),...

IF(ISNUMBER(Table2[[ Unit Value ]]*Table2[[ Mean ]]),...

Ordinarily, I would favor the first form because I suspect it is more efficient in both time and space.

But the two forms do not always have the same results(!). That got me to wonder which one is more correct for 1FJef's purposes. Of course, only he can say.

Consider the case where in one row, Table2[Unit Value] and/or Table2[Mean] is "numeric" text of the form "12.3456". ROUND treats that text as numeric. So in 1FJef's original "second formula", that row would not be ignored, and it would not throw a #VALUE error. Whether or not such "numeric" text behaves as intended for the rest of the SUM(PRODUCT) subexpressions is anyone's guess.

If that is desirable, Aladin's ISNUMBER expression is more correct. My ISNUMBER expressions would treat those values as text, and they would cause the row to be ignored in the sum.

On the other hand, consider the case where in one row, Table2[Unit Value] and/or Table2[Mean] is text of the form "12-10". Excel might interpret such text as a date and convert it to its date serial number when referenced in an arithmetic expression and as a ROUND parameter. On the other hand, text of the form "35-45" would not be interpreted as a date. And again, whether or not such "numeric" text behaves in a meaningful way for the rest of the SUM(PRODUCT) subexpression is anyone's guess.

If that is not desirable, as I'm sure it is not, my ISNUMBER expressions is more correct because, again, they would consistently treat those values as text, whereas Aladin's would not.
@1FJef, it boils down to you deciding just how flexible you want the recognition of text to be.

-----

As for the difference between Table2[[Unit Value]] and Table2[Unit Value].... I am not familiar with the table reference syntax. I simply copied the form that 1FJef used (the latter), assuming that it worked for him.

Perhaps Aladin can comment on why he chose an alternate form, if it is better or more correct in some respect.