Price Development

Try Cell F2 formula, Drag down and across

Formula:

Please Login or Register  to view this content.

365

Formula:

Please Login or Register  to view this content.

Hello wk9128, that was easier than I though! Typical, though . Thanks a lot. I added a step chart to the spreadsheet to visualize the data but realized I need another modification to get correct chart. The step chart is not a standard Excel chart so I had to modify data of the standard line chart. The step chart is supposed to show vertical line connecting 2 prices the day they changed. I manually created a table which is able to produce correct chart. This table need to show changed price at the closest future Date (one Date later) and one extra column with the same Date as the latest Date has to be added and probably also calculated different way.

I tried to create a correct chart using the orifinaly correct final table with adding an extra column to its beginning but I am not able to the line modify chart to show correct results.

Can you help with this too? Other are welcome to help as well!!! Thank you!


The updated spreadheet is here:

Thanks!Attachment 763195

POST#3 The attachment is invalid and cannot be opened

Please re-upload the attachment

Your post and scroll down with the mouse , When see Attachments\manage Attachments start from here to Upload

No file attached: please follow instruction in the yellow banner at top page.

In F2 then copied across

=IFERROR(INDEX(Price,AGGREGATE(14,6,ROW(Price)/((INDIRECT("T[PN]")=$E2)*(INDIRECT("T[Date]")<=F$1)),1)-ROW($A$1)),"")

I have uploaded new file succesfully following the instructions. Apologies for the previsou upload issues.

What is the basis to get values in your expected result.
Eg: For B in 20-4-22 the table value is 10, expected is 15.

15 is expected as this is B value of the previous date 01-02-22. The data in this table needs to be organized this way to get correct Step chart which is not a native Excel chart. Please see V3 file it will be clearer what I am looking for.

I also would like to design the formulas the everything is fully dynamic (see the list below). I elaborated your formula but it is not dynamic solution.

Issues to be Fixed / Desired Improvements:
1) Can the Charts Source Table be completely dynamic automatic updates if Source Table changes?
The column axis (yellow) is already dynamic while row table axis's rightmost orange cell and table core cells (matte yellow) are not.
2) Can the core cells formulas be spilled in the area framed by the dynamic Dates and PN axis?
3)I would also like if the formulas in the Chart Core Cells display the same result even if Source data table is not sorted (something like solution which wk9128 posted (2nd post in the thread)

Source data not sorted means to which column Pn or Date.

For any particular NP, say B the dates are in ascending order. No necessity of sorting required. All dates in the table need not be in sorted manner. Even NP's are not required to be continuous rows as in the table, if they are distributed formula works. The only condition required is each NP's dates should be in ascending order. If you feel your problem different from this Pl upload the file to show what type of data will be there in table. Sorting of table not required. Only formula is to be modified.