Excel “Offset “ Function

[worswick25]

Hi,

I have a MS Excel spreadsheet to the bottom of which add a new price each working day

I wish to calculate the percentage change from the fund low (532.8 in this case)
and display the percentage change offset into column F

I have tried to do this one line below the last price line so that this does not interfere when I add a new line of price data

I have trouble using the Excel Offset function shown below

See lines 754 & 755 on the attached Excel Spreadsheet “Offset “

532.8 = Lowest price for the fund

Cell E574 = last price= 810.4

Percentage increase in cell F755

Formula in cell F755 =(OFFSET (E754,0,0,1,1)/532.8)-1

Could you please help me using the Excel Offset function?


I have only shown one fund price of clarity

Hoping you can oblige

Derek

[TMS]

I'm a little confused. 532.8 seems to be the starting price for the fund, not the lowest price. You have a formula =MIN(E:E) which shows 501.2

You could use:

=INDEX(E:E,COUNTA(E:E))/MIN(E:E)-1 ... if you want to compare to the minimum for the fund

Or:

=INDEX(E:E,COUNTA(E:E))/E$1-1 ... if you want the starting price, that is, in cell E1


I prefer INDEX to OFFSET as it is non volatile. Thes formulae do assume there are no gaps (blank cells) before the end of the data in column E.


Regards

[MyOnlineTrainingHub]

Hi Derek,

If I understand you correctly you need to locate the last value in column E and divide it by the Low of 532.8. To do this you need to start at cell E1 with your OFFSET and count how many rows until the last value in column E like this:

=(OFFSET(E1,COUNTA(E:E)-1,0,1,1)/532.8)-1

You could put this formula in any cell (not in column E), so you could move it to the top if you wanted to.

Kind regards,
Mynda

link removed by admin

[worswick25]

Hi,

I used the 532.8 figure as it was equal to the Date of a Minimum on On a US Index.

Sorry about the confusion.

Thank you for your help

Best regards,

Derek

[TMS]

If this has answered your question, please matk your thread as solved. See my signature for details, or the FAQ.

Regards, TMS

[worswick25]

Hi Mynda,

Thank you for your reply.

Yes I need to locate the last value "of the American Fund" in column E
and divide it by the Low of 532.8.

When I insert a new date line of prices..
I need to move the percentage change formula down
to calculate the new percentage change for the new price of the "American Fund"

Unfortunately I have other funds on the list which following on continuously from " the American Fund"
say named "Asia Fund"

Sorry it is difficult to explain

Best regards,

Derek