Search code examples
excelexcel-formulaoffsetboxplot

Is there a way to assign a cell value as the cell number in the Excel formula?

I need to arrange the hourly data to calculate the diurnal variation boxplot in Excel and prepare a template to calculate it for other data too. I have found an OFFSET and also INDEX excel function to do it in the link (https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2010/drag-horizontally-increase-reference-vertically/f35cdc98-e6b4-43d4-9128-5fdffcdb93ed).

I can get all the vertical values in the horizontal form (like the transpose) but I would have to cut and paste all the data for 365 days to arrange it in the form presented in the picture.

For other dates, I want to change the cell number in the reference value($B$4) in the OFFSET formula to another value shown in Column D (4,28,52....) and drag it down until I arrange the values for 365 days.

*Is there a way that I could incorporate the cell value in column D (4,28,52....) in the OFFSET formula (=OFFSET($B$4,(COLUMN(A1)-1)1,0)), replacing $B$4 with $B$28, $B$52 and so on?

If there is a simpler way to arrange these values, that would be much appreciated too. Thank you in advance!

Screen shot of the excel tab

Solution

  • I would just split the dateTime column by delimiter or fixed width (Text to Columns function on Data tab in Ribbon) so you have one column for date and one for time and then simply put it in the pivot table.