Feb 09 2024 07:48 PM
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
Feb 09 2024 08:21 PM - edited Feb 09 2024 08:43 PM
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.
Feb 09 2024 08:48 PM
Feb 09 2024 09:52 PM
Feb 11 2024 02:00 PM
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
Feb 11 2024 02:41 PM
Feb 11 2024 05:43 PM
Feb 11 2024 09:14 PM - edited Feb 12 2024 08:24 AM
@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.
Feb 09 2024 08:21 PM - edited Feb 09 2024 08:43 PM
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.