Middle of the sum of a list of numbers

[Melvyn1]

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 would be the cell containing 41 since it is the first cell (from the top) the total amount over it (including itself) is over 50%. The sum of ever cells here is 200, so it needs to be at least 100 (50%). At the cell #4, with the 35, the sum is :7+14+19+35= 75.  But when we take the cell #5, the sum passes over 100 (7+14+19+35+41= 116). The result would then be cell#5 (G15 for me). What formula can find this? I know I could do it by making formulas in many cells, but I need it to hold all in 1 formula.

Thank you!

Mel

[#2 · Accepted solution]

@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.

[#3]

Hello Tarler,

I do have the newest version and it works perfectly, thank you! Could the answer of the formula be the celll’s location? Like A3, so I can develop the formula with it. I’d rather not search the cells that have 43 in case many cells end up having the same number.

[#4]

in both cases the actual function is returning the cell reference so you can use that in another formula like CELL("ADDRESS", [reference] ) if you want. but you say "so I can develop the formula with it" and I hope you aren't planning to use INDIRECT(). You could also use ROW() or MATCH() or other options to give reference to its location instead. Maybe explain the next step and we could help with that too

[#5]

Hello,

Thank you very much for your help, it has been really precious to me. Stubborn as I am, I tried to understand it, instead of just copying, and has been able to get the part of the formula I needed (it is French): =LET(X;F11:F24;LET(Y;G11:G24;LET(PM;PRODUITMAT(--(SEQUENCE(LIGNES(Y))>=SEQUENCE(;LIGNES(Y)));Y);INDEX(X:Y;EQUIV((RECHERCHEX(SOMME(Y/2);PM;PM;;1));PM;0);1)))) 

Income ($)

amount of people

[ 0 ; 5 000]	7
[ 5 000 ; 10 000]	14
[ 10 000 ; 15 000]	7
[ 15 000 ; 20 000]	35
[ 20 000 ; 25 000]	41
[ 25 000 ; 30 000]	10
[ 30 000 ; 35 000]	20
[ 35 000 ; 40 000]	16
[ 40 000 ; 45 000]	10
[ 45 000 ; 50 000]	13
[ 50 000 ; 55 000]	7
[ 55 000 ; 60 000]	5
[ 60 000 ; 65 000]	2
[ 65 000 ; 70 000]	13

 X being the first column and Y the second. I needed to get the value of the cell to the left of the one with the value your formula found. Anyway, even though I don't get the matrice's thing yet, it does work, so thanks! 

I really apreciate the time you took to help me,

Have a nice day,

Mel

@m_tarler 

[#6]

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.

[#7]

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)

[#8]

@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.