Excel Formula column - referencing a range of cells

Ask for Help
1

I was hoping someone could help me using a particular excel formula. Full disclosure - I have lots of problems trying to use excel formulas, so it might just be me, but ive read the documentation and i cant figure it out.

I want to use the CORREL function. Usually in sheets/excel you can select a range of values using the annotation =CORRELL(A:A,B:B) to get the correlation between the values in columns A and B. How do i do this in Glide? Ive tried using arrays, but It seems like you cant select an array row in the Excel function column. Not sure what the best way of doing this would be.

2

Try rollup column

3

Im not sure I understand. The correlation formula compares pairs of values, not the sum of the values, so I dont know how this would be applied to help me with this issue.

Essentially, I have a table that has rows in it belonging to various categories, and date stamped with the date they are added to the table. Each row has a price and a score value in it, and I want to determine the correlation between price and score, but only for the rows that have the same category and date. I want to do this for each set of rows in the table. Ive made a date/Category unique identifying column, i just need to be able to run the CORREL formula on the row ranges that have the same value in this column.

Essentially, this issue is one of data range. In Excel/Sheets i would be using a formula that references a data range, but this isnt possible (from what i can tell) with Glide. Is there any way around this?

4

Ive changed the title of the post to reflect what i think is the real issue.

5

I don’t know anything about Excel, and I’ve never used the Excel formula column in Glide, but from your description it sounds to me like you should be passing two joined lists of values, one from each column.

7

image
image1626×609 135 KB

Ive tried using an array (the excel formula doesnt appear to let you selec an array column) and a joined list (I cant get it to do anything besides be blank).

The CORREL function inherently needs to use a range of data to execute its calculation. So unless theres another way to do this that im not thinking of, is Glide just unable to perform any calculations that require ranges of cells, particularly ranges with a relationship to one another?

8

you can’t run Excel formulas on arrays, only on rows… you can transform your data to JSON and use JavaScript

9

Glide is most certainly more than capable of that. In this case, it’s just a matter of figuring out what the Excel function that you’re wanting to use expects (in a Glide context), and then preparing and passing the values.

I had a play with it earlier, but wasn’t able to figure it out. But as I mentioned, I’m not familiar with the Excel plugin. Someone who knows it better might have a clue. Maybe @Robert_Petitto

10

Im not really sure to be honest. In the spreadsheet, a range of cells is provided to the function, but also, the variables are associated to one another in order to calculate a correlation. So more than just two lists of values, the index of the value in one list is associated with the index of the value of the same index in the other.

Id use javascript but i have probably less luck trying to convert javascript into whatever form Glide wants to read it as.

As an aside, if someone could provide me with a bunch of traditional javascript and javascript converted to be able to be used in glide, and also a bunch of excel formulas as entered into excel/sheets, and the same but converted into the form and column format to recreate in Glide i could probably fine tune a GPT to be able to assist people in rewriting their javascript and excel functions to switch them over to Glide.

11

I don’t know if this works and I don’t know what exactly the results should look like, but try this in a javascript column. Pass your joined list colums into the p1 and p2 parameters.

function calculateCorrelation(data1, data2) {
    // Convert comma-delimited strings to arrays
    const array1 = data1.split(',').map(Number);
    const array2 = data2.split(',').map(Number);

    // Check if arrays have the same length
    if (array1.length !== array2.length) {
        return "Error: Input arrays must have the same length";
    }

    // Calculate mean of each array
    const mean1 = arrayMean(array1);
    const mean2 = arrayMean(array2);

    // Calculate numerator and denominator for correlation
    let numerator = 0;
    let denom1 = 0;
    let denom2 = 0;

    for (let i = 0; i < array1.length; i++) {
        const diff1 = array1[i] - mean1;
        const diff2 = array2[i] - mean2;

        numerator += diff1 * diff2;
        denom1 += diff1 ** 2;
        denom2 += diff2 ** 2;
    }

    // Calculate correlation coefficient
    const correlation = numerator / Math.sqrt(denom1 * denom2);

    return correlation;
}

function arrayMean(array) {
    return array.reduce((sum, value) => sum + value, 0) / array.length;
}

return calculateCorrelation(p1, p2);
1 Like