Complex STANDARDIZE function related to SD with arguments

[qwertz87]

Hey there!

I have a little problem. I'm trying to calculate to a z-score where the AVERAGE and SD are linked to certain arguments. I want to figure out the z-score for instance for Name "A" in Session "X". I could calculate the average for A during session X but I'm struggeling to do the same for the standard deviation and put it into a STANDARDIZE formula. Help would be highly appreciated. Maybe a Macro could help.

I've attached the file...

[AliGW]

The sample workbook is of next to no use. Please provide one with at least 10-15 rows of sample data with expected outcomes (not failing formulae) manually entered. Explain the logic behind the expected results in WORDS.

[qwertz87]

Hi!

The file has 49 rows of data though...

I expect a z-score in Column J based on the formula Standardize. Outcome values are usually between -3 and 3.

[AliGW]

You have not done what I asked.

This is still completely devoid of the manually calculated results I asked for and there is no explanation of the logic you are using. You need to explain what you want the formula to do. Try again, please.

Excel 2016 (Windows) 32 bit

	J
1	Z-SCORE IND V1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Sheet: Workload Data

[MrShorty]

Here's how I think I would do it -- recognizing that there will be multiple possible approaches:

1) Use a pivot table to compute average and standard deviation for each name/session combination. Can I assume you are already familiar with Excel's pivot tables (https://www.excel-easy.com/data-****...ot-tables.html )
2) Use lookup functions [INDEX(pivot table,MATCH(...))] inside of the STANDARDIZE() function to get the average and standard deviation from the pivot table. INDEX() function: https://support.office.com/en-us/art...2-b56b061328bd MATCH() function: https://support.office.com/en-us/art...9-533f4a37673a

One caution -- I know we are often enamored of complex array functions that do everything at once. In this case, I would advise against a single cell mega-formula for this, because each copy of the formula will need to compute the average and standard deviation which potentially leads to the spreadsheet repeatedly performing the exact same calculation. I see significant value in keeping the average and standard deviation calculation separate from the standardize calculation to avoid unnecessary computer effort (which potentially slows down the spreadsheet).

[qwertz87]

Ok got it. The problem is my solution to the formula is not suitable or results in an error.

I want the formula to take based on Name "A" and Session Type "Y" to take the value of cell D2. From D2 the value of the cell E2 should be substracted. E2 is represents the average values from Column D based on the Name (in this example A) and the session type (in this example Y). The new value (D2-E2) should afterwards be divided by the standard deviation (Column F2 ; based on Name ("A") and the session type ("Y"). I am looking now for an equilavent to AVERAGEIFS for STDEV where I can add argument for STDEV to the calculation.

What I am basically looking for is how to add arguments to the formula STDEV in column F.

[Glenn Kennedy]

use this array formula:
=STDEV(IF([Session Type]=[@[Session Type]],IF([Name]=[@Name],[V1])))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You may need ; instead of ,