Forum Discussion

Melvyn1's avatar
Melvyn1
Copper Contributor
Feb 10, 2024
Solved

Middle of the sum of a list of numbers

Hello,    I have lists of numbers organized this way : 7 14 19 35 41 10 20 16 10 13 7 5 1 2 I need to find at which cell the sum of relative frequencies pass 50%. In this case it wou...
excel
Formulas and Functions
office 365
  • m_tarler's avatar
    m_tarler
    Feb 10, 2024

    Melvyn1 Here is one option.  it doesn't use lambda but does use some newer functions that require excel 2019 I think.  But it could also be modified to use some older context if needed:

     

    =LET(in,A1:A14,XLOOKUP(SUM(in)/2,MMULT(--(SEQUENCE(ROWS(in))>=SEQUENCE(,ROWS(in))),in),in,,1))

     

     if you don't have a newer version of excel you can use something like this:

    =INDEX(A1:A14,MATCH(SUM(A1:A14)/2-0.1,MMULT(--(ROW(A1:A14)>=TRANSPOSE(ROW(A1:A14))),A1:A14),1)+1)

    you might need to use ctrl,shift, enter when you enter the formula.  it does assume you are using whole number and therefore the -0.1 is enough to make sure it is never exactly = to 1/2 but you could use a sufficiently small offset if needed.

m_tarler's avatar
m_tarler
Bronze Contributor
Feb 11, 2024
so glad it is working and even more glad you are taking the time to understand how it is working. It isn't just the teach them to fish mantra (although that is important) it is that I am a strong believer that you MUST understand the tools you are using. If not you don't understand the limits under which they will work.
That said I thought I would mention some tips on the formula:
=LET(X;F11:F24;Y;G11:G24;PM;PRODUITMAT(--(SEQUENCE(LIGNES(Y))>=SEQUENCE(;LIGNES(Y)));Y);RECHERCHEX(SOMME(Y/2);PM;X;;1))
so you do NOT need a new LET for each variable you create. just list them all in the format (variable1; value1; variable2; value2; .... ; output) and note that any 'value' can be a formula
you should also be able to use that XLOOKUP to directly get to your answer. basically lookup the sum/2 in the PM variable you created and return the corresponding value from X.
I show what I think is the improved formula above but my french is there so I hope I did it justice.
MelvynM's avatar
MelvynM
Copper Contributor
Feb 12, 2024
Thank you again for your feedback! The advices have been really usefull, and even in French it was perfect! Quick question, do you know any formula that is able to remove many symbols at once, or only keep numbers? In this formula, for example, to remove the "[" & "]" I need to do substitue two time, and sometimes I have way more characters: =LET(X;F16:F23;Y;G16:G23;PM;PRODUITMAT(--(SEQUENCE(LIGNES(Y))>=SEQUENCE(;LIGNES(Y)));Y);xx;RECHERCHEX(SOMME(Y/2);PM;X;;1);GAUCHE;SUPPRESPACE(SUBSTITUE(SUBSTITUE(GAUCHE(xx;TROUVE(";";xx)-1);"[";"");"]";""));DROITE;SUPPRESPACE(SUBSTITUE(SUBSTITUE(DROITE(xx;NBCAR(xx)-TROUVE(";";xx));"]";"");"[";""));(1-1/(RECHERCHEX(SOMME(Y)/2;PM;Y;;1))*((RECHERCHEX(SOMME(Y/2);PM;PM;;1))-SOMME(Y)/2))*(DROITE-GAUCHE)+GAUCHE)


  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Feb 12, 2024

    MelvynM  I wrote the following LAMBDA function for someone to extract numbers:

     

    ExtractNumbers = LAMBDA(in,
    IF(ISBLANK(in),
        "",
        LET(_s, REDUCE(";",
                MID(in, SEQUENCE(LEN(in)), 1),
                LAMBDA(prior, this, prior &
                        IFS(ISNUMBER(SEARCH(this, "0123456789,.")), this,
                            RIGHT(prior, 1) = ";","",
                            TRUE, ";")
                )
            ),
            _m, MID(_s, 2, LEN(_s)),
            IF(RIGHT(_m, 1) = ";", LEFT(_m, LEN(_m) - 1), _m)
        )
    )
);

     

    it will go letter by letter and only return numbers and comma and periods and return them in a list fashion.  e.g. 123and456then89 would return 3 groups of numbers: 123;456;89

    if you know the only non-number characters will be [] and you just want those characters removed from the string then try this:

     

    =CONCAT(TEXTSPLIT(A1,{"[","]"}))

     

    basically textsplit can be used to split on any of an array of characters and then CONCAT used to rejoin all the parts.

    and here is a really clever version that uses a double negative technique to keep only the numeric digits (the sequence of 10 CHAR from "0" to "9"):

     

    =CONCAT(TEXTSPLIT(A1,TEXTSPLIT(A1,CHAR(SEQUENCE(10,,CODE("0"))),,1)))

     

     so the inner TEXTSPLIT will split on any digit resulting in the non-digits so the outer TEXTSPLIT will split on those non-digit characters and then concat back together.

Resources