I solved it never mind. Thanks if anyone was looking at this.
I have a sheet that is moving very slowly, in fact the entire page doesn't seem to work when I have the sheet open. It is less than 30 columns and 100 rows, however it has many lookups and column references due to the nature of what I am trying to do. below is a sample of the code which is repeated down 3 columns for 24 total work groups (I've shown 5 of the workgroups). I haven't found any other way to do what I want and this works perfectly in getting me the numbers I desire, but the speed makes it almost un-useable.
What the formula does- Searches for 2 values which I've concatenated into a helper box and returns a 3rd value on the same row. Repeats for each area in the specified facility, then averages them together and posts. The form is updated via webform and is not updated in a specific order, hence the column lookups, so I can use the sort function and have my worksheet be organized.
I'm still on a temporary account, but we have purchased some users and I will have the full access within the next couple days if that effects the speed at all.
I'd appreciate any feedback to be able to speed this sheet up. I'd prefer to keep it on a single page as I am going to draw a lot of data from this sheet to other sheets, but if it isn't possible I might end up having to use multiple sheets.
=IF($Department1 = "1. Production PL17", AVG(LOOKUP("1. Production PL17" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("2. DF Dieshop" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("3. DF Maintenance" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("4. Quality" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("5. Storeroom-Logistics" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("6. Offices" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false)),