I have all data in different column but in same row.
Each 24 column represent 24 hours of one days and all have data on it, let say start from C50 to BEH 50
My requirment is to found out the maximum value of each those 24 column and output would be stored in row 51. I input the formula at C51 = MAX(C50:Z:50) to count the first day, so i would like to how to let the next cell become i.e. C52= MAX(AA50:AX51) automatically without input the formula cell by cell.
Thanks for any suggestion.
Using your example layout:
C51: =MAX(INDEX($C$50:$BE$50,1+24*(ROWS(C$51:C51)-1)):INDEX($C$50:$BE$50,24*ROWS(C$51:C51)))
copied down as required
you could also use OFFSET but though shorter & simpler in terms of syntax unlike the above it is Volatile
C51: =MAX(OFFSET($C$50,,24*(ROWS(C$51:C51)-1),1,24))
copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks for your help.
You suggestion work as my exception.
Furthermore, after founding out the maximum of each 24 column, i would like to how i could highlight those column with different color.
Let say, the maximum volume found on the G51, AA51, after the use of new formula or function, whole Column G and AA would be highlight with different color.
Thanks for any further suggestion.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks