Videos
SOLVED: I ended up just reformatting my entire dataset with pivot tables and going from there!
I need to find the mean # of calls that are both INCOMING/OUTGOING calls on WEEKDAYS/WEEKENDS (see the codebook below) and I can't figure out how to fiddle w/ =AVERAGEIF formulas atm TTv
I've included a screenshot of my excel data, the codebook, and the questions.
(MeanOutWD stands for Mean Outcoming Calls on Weekdays and so forth)
My Excel Data the questions asked the codebookHi!
I am trying to calculate the mean of all values in column I (which contains data on household income). However only specific cells should be selected, namely if the value in column H is 0 (this is the control group). I tried AVERAGEIF, but it does not work somehow. I would appreciate your suggestions very much:)
Thanks!
If one has Office 365 with the dynamic array formula use:
=MEDIAN(INDEX(A2:A6,MATCH(SEQUENCE(SUM(B2:B6),,0),SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))
and
=MODE.SNGL(INDEX(A2:A6,MATCH(SEQUENCE(SUM(B2:B6),,0),SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))
If not then this array formula:
=MEDIAN(INDEX(A2:A6,MATCH(ROW($ZZ1:INDEX($ZZ:$ZZ,SUM(B2:B6)))-1,SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))
and
=MODE.SNGL(INDEX(A2:A6,MATCH(ROW($ZZ1:INDEX($ZZ:$ZZ,SUM(B2:B6)))-1,SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))
Being array formula, then need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
Assuming that each Quantity only appears once alongside its Frequency, then you could use an INDEX MATCH on the MAX Frequency to find the Mode:
=INDEX(A2:A6,Match(Max(B2:B6), B2:B6, 0))
This will, of course, only return the first number should there be multiple Quantities which share the same maximum frequency (such as if Quantity 12 also occurred with Frequency 10)
Further assuming that your Quantities are in Ascending Order, you can use Matrix Multiplication (MMULT) to calculate the running total for each row, and use AGGREGATE to get the smallest row where the running total greater-than-or-equal-to half the overall total. This is the Median:
=AGGREGATE(15, 6, A2:A6/(MMULT(--(TRANSPOSE(ROW(B2:B6))<=ROW(B2:B6)), B2:B6)>=0.5*SUM(B2:B6)), 1)
(For reference, MMULT(--(TRANSPOSE(ROW(B2:B6))<=ROW(B2:B6)), B2:B6) is the bit which works out the Running total)