SOLVED

Excel Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2745566%22%20slang%3D%22en-US%22%3EExcel%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2745566%22%20slang%3D%22en-US%22%3E%3CP%3EHere's%20an%20easy%20one%20for%20you%20experts%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20find%20the%20best%20Excel%20function%20to%20accomplish%20the%20following%3A%3C%2FP%3E%3CP%3EThe%20baseline%20number%20in%20my%20calculation%20is%3A%209%3C%2FP%3E%3CP%3EI%20want%20to%20increase%209%20by%20.5%20each%20month%20for%2012%20months%20(I.e.%20(9%2B.5)%2B(9%2B.5%2B.5)%2B(9%2B.5.%2B.5%2B.5)%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20a%20Windows%2010%20user%20and%20have%20the%20latest%20version%20of%20Excel.%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EGeorge%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2745566%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2745598%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2745598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154295%22%20target%3D%22_blank%22%3E%40gs2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20a%20mathematics%20problem.%20You%20want%20to%20calculate%20the%20sum%20of%2012%20times%209%20and%20(1%2B2%2B...%2B12)%20times%200.5%3C%2FP%3E%0A%3CP%3EIn%20general%2C%20the%20sum%201%2B2%2B...%2Bn%20is%20COMBIN(n%2B1%2C2)%20%3D%20(n%2B1)*n%2F2%2C%20so%201%2B2%2B...%2B12%20%3D%2013*12%2F2.%3C%2FP%3E%0A%3CP%3EThe%20result%20you%20want%20is%3C%2FP%3E%0A%3CP%3E%3D12*9%2B13*12%2F2*0.5%3C%2FP%3E%0A%3CP%3EIt%20can%20also%20be%20written%20as%3C%2FP%3E%0A%3CP%3E%3D12*9%2BCOMBIN(13%2C2)*0.5%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2745645%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2745645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154295%22%20target%3D%22_blank%22%3E%40gs2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT(ROW(1%3A12)*0.5%2B9)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2746105%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2746105%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20both%20for%20providing%20alternative%20approaches%20to%20my%20calculation!%26nbsp%3B%20I%20appreciate%20your%20responses.%26nbsp%3B%20Additionally%2C%20I%20learned%20some%20new%20Excel%20strategies.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20good%20day...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGeorge%20Stoffel%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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))