I’m trying to create computations and charts on some data. In it, there are two columns, C with the Age, and D with purchase. My task was to create clusters of data for 21-30, 31-40, and 41-50 age groups, and compute medians and average. All values are numbers.
I managed to do that using array functions using nested ifs to check both conditions. For example:
{=MEDIAN(IF(C2:C3001>=21, IF(C2:C3001<=30, D2:D3001, "na"), "na"))}
Now, this worked fine for these two rules, but why isn’t something like this working:
{=MEDIAN(IF(AND(C2:C3001>=21, C2:C3001<=30), D2:D3001, "na"))}
? Or what would be the way to specify multiple conditions, eventually over multiple columns in a more natural fashion instead of nested ifs? I imagined something like this should have worked, but nope:
{=MEDIAN(IF(AND(C2:C3001>=21, E2:E3001="PRO_User"), D2:D3001, "na"))}
The second problem I faced in my clustering was graphs. I wanted to plot the sales for the three age groups. I ended up creating a helper column E with =ROUNDDOWN((C2-1)/10), then I used the “pivot table” functionality and use that column for clustering sums of sales. Is that the way to go?