Excel: Invert Table Order for WIG Rates of Return, Create Consecutive Date Column, Import CSV

Hello, I have an Excel table with numbers, I would like to reverse it, but not sort ascending or descending, but just invert it (these are rates of return from WIG, so I care about the order - the numbers match the dates.)
For example, we have the following:
1
3
4
2
I dream of turning it into a figure
2
4
3
1
My next question is the date. I wanted to enter "something" in cell 1 so that subsequent cells in the column would be consecutive dates. For example:
01.2010
02.2010
03.2010
[...]
12.2010
01.2011
It can also take the form of:
2010.01 (here I drag it to 2010.12 and enter 2011.01 manually and drag it again)
The problem for me is to jump from 2010 to 2011 if it would be possible to do it automatically, so that, as a result of the formula, the program itself enters me with subsequent dates differing by a month (monthly rates of return ... :)

For today, the last question is to download a table (or a csv file) from a notebook to Excel - how to do it to transfer each separate value to the next cells (so that it looks the same as in the notebook, but simply in a spreadsheet).

Greetings Marcin

1
If you want to change the order, add a column with an ordinal number (No.) next to it.
[enter 1 in the first cell, in the second 2 select both and drag it down - the next numbers will be created]
Then select this column of Lp with your data and sort by No. descending.

2
Use the EMONTH function (base year, number of months) and format the cells for a non-standard date, e.g. yyyy-mm, while the base year is taken from the previously calculated EMONTH () cell.
Of course, the first date is typed by hand and is in the Excel date format. [example below].

I don't know if the EMONTH () function is included in Excel functions as standard. If not, select Tools - Add-ons from the menu and select Analisys ToolPak and give ok .

3
I can't see the data, but maybe it is enough to select the data and "text as columns ..." in the menu. Set the division accordingly
and that's it.

Hello,
As for problem # 1, maybe someone else will help, because I only think of VBA unless you acknowledge having macros on this sheet.

Problem 2 is fairly simple to solve ...
If you want exactly in the format e.g. 01.2010, you right click on column A, select format cells, Custom and in Type you enter: mm.yyyy

You enter the first date in the cell (in 2010-01 format), right-click the black square in the lower-right corner of the cell, and drag it down. When you get where you want, you let go and choose "fill with months" from the menu.

CSV in Excel ... hmmm the easiest way is to right-click on the CSV file and Open with Excel

Thanks a lot for your help
The idea of creating an ordinal number next to the table is great Everything works as it should.
As for EMONTH, also a nice idea and I have already applied it.

Regarding the formatting of cell values, entering mm.yyyy or yyyy.mm Excel changes only the year for 1 time, and for the second time the number of months in the year tends to infinity :D

CSV file and Excel - opening it in Excel is successful, but the program sees everything in one cell / row, i.e. the data is ok as regards vertical distribution - each for a different period is a row below, but those from one period are in one cell.
Regards


PS For me, questions are at best from Risk Management or Financial Matma
PS 2 I didn't notice this data as columns - it works too no putty, and Excel is so simple ...

as for this csv file, when importing from the data file, in the "delimiter" options, you select the appropriate box (it depends what you have between the data in the source file, e.g. in the file you have:
"jan, blacksmith, 12 years old, born 98"
then in this case our delimiter is "," and everything that is separated by a comma will be in a separate column)

ps you open it by turning on excel -> data -> from a file -> you indicate the location (this is the case in office 2007) and in the older one I do not remember how: p

Martins10, as for dates, you apparently enter the date in the cell itself in the format 12.2010 or 2010.12 which Excel treats as a number and not a date. If you enter 2010-12, it will be considered the date. If you change the formatting of cells to mm.yyyy and enter 2010-12 in the cell itself, then Excel will "figure out" what you mean and the Zam will change 2010-12 to 12.2010, which will still be considered a date.

Filling for months should work at this point.

One more trick, not only for dates ...
try to enter 2010-11 in one cell, in a cell below 2010-12.
Select both cells and drag the black square in the lower right corner down ...

PS: The fact that Excel is easy is a bold observation

The stall method also works flawlessly
In case of further questions, I will write.
As for the construction of the macro, I do not need it yet, it did not take long to open this file in Excel.

Hello, it's me again :)

I would like EXCEL to return to me in the column "Number of rates of return corresponding to a given class range" the number of rates of return that fall within the given range from the column with rates of return which is furthest to the right.

I was thinking about a different table layout, in which the spreadsheet would generate 0; 1 answers (does not fit; fits) and then sums it up. And since it would be in the zero-one mode, it would be easy to read how many returns there are in a given range. But somehow nothing specific can come to my mind yet.

I was also thinking about the function if, but I would not be able to write a function that would "jump", everything should be as it should be, because there would probably have to be some conditional sum ...

I think that someone knows how to help me, I am asking for it :)

Regards,
Marcin

For the future:
If you have a different topic (another problem), create a new topic. It will be easier for others to possibly use this knowledge later if they have a similar problem.


As for the question:
There are different methods of getting the results you want to achieve.
It can be by if, by search, index, and many more.
Because I have Excel 2003 and you can't nest more than 7 if (and you would need a minimum of 9) and I didn't want to use the auxiliary column to break down the "if" function I used VLOOKUP with 1 (1) at the end and functions select a for counting intervals the count if function. But there are many ways to work it out.

A few comments on your data (ranges)
You manually enter a jump of 0.01%, while the differences between the beginning and the end are accurate to three decimal places. Excel only shows you rounding to two places, but it remembers (stores) all the places (in your case three) so there may be some distortion.
Therefore, there are "empty spaces that are not" covered "by any range. In my case, it will be classified to the lowest and not exceeded (I check the ranges highlighted in the box).

If there is to be two decimal places in percent, you have to close all functions in the formula round (number; 4)

Another thing about my way:
if the value is less than the minimum, the function returns an error, and when it is greater than max, it will always indicate the "J" (last) interval

You have a bad hand calculated sum of the interval "D".

Once again, I recommend that you align the ranges carefully. If you have any questions, please write.

Thank you for the answer and the ready solution.

With the COUNTIF function, I even had good thoughts towards the conditional sum :D

As for CHOOSE (GIVE ITEM), I still have to think about it for a while to be able to do it myself for the next time.

If there are further doubts, and they probably will, because I will have to build histograms, risk maps and a few other things based on the above-mentioned data (of course I have more of them than just in this last uploaded file), I will probably report it. Because these will be my first times using Excel in this way :)

Thanks again
greetings.

As for the use of VLOOKUP () in this case, it's a "variation". As a rule, it is used for other but similar purposes.
And another function is quite a simple select () function.