topic Re: Calculate Average of Multiple Rows Where Some Are Populated With "n/a" in Alteryx Designer Desktop Discussions

Calculate Average of Multiple Rows Where Some Are Populated With "n/a"

Rob48 — Mon, 26 Feb 2024 14:06:37 GMT

Having a difficult time with this one. It's very easy in excel so I assume it's easy in Alteryx and I just don't know the trick. I'm trying to average out the values in combined columns, but some of the cells are populated with text if there is no value. in the example below I need to get an average for each category for column 1, column 2, columns 3&4 combined, and columns 5&6 combined.

My data looks like this:

Category	Number1	Number2	Number3	Number4	Number5	Number6
Cat1	3	3	3	3	3	3
Cat2	3	4	4	3	1	n/a
Cat3	3	3	3	3	3	n/a
Cat4	3	3	3	n/a	3	3
Cat5	2	2	3	2	2	2
Cat4	3	3	3	3	2	n/a
Cat4	2	2	4	3	n/a	3
Cat3	3	3	3	3	3	n/a
Cat4	3	3	3	n/a	2	2
Cat5	2	2	2	2	2	n/a
Cat2	4	4	4	4	n/a	n/a
Cat3	1	1	1	3	1	1
Cat4	3	3	3	3	2	n/a
Cat5	3	3	4	n/a	n/a	3
Cat1	3	3	3	3	3	3

I need it to look like this:

Category	Avg of Number1	Avg of Number2	Avg of Number3 & 4	Avg of Number5 & 6
Cat1	3.1	3.2	3.0	2.9
Cat2	3.1	3.2	3.0	2.9
Cat3	3.1	3.2	3.0	2.9
Cat4	3.1	3.2	3.0	2.9
Cat5	3.1	3.2	3.0	2.9

Excel will just ignore the "n/a" values when calculating to the new columns for 3&4 and 5&6. Alteryx won't calculate non-numbers (as far as I can tell) and if I convert those n/a text cells to zero I get another set of problems. is there a simple trick I don't know about?

Re: Calculate Average of Multiple Rows Where Some Are Populated With "n/a"

Raj — Mon, 26 Feb 2024 14:14:07 GMT

Convert your N/A to 0 using multi- Field Formula

then you have a option to calculate average considering that cell or average ignoring 0's as well.

according to me this is easiest and effective way to achieve the solution

Re: Calculate Average of Multiple Rows Where Some Are Populated With "n/a"

Rob48 — Mon, 26 Feb 2024 16:54:01 GMT

I tried that and that's where I got stuck. How do i combine the values in columns 3&4 to average them?

Re: Calculate Average of Multiple Rows Where Some Are Populated With "n/a"

cjaneczko — Mon, 26 Feb 2024 17:16:06 GMT

You can add a formula tool that averages them ([Number 3] + [Number 4])/2 and ([Number 5] + [Number 6])/2.

or Average([Number 3],[Number 4]) and Average([Number 5],[Number 6])

Re: Calculate Average of Multiple Rows Where Some Are Populated With "n/a"

Rob48 — Mon, 26 Feb 2024 17:18:20 GMT

that won't eliminate the zeros. (4+0) / 2 = 2.

Re: Calculate Average of Multiple Rows Where Some Are Populated With "n/a"

cjaneczko — Thu, 29 Feb 2024 17:07:09 GMT

Replace the N/A's with 0's or blanks with a formula tool.

Re: Calculate Average of Multiple Rows Where Some Are Populated With "n/a"

kamal03 — Thu, 29 Feb 2024 17:54:37 GMT

Hi @Rob48 ,

You can try the below workflow to get the desired output.

Let me know if it works for you or not.

Thanks