Forum Discussion
Middle of the sum of a list of numbers
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.
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.
- 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.- 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
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
