What functions to use in tables when they are sorted and new data is entered.

Hi,

I am new to this forum and I am sorry if this thread is posted in the wrong section.

My problem is that I would like to sort a table by size when the table cells have a function based on other tables that change, without messing up the formulaes.

If you take a look at the enclosed .xls file there are five sheets;
Stats - front page with some statistics
Settlement summary - where all the settlements are entered after completion of funds collection (based on date from outside this xls)
Collection deviates - where all the insurers who did not pay within due date is entered (based on date from outside this xls)
--statsSource-- - contains lists based on the Collection deviates sheet
--listSource-- - contains lists that are used in the entry of the Settlement summary and Collection deviates sheet

The two latter will be hidden once the project is complete.

In the --statsSource-- sheet you can see that I have made three tables, one that finds the total no of incidents pr insurer from the Collection deviates sheet ith the function; “ =COUNTIF('Collection deviates'!D3:D1016;B3) ” and one that finds the total no of days any insurer is late also from the Collection deviates sheet with the function; “=SUMIF('Collection deviates'!$D$4:$D$999;E3;'Collection deviates'!$G$4:$G$999) “. These tables is now sorted alphabetical, but should be sorted by size, to make the graphs in the Stats sheet look smoother.

The third table is a combination of the two earlier, where I try to find the average days pr incident per insurer, thus the function; “ =IF(ISERROR(F7/C7);0;F7/C7) “. My problem is that when the two first tables get sorted, and change the last table gets messed up. The two first tables will change with use of the .xls, since they are based on data that change over time, whenever a new claim is registered in the Collection deviates as a matter of fact.

As you can see I have added a check column with the correct sums to the right of the third table and a graph at the bottom.

The thing I wonder is, what functions should I use to accomplish the calculation of average days per insurer using these tables? I have tried the usual $ in all possible combinations I can think of, but it does not point to the correct cells when it changes.

Thanks
Øyvind Krapf-Sterner