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?
