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