CODEWITHSUNDEEP
excel
Dev Oskii
Dev Oskii

Reputation: 929

How to correctly formulate a sum function with range in excel

I'd like to write this function in excel:

enter image description here

It's a function that calculated how much experience is needed for a particular level. level 2 is 83xp, level 99 is over 13,000,000 for example.

I have all the levels arranged in this way:

enter image description here

I just can't figure out the excel formula to put into cell 4B to calculate the function in the image. I'm thinking it should be something like

=floor(sum[1 to cellToLeft.value - 1](floor(x + 300*2^(x/7))/4)) but that's not quite right

Upvotes: 1

Views: 101

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

This will be an array formula in excel answer. Place the level you want (the value for L) in N4, or change the reference to suit your needs. Enter the following formula in the cell you want the answer to be in remembering to press CONTROL+SHIFT+ENTER when finishing the formula instead of just ENTER. You will know you have done it right when {} appear around your formula in Excel.

=FLOOR(SUM(FLOOR(ROW($A$1:INDEX(A:A,$N$4-1))+300*2^(ROW($A$1:INDEX(A:A,$N$4-1))/7),1)/4),1)

POC1 POC2

Level 1 will cause an error since L-1=0. Since I am assuming it takes 0 for level 1, I threw in a error trap. anytime the formula returns an error it give a value of 0 instead. With the following formula you can change the $N4 reference to where ever you have your level 1 value. remember to use C+S+E when you enter the formula and copy downward as far as your levels go.

=IFERROR(FLOOR(SUM(FLOOR(ROW($A$1:INDEX(A:A,$N4-1))+300*2^(ROW($A$1:INDEX(A:A,$N4-1))/7),1)/4),1),0)

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117064

This seems to work for me:

{=FLOOR(SUM(FLOOR(R4C[-1]:RC[-1]+300*2^(R4C[-1]:RC[-1]/7),1))/4,1)}

Make sure you enter as an array formula.

If you don't use R1C1 mode then you need something like this:

{=FLOOR(SUM(FLOOR(A$4:A12+300*2^(A$4:A12/7),1))/4,1)}

Upvotes: 2

Related Questions