Getting max value from field in Field Calculator of ArcMap

Question

I'm working from a geodatabase in ArcMap 10.5, and using Model Builder.

I currently have a field with values that range from 0 to 30 and I want to convert that to a 0-10 scale. The idea was to use a formula like:

([VALUE]/[MAX VALUE])*10

Which, after some minor research, I assumed would be something like this in Field Calculator (using...):

(INDEX/max(INDEX))*10

But it doesn't work. The calculator does not return any error, it just doesn't give the exepected results. I tried adding [] to the max function, adding some extra fields so i has something else to search on, etc, but I always get one of these results:

• All with value = 1 and some NULLs (which is odd 'casue there are no initial NULL values)
• All with value = 10 and some NULLs (same as above in this case)

Any of these makes sense taking the formula in mind, but there is also no error message, so I assume there is some piece I am missing to make the calculation work correctly.

How can I (if it's even possible) get the max value in a field to calculate this formula?

Doesn't matter if it's VB Script or Python (currently using the latter) or even if it's some workaround, as long as it works.

Answer 1

Just right click on the field header of the INDEX field and choose Statistics, you will be presented with some basic but useful stats, including maximum value. Note this will honour any selection on your table.

The reason why your approach has not worked is because to answer the question what is the maximum value would require you to traverse the table searching for that first but you have embedded the max() function into an operation that steps through the table one row at a time. Chicken and egg scenario! So you either use the summary tool to find the maximum value and store it away or use the approach I have suggested above.

Answer 2

If you fancy python (it can be run in the python console or standalone):

import arcpy

## input file
file = r'input_shapefile_or_other_file.shp'

field_to_max = 'from_node' ### field with values
field_to_update = 'grid_code' ### create new field (before), which will be updated

values = []
with arcpy.da.SearchCursor(file, [field_to_max]) as cursor, arcpy.da.UpdateCursor(file, [field_to_max, field_to_update]) as upd_cursor:
    for row in cursor:
        values.append(row[0])
    max = max(values) ### get max value
    for row in upd_cursor:
        value = (float(row[0]) / float(max)) * 10

        row[1] = int(value) ### calculate value
        upd_cursor.updateRow(row) ### set value