2 Replies Latest reply on Jul 14, 2017 6:33 AM by Mihai Constantinescu

    LOD and Averages at a Specified Level

    Steve Kline

      My data has planned capital equipment spending by machine type (defined by dimensions Question ID = 2 and Wording) for unique facilities (Respondent ID).

       

      I first created a set of Usable Respondent IDs to remove any facilities that planned to spend less than $1,000 and more than $50,000,000 as these are not viable responses.

       

      On the sheet Distinct IDs by Plant size, I have a simple bar chart showing the count of distinct IDs for a given plant size and then average spending per plant facility in that plant size. The average spending per facility on this sheet is what I want.

       

      The sheet Spending of Usable Surveys shows the five plant sizes with the unique Respondent IDs in each plant size. It then shows there planned Spending (which can be many up of any number of machine types).

       

      What I need is the average spending for each plant size (for plant size 1 this is $272,227) on the row with each Respondent ID. I think I need a Fixed LOD calculation. However, I cannot figure out how to do this. This seems like it should be relatively but I am stumped.

       

      On the Spending of Usable Surveys sheet you can see a number of attempts that I have made.

       

      Your help would be greatly appreciated.