Sorting 7 Average

Cobbles_Classics · February 15, 2025, 4:21pm

Hello all.
This is a 2-part question.

I am trying to auto arrange figures so they appear in the correct column.
eg: if “Product A, B or C” is entered in the cells “A3:A14” and the value is added to the cell range “B3:Bt14” then the value is auto placed beneath the corresponding column heading in “D3:D14” “E3:E14” or “F3:F14”.

The next part, is I would like to calculate the average, price of PRODUCT A, B or C, i the relevant cells “D2, E2. F2”.

Average calculations are relatively simple, BUT I need to only calculate the average based on an actual figure auto placed in the column (no 0.00 values) rather than just a distorted sequence average on the whole range in a column.

I’ve tried adapting a SUMUP calculation, used in previous sheets, but I’m simply guessing, until I find the correct formula.
Any help appreciated.

Thank you

Steph1 · February 15, 2025, 5:42pm

I attach a solution. For the first part of your question I used an IF formula, comparing the content of the first column with the content of the first row. Note the presence of the $ sign in the formula, which doesn’t change the value when you copy the formula to another cell. So I just had to create the formula in the top left corner (cell D3) and copy it.
For the average, I used SUM of the column / COUNT of the column. COUNT returns the number of numeric values in the defined range, therefore you obtain the average. Note that you should embed the formula in an IF function, to decide what to do when COUNT is 0, or else you will have a DIV/0 error.
LIBREOFFICEEXAMPLE.ods (18.8 KB)

Cobbles_Classics · February 15, 2025, 6:38pm

That solved it ! Thank you Steph!

shantanuo · February 16, 2025, 11:28am

You can get the compact results like this if you have the pythonista extension installed.

PRODUCT A	PRODUCT B	PRODUCT C
2.5	7.75	9.25
1	5	6
2	7	9
3	8	10
4	11	12

# create a dataframe
df = lp("A1:B13", headers=True)

# Compute the mean price per product and transpose it
my_series = df.groupby("Item")["Price"].mean().to_frame().T

# Create row numbers to align products correctly
df["Row"] = df.groupby("Item").cumcount()

# Pivot to get products as columns
my_pivot = df.set_index(["Row", "Item"])["Price"].unstack()

# Concatenate both DataFrames
result = pd.concat([my_series, my_pivot], ignore_index=True)

The extension can be installed from:

LeroyG · February 16, 2025, 4:09pm

You don’t need to repeat the values in D3:F14 to get the averages.
Just use =AVERAGEIF($A3:$A14;D$1;$B$3:$B$14) in D3, and fill until F3.
SUMIF_AVERAGEIF_LeroyG.ods (14.8 KB)