UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Array Function Assist - If Anyone Has A Moment..., Any Version    
 
   
bebeck2017
post Feb 13 2020, 07:02 PM
Post#1



Posts: 31
Joined: 28-April 17



Attached File  ArrayPuzzle.zip ( 13.86K )Number of downloads: 1


Good Afternoon Everyone,

Sorry to bug you guys AGAIN. The attached file contains a data table named "TrainingData." I'm going out on a limb and describe this table as an array with Trainees in the left most column, with a date, and then training subsection fields A1-A5 & B1-B5. To the right of the TrainingData table is an aggregation area that averages each subsection by trainee. This works great and is dynamic enough for my uses. Underneath this is my Newb aggregation by training section for each Trainee. [NOTE: In this situation, Section A is comprised of sub-sections A1 thru A5 and Section B is comprised of sub-sections B1-B5] This only works statically due to simple formulas I used to calculate these numbers. What I am looking for is an array formula or function that reaches into my data table and computes section averages for each trainee dynamically. By dynamically I mean that I might add more iterations of the training on a different date, for example. In fact, rather than doing the work for me, perhaps someone could point me in the direction of an article or YouTube video that covers the basics of array functions sufficiently well that I can figure it out for myself...

Thanks for your time and "Happy Valentine's Day" in advance hat_tip.gif

--------------------
Bruce Beck
Digital Data Minion & "Wanna-Be" MS Access Developer
"bebeck2017"
Go to the top of the page
 
Vince
post Feb 14 2020, 05:15 AM
Post#2



Posts: 76
Joined: 18-August 16
From: Bristol, UK


Assuming you always have A1-A5 then you could use the following:
Sum all 'A' items for particular trainee:

SUMPRODUCT(TrainingData[[A1]:[A5]]*(TrainingData[Participant]=N17))
(where N17 contains "Trainee_1")

Count occurrences of Trainee:

COUNTIF(TrainingData[Participant],"=" & N17)
(this will need to be *5 for A1-A5)

Put together to find average:

=SUMPRODUCT(TrainingData[[A1]:[A5]]*(TrainingData[Participant]=N17))/(COUNTIF(TrainingData[Participant],"=" & N17)*5)

Put the above formula in your cell O17 and drag down. For your B average change the TrainingData[[A1]:[A5]] reference. Note that although SUMPRODUCT is an array formula there is no need to enter using ctrl/shft/ent.

If you are likely to change A1-A5 or B1-B5 I'm sure it can be done, but I'd need to have a bit more of a think about it!
Go to the top of the page
 
bebeck2017
post Feb 14 2020, 04:56 PM
Post#3



Posts: 31
Joined: 28-April 17



Hello & Thanks Vince notworthy.gif

Your suggestions makes sense to me. FWIW, I think I was making things more difficult than necessary (as usual).

Anyway, I did as you suggested, the results are in the attached file:

Attached File  ArrayPuzzle_VinceFixes.zip ( 14.54K )Number of downloads: 2


Fortunately, I do not anticipate the sub-section areas to change once the actual training evolution begins so fine tuning the formula for Sections that contain a different number of sub-sections won't be an issue.

Thanks again for helping me out Vince, I was WAAAAAAY to far down the Excel rabbit-hole to see your solution. Cheers & Have an AWESOME weekend!

--------------------
Bruce Beck
Digital Data Minion & "Wanna-Be" MS Access Developer
"bebeck2017"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th February 2020 - 02:28 PM