CODEWITHSUNDEEP
excelmacosif-statement
XLSlearner
XLSlearner

Reputation: 1

MAX IF function that refers to other sheet

I have a sheet in my workbook called Group Dashboard. On that sheet I am entering a formula referring to another sheet. 

=MAX(IF('Worker Dashboard'!C:C=1,'Worker Dashboard'!F:F))

I want it to look through all the data in Worker Dashboard. For each line where the value in the C column is 1 (or 1.0), I want it to consider the value in Column F. It should then output the MAX value among the considered values from Column F.

This formula seems to work fine.

However, when I then enter the following:

=MAX(IF('Worker Dashboard'!C:C="<1",'Worker Dashboard'!F:F))

It just reads 0. The max value should not be 0. Column C in that sheet has values <1 such as 0.8, 0.6, 0.5, etc. If I change the formula to:

=MAX(IF('Worker Dashboard'!C:C="<1",'Worker Dashboard'!F:F,-1))

Then the output is -1. This makes me think there is an issue with the logic statement:

'Worker Dashboard'!C:C="<1"

I have also tried variations for other purposes where the logic statement is:

=MAX(IF('Worker Dashboard'!B:B="General",'Worker Dashboard'!F:F))

However, here too it spits out 0 even though the max is 5.0 for rows where Column B lists General and the value in Row F is considered.

Upvotes: 0

Views: 3197

Answers (1)

user4039065
user4039065

Reputation:

There are two points that I've found from your description of the problem.

  1. Use 'Worker Dashboard'!C:C<1 not 'Worker Dashboard'!C:C="<1". Comparing a true number to the textual representation of that number will work with some functions like the COUNTIF function but not all. Best to compare numbers to numbers and test to text whenever possible.
  2. Your formula looks to be of the array formula variety. Array formulas need to be finalized with Ctrl+Shift+Enter↵ and not just Enter↵. When entered correctly with CSE, Excel will wrap the formula in braces (e.g. { and }). You do not type these in yourself. Once entered correctly, the formula may be copied/filled to new locations with conventional methods like Ctrl+D (aka Fill Down) and the CSE will carry along.

Upvotes: 1

Related Questions