3 Replies Latest reply: May 12, 2011 2:57 AM by Raghavendra Suryakumar

# Storing expressions in tables

Hi All,

We have a scenario wherein the a lot of fields are calculated values and again these calculated fields are used in next stage for deriving conclusions.  Can somebody let me know if we can store the values derived from first stage into a table and later use the table values for deriving conclusions.

I am trying to explain the process with help of an example.  Please note that we can't variables here because we are using calculated fields for more that 10,000 lines of data.

1st stage

Product      Sales Data      Calculated field 1   (based on sales data some calculated values are derived with the help multiple factors)

1                   1000                   xxx

2                    2000                  YYY

3                    3000                  ZZZ

2nd Stage

Product    Calculated Field 1           Calculated field 2             calculated field 3

1                   xxx                                   AAA                            EEE

2                   YYY                                  BBB                            FFF

3                   ZZZ                                    CCC                            GGG

3rd Stage

Product           Conclusions

1

2

3

The conclusions we have to derive is again based on Calculated Fields 1,2,3 based on different market variables.  Can anybody suggest if can create a table for loading Calculated fields which automatically gets updated with change in sales data.

Please let me if anyone have encountered similare situation.

Thanks and Regards,

Raghav

• ###### Storing expressions in tables

Hi,

The only thing that springs to mind is something we've done for margin calulations and work in progress values. I've simplified it a bit here.

We have a Job Number column for identifying the specific job and then we have a Total Price column and Total Cost column summing up the relevant price and cost fields. We then use the column headings to get Total Price/(Total Price-Total Cost).

I'm guessing that you're doing something more complicated but just thought I'd mention it in case it gives you another way of thinking about the problem. Could you create a Calculated Column 1 and then use this column in the formulas for the other columns? You could always hide the columns that you don't want to show.

Regards,

Chris

• ###### Storing expressions in tables

Hi Chris,

The logic you have mentioned works if have all the calculated fields in one single table.  We have a situation wherein the conclusion should be done in a different table and different sheet/tab.

Regards,

Raghav

• ###### Storing expressions in tables

Hi,

Can anybody help me pls.

Regards,

Raghav