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.

 

[austinandreikurt]

Hi @Marcelo Branco is it possible not to refer to a table? A formula that would look like a combination of formula and algebraic equation where the score will be divided by 10 then the result will be the multiplier. Like if 41/10 =4.1, then the formula will know that n+(n+1)+(n+2)+(n+3).

@Dave Patton yes, but it will still lookup in a table that was set up in an array. My scores are not limited until 50, thus I will have to input, 51,61,71,81 and so on for it to function. If there will be no other ways, then I will use that formula and just state from 0,11,21.... to 491. Thankyou

 

[Marcelo Branco]

Hi @Marcelo Branco is it possible not to refer to a table? A formula that would look like a combination of formula and algebraic equation where the score will be divided by 10 then the result will be the multiplier. Like if 41/10 =4.1, then the formula will know that n+(n+1)+(n+2)+(n+3).

The formula i suggested in post #9 does not refer to any table or range. It uses the ROW function to generate an array {2;3;4...}

M.

 

[Dave Patton]

2 solutions that agree with Marcelo Branco's suggestion.

T202104a.xlsm
	A	B	C	D
1	Value	Score Marcelo Branco	Score Dave Patton	Score Dave Patton
2	23	9	9	9
3	50	20	20	20
4	51	27	27	27
5	40	14	14	14
6	100	65	65	65
7	105	77	77	77
8
3c

Cell Formulas
Range	Formula
B2:B7	B2	=SUMPRODUCT(ROW(A$1:INDEX($A:$A,ROUNDUP(A2/10,0)))+1)
C2:C7	C2	=(ROUNDUP(A2/10,0)+1) * (ROUNDUP(A2/10,0)+ 2) / 2-1
D2:D7	D2	=LOOKUP(A2,{0;11;21;31;41;51;61;71;81;91;101},{2;5;9;14;20;27;35;44;54;65;77})

 

[Marcelo Branco]

Dave
Your solution used in column C seems very good to me.
I had thought of something similar - sum of the terms of an arithmetic progression:
a1(first term) = 2;
an (last term) = ROUNDUP (A2/10; 0)+1;
n (number of terms) =ROUNDUP(A2/10; 0)
S = (a1+an)*n/2

M.

 

[Dave Patton]

N.B. Mr. austinandreikurt did not confirm that suggestions solve his problem.

For future reference
- Ceiling is an alternative
- With Excel 365, you could use Let.
Without Excel 365, you could download and install a UDF named V.
- E2 The Lookup array is named aL; a table is not used.

T202104a.xlsm
	A				E		G
2	23				9		9
3c

Cell Formulas
Range	Formula
E2	E2	=LOOKUP(A2,aL)
G2	G2	=LET(V,CEILING(A2/10,1),(2+V+1)*V/2)