Comparing two money columns


#1

In a long list that includes two money columns for each record, how can I select the higher amount?


#2

I’m not sure what you mean by “select the higher amount”. Do you want to fill a third field with whichever value is higher? In a procedure, you could do something like this, of course substituting your actual field names instead of A, B and C.

field C
formulafill max(A,B)

Manually, you would do this with the Manipulate Data in Field dialog, choosing the Start with Formula option and using the max( function as I show above.


#3

Done. Thanks Jim … you’re the best!


#4

What if there are more than 2 money columns? I tried the same procedure using the column names separated by comma but that didn’t work. I also tried the Mini Statistics Wizard but I get an error message saying Fixed Point Overflow. What I’m trying to do is forecast a dollar amount per record based on 4 money columns in a file of 600K records.


#5

You could nest several max( functions together to achieve the result:

field E
formulafill max(max(max(A,B),C),D)

#6

Thanks Gary! Any way I can forecast an amount based on multiple past data?


#7

I believe you’re wanting to average the existing amounts for your forecast. That would be: A+B+C+D/4