Use cell content as values for a weighted.average formula in google sheets-CodePudding

I want to use the content of a cell as values of weighted.average formula in google sheet.

Example:

Cell A1 manually input values: 10,2,50,3,60,1 That means 10x2 50x3 60x1

Cell F1 put the formula: =weighted.average( [contents from A1] ) The result must be 38.33

I don't know how to concatenate this.

I've tried to use =weighted.average(text(A1)), =weighted.average(cell("contents",A1))

No results

CodePudding user response：

try:

=LAMBDA(ax,AVERAGE.WEIGHTED(QUERY(ax,"SELECT * SKIPPING 2"),QUERY({2^99;ax},"SELECT * SKIPPING 2 OFFSET 1")))(TRANSPOSE(SPLIT(A1,",")))

CodePudding user response：

Another approach, based on adding a second additional delimiter every 2nd delimiter in the input and then splitting in two stages with a flatten in-between:

=arrayformula(lambda(splitrng,average.weighted(index(splitrng,,1),index(splitrng,,2)))(split(flatten(split(regexreplace(A1,"(\,.*?){2}","$0|"),"|")),",")))