Compare multiple names

[gfagan]

I have several named cells each of which may contain a numeric value or the text string “NA” for not applicable. I need to A) compare a variable number of these names to see if they all equal “NA” and if not then I need to B) find the highest numeric value among them (and apply an offset). Is there a straightforward way to do this without macros? I also need to share the spreadsheet with people who may have macro security enabled.

Let’s start with B, what I need is just the MAX function, except that while it ignores text strings in cells referenced directly, it throws an error when referencing a cell by name that contains text. Any workarounds?

For A, I can’t seem to find any comparison function that compares more than 2 elements, something like EXACT but accepting multiple arguments. Since most of the numbers are positive integers, I also tried SUM(Alpha,Bravo,Charlie) thinking that if the sum equaled zero then either I had the text string in all names or else an edge case that I might be able to live with, but SUM has the same problem as MAX, it ignores text in cells referenced directly but not those referenced by name.

I have an interest in keeping the cell contents grokable at a glance, in other words I seek a simpler version to do the following, potentially with more than 3 names:

=IF(AND(Alpha=“NA”,Bravo=“NA”,Charlie=“NA”),“NA”,IF(AND(Alpha=“NA”,Bravo=“NA”),Charlie-3,IF(AND(Bravo=“NA”,Charlie=“NA”),Alpha-3,IF(AND(Alpha=“NA”,Charlie=“NA”),Bravo-3,IF(Alpha=“NA”,MAX(Bravo,Charlie)-3,IF(Bravo=“NA”,MAX(Alpha,Charlie)-3,IF(Charlie=“NA”,MAX(Alpha,Bravo)-3,MAX(Alpha,Bravo,Charlie)-3)))))))

I might be able to swap an out-of-range numeric value for “NA” but then in the original cells I’d need formlæ to present “NA” instead of say -100. Expected range for the original cells is -6 to 25 and the offset ranges from -40 to zero. The number of names involved is probably less than 52, and I might be working with half a dozen at a time for these comparisons.

Actual use-case is a character sheet for a role-playing game. Characters are magicians who know magic skills, spells are based on skills at a penalty depending on difficulty, but some spells are available with more than one skill, and a given character probably won’t not know all of the skills. So, while this is vexing me, it’s for a hobby: The stakes aren’t high.

Thanks,

GEF

[gfagan]

Aha! Some of my problems come from having names that don’t actually refer to a cell, but just have “NA” as their value. I just figured out that if Alpha and Bravo point to A1 and A2, but Charlie is defined as “NA”, then =COUNTIF((Alpha,Bravo),“NA”) returns 2 but =COUNTIF((Alpha,Bravo,Charlie),“NA”) returns an error. Maybe everybody else knows this, but to me it seems counterintuitive. In any case, I can add a cell with “NA” for all these names to reference.

[karolus]

Hallo

=IF(COUNTIF((alpha~bravo~charly);"na")=3;"na";MAX(alpha;bravo;charly)-3)

works for me…
but why do you distribute the values over drölfzich individually named cells instead of putting them in a contiguous cell range…
so I would suggest something like:

=IF(COUNTIF(big_named_cellrange;"na")=COUNTA(big_named_cellrange);"na";MAX(big_named_cellrange)-6)

[gfagan]

Wow, that was quick. Thanks karolus.

I just tried to answer your question and deleted my answer because I found myself having to explain the mechanics of the role-playing game. Let me know if you’re into that and I’ll try again.