Forum Discussion
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 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
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.
7 Replies
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
