Dynamic Field Formulas

Question

I am trying to dynamically add a large number of fields (>150) that references the field previous (as well as two static fields) for the calculation. For example, in the following table, each consecutive field is calculated based on the previous until the min is reached, at which point it goes back to max.

RateMaxMinM 1M 2M 3#10.11029.99.89.7#20.5433.53.04.0#3162543

Is there a way to do this dynamically where each field references the field previous rather than typing in each formula? Additionally, can it be done where the appended range is dynamic (i.e. can do M1-M10 or M1-M100)?

Thanks!

MarqueeCrew · Accepted Answer

@owill,

Yes!  No macro required!

You will transpose the data and keep the "static" data as keys.  Then you'll use a formula that groups by ID (I checked the wrong box in my configuration in the picture, but fixed it in the post).  I created a formula near yours and called it new_value.  Then I used the crosstab tool to horizontally view the data.

There is a version 10.x and a version 11 sample for you to work from.

Cheers,

Mark