geometric progression on excel or vba

[sandradepar]

Hi,
Can someone help me?

I know how to do a geometric progression on excel in vertical and horizontal lines for large sample such as 500 or 1000.

I don't know how to do a geometric progression on excel in diagonal lines for large sample such as 500 or 1000

. (i know how do it by fulfilling each cell at time, by it'll take an eternity to fulfilling each cell, this is the hard way). Here is a photo of the hard way. In the example the first trm of geometric progression is 100 and is increased by 1,07 each time.

Can someone show us the easiest way how to do a geometric progression on excel in diagonal lines for large sample such as 500 or 1000 on excel or a code for vba ?

Many thanks in advance

 

[Eric W]

Welcome to the forum!

I can't imagine what you'll use this for, but try this. Open a new workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens.

Sub GeoProg()
Dim MyCell As String, MyStart As Double, MyMult As Double, i As Long

    MyCell = InputBox("Enter the cell address on the active sheet to start: ")
    MyStart = InputBox("Enter the starting value: ")
    MyMult = InputBox("Enter the factor: ")
    For i = 0 To Range(MyCell).Row - 1
        Range(MyCell).Offset(-i, i) = MyStart
        MyStart = MyStart * MyMult
    Next i
End Sub

Press Alt-Q to close the editor. In Excel, press Alt-F8 to open the macro selector. Select Geoprog, and click run. Follow the prompts.

Hope this helps!

 

[DanteAmor]

This could be an option, put the formula in cell B2 and copy down until you find the cell with the number, in my example up to cell B9, then copy to the right until the number appears in the first row.

varios 01jun2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1										183.8459
2									171.8186
3								160.5781
4							150.0730
5						140.2552
6					131.0796
7				122.5043
8			114.49
9		107
10	100
SERVICIO

Cell Formulas
Range	Formula
B1:J9	B1	=IF(A2="","",A2*1.07)