Rory has attended:
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!
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
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
