analyse survey (array formula???)

Topic

New Reply

Column A in a list of survey results is Products.
Column B is users’ assessments of ‘quality’ of that product, scale 1 to 5 or blank (= don’t know or can’t be asked).
In cells elsewhere I want, for each of product1, product2 etc the Max, Min and Average score. [Ignore the blanks.]
If someone can start me on any one of these, I think I can work out the others.
Thanks in advance.
JRR

Reply | Quote

Replies

Column A in a list of survey results is Products.
Column B is users’ assessments of ‘quality’ of that product, scale 1 to 5 or blank (= don’t know or can’t be asked).
In cells elsewhere I want, for each of product1, product2 etc the Max, Min and Average score. [Ignore the blanks.]
If someone can start me on any one of these, I think I can work out the others.
Thanks in advance.
JRR

The easiest solution is a pivot table.
A pivot table requires that the source table contains field names (column headers) in its first row. If you don’t have those, insert them before proceeding.
Click in any non-blank cell in the table.
Select Data | Pivot Table and Pivot Chart Report…
Excel should select the entire table.
Click Next, then Next again.
Click Layout…
Drag the column A header to the Row area.
Drag the column B header to the Data area.
Double click the button in the data area.
Select Max as summary function.
Drag the column B header to the Data area (yes, again!)
Double click the button in the data area.
Select Min as summary function.
Repeat another time, this time selecting Average as summary function.
Click OK.
You can choose whether you want the pivot table to be created on a new worksheet or on an existing worksheet (and specify where it should be).
Click Finish.

Note: pivot tables aren’t updated automatically if the source data change. To update, click in the pivot table and click the Refresh Data button on the Pivot Table toolbar (the exclamation mark).

Reply | Quote

cool, Hans.
Is there a way using an ‘event’ (eg any change to the results list) to provoke the pivot table to be refreshed?

Note: pivot tables aren’t updated automatically if the source data change. To update, click in the pivot table and click the Refresh Data button on the Pivot Table toolbar (the exclamation mark).
[/quote]

Reply | Quote

Yes, using the Worksheet_Change event. Assuming that the pivot table is on the same worksheet as the source data:

– Right-click the sheet tab.
– Select View Code from the popup menu.
– Enter or copy/paste the following code into the module:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A:B"), Target) Is Nothing Then
	Application.EnableEvents = False
	Me.PivotTables(1).RefreshTable
	Application.EnableEvents = True
  End If
End Sub

– Switch back to Excel.

Reply | Quote