Averaging Survey Questions for Multiple Users by Respondent

I have multiple surveys with multiple respondents. Not every survey question is mandatory, so the number of responses is not always the same as the number of responders times the number of questions.

I want to find the overall average for each survey by first averaging by respondent and then also find the average by each question. An example is attached, with Sheet1 being the data, and Sheet2 being the desired output. I am anticipating getting over 2,000 surveys so any method to automate this process would be greatly appreciated.

In other words, I want to find the average for each question, treating each answer equally, but for the overall average, I want to treat each respondent equally, regardless of how many questions they've answered.

Thank you very much for your response.

Okay, so for E2, SURVEYID 41346. I first filter by SURVEYID on SHEET1, so 41364. I then filter by RESPONDENTID one by one. So 101646 has answers one question, so this respondent's average is 4. 101649 response's average to 4.5, 101647 is 4, 101650 is 3.33333, 101651 is 4.5. I then average all of those scores to get 4.666666.So E2 is not the average of the question averages, but the average of the respondent's overall answer(s). If all of the respondents answer the same number of questions, these averages are the same, but when respondents answer a different number of questions, the numbers are different.