Accumulated Result in a Formula

[austinandreikurt]

Is it possible to change a multiplier given the range within a formula? Say I have from Cell A1 as score of 50. Then below is my table:

Range	Multiplier
0-10	2​
11-20	3​
21-30	4​
31-40	5​
41-50	6​

Then the answer will be 20 in Cell B1. It is because that on the first 10 it will get 2, for the second 10's which is 11-20, it will get 3 and so on until the score of 41-50 which is 6. So its like 2+3+4+5+6. This is just a simple way to explain my problem. It will be applied using DatedIf formula like getting the accumulated bonus leave based on the start date from today. Your assistance will be very much appreciated. Thankyou.

 

[lrobbo314]

Book1 (version 1) (version 1).xlsb
	A	B	C	D	E
1	Range	Value		Lookup	Result
2	0	2		35	5
3	11	3
4	21	4
5	31	5
6	41	6
Sheet9

Cell Formulas
Range	Formula
E2	E2	=LOOKUP(D2,$A$2:$A$6,$B$2:$B$6)

 

[Dave Patton]

try either of

T202104a.xlsm
	A	B	C
1	50	20	20
2
3	Range	Multiplier
4
5	0	2	2
6	11	3	5
7	21	4	9
8	31	5	14
9	41	6	20
3c

Cell Formulas
Range	Formula
B1	B1	=LOOKUP(A1,A5:A9,C5:C9)
C1	C1	=LOOKUP(A1,{0;11;21;31;41},{2;5;9;14;20})
C5:C9	C5	=B5+C4

 

[austinandreikurt]

Thanks for the reply @lrobbo314 but lookup is not the formula for this. What I mean from the problem is that the "total bonus" so when he gets 50, instead of 6, it will be 20. Because he earned 2 on the first 10, 3 on the 2nd 10, 4 on the 3rd 10, 5 on the 4th 10 and 6 on the 5th 10 which is 41-50. And the table is not limited to 41-50 as the max score but it can go on as 991-1000. But the pattern is just the same, for each 10, you earn another 1+ the previous bonus.

 

[austinandreikurt]

@Dave Patton is it possible not to use a lookup because I do not really wish to have a table but just within the formula? The actual problem is getting the number of years to determine the accumulated leaves. So for the first year, he earns 5 for the whole year, and for every year, that leave will add up to 1. So his second year, he will earn 6 leaves so that makes it 11 total accumulated leaves for his 2years from date of hire. So if we will see his 3rd year which will earn him 7leaves, his total accumulated leaves will be 18 at the end of his 3rd year. That is why I am trying to use the DATEDIF with "y" as the unit.

 

[kweaver]

What if the value is 22? Is the value you want 9 (2+3+4)?

Book7
	A	B
1	23	9
2	0	2
3	11	3
4	21	4
5	31	5
6	41	6
Sheet8

Cell Formulas
Range	Formula
B1	B1	=SUM(B2:INDEX(B2:B6,MATCH(A1,A2:A6,1),0))

 

[austinandreikurt]

@kweaver yes, exactly, it will be 9. But is it possible not to reference a formula in a table? The pattern will just be the same, for 51-60 it will add another 7, 61-70 will add another 8 and so on. SO I was thinking of n=n+(n+1) but can't get ahold of the right formula.

 

[Marcelo Branco]

@kweaver yes, exactly, it will be 9. But is it possible not to reference a formula in a table? The pattern will just be the same, for 51-60 it will add another 7, 61-70 will add another 8 and so on. SO I was thinking of n=n+(n+1) but can't get ahold of the right formula.

Not sure i understand what you're looking for. See if this does what you need

Pasta1
	A	B
1	Value	Score
2	23	9
3	50	20
4	51	27
5	40	14
6	100	65
7	105	77
Planilha3

Cell Formulas
Range	Formula
B2:B7	B2	=SUM(ROW(A$1:INDEX($A:$A,MATCH(A2,10*(ROW(A$1:A$1000)-ROW(A$1)+1)-9)))+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

M.

 

[Marcelo Branco]

Simpler formula...

Pasta1
	A	B
1	Value	Score
2	23	9
3	50	20
4	51	27
5	40	14
6	100	65
7	105	77
Planilha3

Cell Formulas
Range	Formula
B2:B7	B2	=SUMPRODUCT(ROW(A$1:INDEX($A:$A,ROUNDUP(A2/10,0)))+1)

M.

 

[Dave Patton]

Did you try post #3, C1?