max(if(range_criteria = criteria, value_criteria) ignoring criteria in the search

[LlebKcir]

to test, I simplified the formula to focus on 1 network with 4 sub-networks. the expected output is 4 different MAX values, one for each sub-network.

=MAX(IF(tOrlando[City/Network] = [Underground Network Name], tOrlando[Group]))

This is run within the tNetworkname table, so [Underground Network Name] Excel is removing the tNetworkName from that portion of the code. I find that irritating, but thats what Excel does.

In column [Underground Network Name] and in column tOrlando[City/Network] there are 4 sub-networks: Orlando - North, Orlando - South, Orlando - West, Orlando - East.

Only Orlando - North is reporting a value, but it is the wrong value. It is the Max for all sub-networks in the tOrlando[Group] column thus the Max(If is ignoring the criteria of [Underground Network Name] for the search.

edit to add: Current output is Orlando - North (wrong value), all others have a 0 as their output. All outputs are drastically off from the correct value.

1. Why is it ignoring the criteria value
2. How do I fix this?

 

[RoryA]

Your [Underground Network Name] reference is to an entire column, not a specific cell - I suspect that was not your intention? For a cell in the current row, there should be an @ symbol at the start.

 

[LlebKcir]

You are correct, that was not my intent, sadly making that change did not help:

=MAX(IF(tOrlando[City/Network] = [@[Underground Network Name]], tOrlando[Group]))

The output is still the same, Orlando - North is a max, but it is the max for all under [Group] column, South, West, and East are all 0 value output.

 

[LlebKcir]

also to add, just in case there was any kind of typing difference between the value in the [Underground Net...] column matching values in tOrlando[City/Network] I did a copy/paste value into [Underground Net...] column to verify the data was identical.

 

[LlebKcir]

Corrected my error: forgot to shft+crtl+enter... duh.

Now I am getting expected results when hard coded, getting 0 when trying to use full referencing ;(

=max(if(IFERROR("t"&LEFT(tNetworkName[Underground Network Name],FIND(" ",tNetworkName[Underground Network Name])-1)&"[City/Network]","t"&(tNetworkName[Underground Network Name]&"[City/Network]")) = [@[Underground Network Name]], IFERROR("t"&LEFT(tNetworkName[Underground Network Name],FIND(" ",tNetworkName[Underground Network Name])-1)&"[Group]","t"&(tNetworkName[Underground Network Name]&"[Group]"))))

Now excel changes the above to:

=MAX(IF(IFERROR("t"&LEFT([Underground Network Name],FIND(" ",[Underground Network Name])-1)&"[City/Network]","t"&([Underground Network Name]&"[City/Network]")) = [@[Underground Network Name]], IFERROR("t"&LEFT([Underground Network Name],FIND(" ",[Underground Network Name])-1)&"[Group]","t"&([Underground Network Name]&"[Group]"))))

IFERROR("t"&LEFT([Underground Network Name],FIND(" ",[Underground Network Name])-1)&"[City/Network]","t"&([Underground Network Name]&"[City/Network]")

produces tNetwork[City/Network] so produces the same effect as tOrlando[City/Network] in the hard coded formula.

= [@[Underground Network Name]] works great thank you for reminding me to add the @

IFERROR("t"&LEFT([Underground Network Name],FIND(" ",[Underground Network Name])-1)&"[Group]","t"&([Underground Network Name]&"[Group]")

Produces the desired tNetwork[Group], just as tOrlando[Group] again when hard coded.

All values are now 0. Oh the joys of not being a programmer expected to code.

 

[LlebKcir]

so took it a step further, build a different set of data using concat to build out tNetwork[City/Network] and tNetwork[Group] and that is still producing zero

 

[LlebKcir]

Sorry, stupid IT here and their horrid proxy server caused upload issues for that post.

to finish the statement:

producing zero output.

{=MAX(IF(Sheet1!J2 = [@[Underground Network Name]], Sheet1!K2))}

The values on Sheet1 are just concatanated for "t", Network, [City/Network], or [Group] to generate the formula. Still zero.

 

[LlebKcir]

=Sheet1!J5

output =

tOrlando[City/Network]

=Sheet1!K5

output =

tOrlando[Group]

=INDEX(tNetworkName[Underground Network Name],5)

output =

Orlando - East

But when I attempt to wrap all of that together the output is 0

{=MAX(IF(Sheet1!J5 = INDEX(tNetworkName[Underground Network Name],5), Sheet1!K5))}

Why? That should build the correct formula

{=MAX(IF(tOrlando[City/Network] = INDEX(tNetworkName[Underground Network Name],5), tOrlando[Group]))}

Why are the two formulas not providing the same results?

 

[RoryA]

As I said earlier, you need INDIRECT to convert text to an actual range reference.

 

[LlebKcir]

yes, tried that in post #7, thus my confusion.