![]() ![]() So, if you’d like to FLOOR all values (away from zero) and you’ve got positive and negative numbers you could write =FLOOR(yourCell,IF(yourCell>=0, 1,-1)). Caveat: FLOOR and CEILING in excel do round AWAY from zero and need a significance related to the actual value. To have even values as categories, it is proven useful to nest those into FLOOR respectively CEILING. ![]() The value ranges can be retrieved by the functions MIN and MAX. To set up a category grid I first retrieved the following variables: x value range the minimal and maximal value in x y value range the minimal and maximal value in y x span the actual difference between the max and min x values y span same as above for y grid size the number of categories for x and y, the higher this number the finer the grid (do twice for a non square grid) x step the range between the lower and upper bound of one category, determined by x span and grid size y step see x step ![]() Since we want to sort values (value pairs) we need some categories they fit in. But before that: Setting up the data gridĪn example spreadsheed follows at the end of this article, feel free to peek into it to follow my explanations. What I needed was the number of value pairs (x and y) fitting into each of the category pairs. In the case of TL DR we need columns for x values (or x value categories in the case of continuous numerical data) and rows for the y values (or categories). The data structure we need can be seen here. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |