Identifying a row name from after finding the row with the highest value

Hi,

Hope all well! I am really struggling trying to work out the below so I am hoping someone can help.

Firstly, I'm using Microsoft 365 (business) and running Windows 10.

I have a situation whereby I have many rows of data every month to analyse.

So I have, say, 31 rows of data, with the first column being the date of the month, and the first row being the time of day (split by half hour) - so 49 columns in total.

The data itself is just standard numbers.

I need to have Excel identify the highest number in all 31 rows and 48 columns - I can do this part easily, however, I need Excel to then reference the date (row title) and the time of day (column title) the highest number occured in i.e. these would be output to two different cells with the time in one cell and the date in another.

That is the first part!

I then need to have the row that has been identified as having the highest number in it referenced into a new row (the full row of data, not just the cell with the highest number). And all the data in this newly referenced row needs to be multiplied by 2.

If anyone can help with this it would be very much appreciated!

Thanks,

James

Hello FlameRetired.

Thanks for your response.

I have attached an example file.

Each month I need to identify the highest number (highlighted in yellow).

In this example, the highest number takes place on 27 December at 08:30 (highlighted in orange).

That part is fine, however, I then need to have the date and time the highest figure takes place referenced into some cells (in this example C38 and C39).

Also, for the date the highest figure takes place, I need that full row of data to be referenced underneath the rest of the data - and this data needs to be multiplied by two.

If you could help it would be very greatly appreciated.

Thanks,

James

Hi Ace_XL.

Thanks however, I have copied in the formulas (into the attached file) and receiving the cells that say #SPILL!.

Also, cells from D39 across appeared to be filled with data that isn't required.

If you could help that would be great.

Thanks,

James

@Ace_XL
Both the Sumproduct need to be array entered if you have access to the new Dynamic Array functions, otherwise it tries to return a array into multiple cells.

Hi all,

Thanks very much for all your help. It is great. I have been using your formulas. From the data I create graphs and there are many tabs of the data so it does take a long time!

An issue I have come across is when the data (highlighted in yellow) has two or more occurrences of the same value. If this was to take place, ideally the date and time it occurs (over and above the first occurrence of the highest number) would be presented below e.g. in cells C41 and C42 etc.

Also, if the data has two or more entries that are the same, it does not matter which full day's data (that has the same highest number) is presented in line 36 and multiplied by 2 - at the moment it is zeroing the data in line 36 if two or more data entries have the same value.

Many thanks,

James

Hi, Please upload the final file

Hi all, please find attached a modify file to help demonstrate the issue I was having if more than 1 cell of data has the same value.

I have entered 3 cells of data with the value of 1000.

As you will see, this is leading to #REF! errors.

As there are 3 cells with the same value in this example, it would be the best if each of these values were listed below the initial high value e.g. in cells C41 and C42, and also C44 and C45. And this would increase to 4 entries if there were 4 high values the same, and so on.

The values in Row 36 just need to be from one of the days where the highest value has been seen - it doesn't matter which day if there is more than one day with the same high value.

I hope I've been able to explain that ok!

Thanks,

James

I would just like to say a very big thank you to everyone that has helped me out. This is a great resource.

Thanks again.

James