SOLVED

Excel Function

Copper Contributor

Here's an easy one for you experts

 

I want to find the best Excel function to accomplish the following:

The baseline number in my calculation is: 9

I want to increase 9 by .5 each month for 12 months (I.e. (9+.5)+(9+.5+.5)+(9+.5.+.5+.5), etc.

 

I'm a Windows 10 user and have the latest version of Excel.

Thank you!

George

 

 

 

5 Replies

@gs2019 

That's a mathematics problem. You want to calculate the sum of 12 times 9 and (1+2+...+12) times 0.5

In general, the sum 1+2+...+n is COMBIN(n+1,2) = (n+1)*n/2, so 1+2+...+12 = 13*12/2.

The result you want is

=12*9+13*12/2*0.5

It can also be written as

=12*9+COMBIN(13,2)*0.5

best response confirmed by gs2019 (Copper Contributor)
Solution

@gs2019 

 

=SUMPRODUCT(ROW(1:12)*0.5+9)

 

@Juliano-Petrukio 

@Hans Vogelaar 

 

Thank you both for providing alternative approaches to my calculation!  I appreciate your responses.  Additionally, I learned some new Excel strategies.

 

Have a good day...

 

George Stoffel

Anytime my friend. If possible, hit the like button and/or flag the post as answered.

@gs2019 

As variant, if you are on 365

=SUM(SEQUENCE(12,,9.5,0.5))
1 best response

Accepted Solutions
best response confirmed by gs2019 (Copper Contributor)
Solution

@gs2019 

 

=SUMPRODUCT(ROW(1:12)*0.5+9)

 

View solution in original post