Averaging and using array formulas in dynamic tables

Resolved · Medium Priority · Version 2013

Wed 25 Jul 2018, 15:40 Delegate Rory said...

Rory has attended:
Excel Intermediate course
Excel VBA Intermediate course
Word Advanced course

Averaging and using array formulas in dynamic tables

Issue: Attempted to make a spreadsheet dynamic, however formulas do not update when new row added.

I have 3 tables

Table 1:
Contains a list of sample types (e.g. AISS, AIST, AISU …)

Table 2:
Contains test results for a given specimen within the sample type (e.g. AISS001, Vf = 60%; AISS002, Vf = 60.1%; AIST, Vf = 58% …)

Table 3:
Establishes an individual sample type from Table 1 using a Data Validation tool.
Uses an AVERAGEIF( function to determine the average of results from Table 2 for a given sample type, i.e. the average of all the AISS specimens.

=IFERROR(AVERAGEIF(Analysis!$C$5:$C$19,B6,Vf),"")

Uses an array formula containing a STDEV(IF( to determine the standard deviation of results from Table 2 for a given sample type, i.e. the standard deviation of all the AISS specimens.

{=IFERROR(STDEV(IF(Analysis!$C$5:$C$19=Outputs!B6,Vf)),"")}

I intended the spreadsheet to be dynamic so that, when a new sample type is added to Table 1 (e.g. AISV), and the results for the individual specimens within this sample (e.g. AISV001, AISV002, AISV003) added to Table 2, a new row could be added into Table 3 and the new sample code chosen (dynamically updated using a drop down from the Data Validation tool) and the Average and Standard Deviation calculated by the functions shown above automatically/dynamically calculated.

However, the functions seem to stop working when I do this, as the function doesn’t update the new range/array for the added rows in my Table (i.e. the "Analysis!$C$5:$C$19" line in the above equations does not dynamically update to Analysis!$C$5:$C$20 for adding another row in my Table 2).

Thanks!

Wed 25 Jul 2018, 20:41 Trainer Jens said...

RE: Averaging and using array formulas in dynamic tables

Hi Rory,

Thank you for the forum question.

Please send me and example of your three tables, so I can see what you have done thanks.

Please forward the file to info@microsofttraining.net

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Sun 29 Jul 2018, 21:12 Trainer Jens said...

RE: Averaging and using array formulas in dynamic tables

Hi Rory,

The code below takes all MIR_Codes from the input sheet and add them to the Output sheet and then do the calculations.

if you paste the code in the example workbook and delete the records in the Output table, you can run the code and see how the code populate the calculations.

I hope, that this is what you want.

Sub SpecimenResult()
Dim VarArray As Variant
Dim lRow As Long
Dim lCount As Long

Sheets("Inputs").Select

VarArray = Range(Range("b5"), Range("b5").End(xlDown))

Sheets("Outputs").Select

Range("b6").Resize(UBound(VarArray, 1), UBound(VarArray, 2)) = VarArray

lRow = Range(Range("b6"), Range("b6").End(xlDown)).Rows.Count

For lCount = 1 To lRow

Cells(lCount + 5, 3).FormulaR1C1 = _
"=IFERROR(AVERAGEIF(Table3[MIR_Code],[@MIR],Table3[Vf]),"""")"
Cells(lCount + 5, 4).FormulaArray = _
"=IFERROR(STDEV(IF(Table3[MIR_Code]=[@MIR],Table3[Vf])),"""")"
Cells(lCount + 5, 5).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],"""")"

Next lCount

End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector