Forum Discussion

Melvyn1's avatar
Melvyn1
Copper Contributor
Feb 09, 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 09, 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.

MelvynM's avatar
MelvynM
Copper Contributor
Feb 11, 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 11, 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