Expert Solution - Excel COLUMN Problems

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula to calculate the MAX of a range of 167 rows in column D, then find the MAX of the next 167 rows in column D and so on continuously through the entire sheet. Example MAX D2:D169, D170:D337, D338:D505 etc...

Solved by I. Q. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert 30/03/2018 - 03:54

hi!

Excelchat Expert 30/03/2018 - 03:54

hi there

Excelchat Expert 30/03/2018 - 03:56

Do you have your working file with you? Are you able to upload it as attachment?

User 30/03/2018 - 03:58

I pasted the data I need help with

Excelchat Expert 30/03/2018 - 03:58

Okay. Where do you wish to return the MAX values of 167 rows each?

User 30/03/2018 - 03:58

Next to it..so column B is fine.

Excelchat Expert 30/03/2018 - 03:59

okay, let me check it for a bit and come back to you. :) basically, we'll be using the MAX function together perhaps with another one

User 30/03/2018 - 04:02

Thank you. I hope you understand what I'm trying to solve. I need the max value of the first 167 rows starting at row 2 then the next 167 rows after that. and so on. The issue I'm having is how to tell excel to find the MAX for the Next 167 rows continuously

Excelchat Expert 30/03/2018 - 04:03

if you're to look at column B, I pasted a formula which combines INDEX and MAX

User 30/03/2018 - 04:03

I see that but how come the first 17 rows are all the same number now?

User 30/03/2018 - 04:03

should i repaste the info?

Excelchat Expert 30/03/2018 - 04:04

Hmm. Yes if they're not supposed to be that way.

Excelchat Expert 30/03/2018 - 04:04

Just paste in column A

User 30/03/2018 - 04:05

hmm let me try again

Excelchat Expert 30/03/2018 - 04:05

It seems they're all the same?

Excelchat Expert 30/03/2018 - 04:07

what I have with column C is a manually inputted formula to simply validate the correctness of results in column B.

User 30/03/2018 - 04:07

im not sure why the data keeps repeating itself

Excelchat Expert 30/03/2018 - 04:07

Basically, what column B does is it cycles every 167 rows for every row it is copied or dragged down.

Excelchat Expert 30/03/2018 - 04:07

Hmm.

Excelchat Expert 30/03/2018 - 04:08

Are you able to upload an excel file instead, which only contains the details in column A?

Excelchat Expert 30/03/2018 - 04:08

Then I could just input the formula in there and reupload it for your review.

User 30/03/2018 - 04:10

for some reason my excel sheet is acting up

Excelchat Expert 30/03/2018 - 04:11

Okay. You may wish to manually input values in affected cells then.

Excelchat Expert 30/03/2018 - 04:11

Or anyhow, you could just copy paste the formula in B1

Excelchat Expert 30/03/2018 - 04:11

and paste it in your excel

User 30/03/2018 - 04:12

i understand the formula the issue is I don't want it to show the values for the next 167 rows for every row it is dragged down. I want to to only show the values for the range of 167 rows then the value for the next range of 167 rows

Excelchat Expert 30/03/2018 - 04:14

Hmm.When do you wish to show the value of next set then.

User 30/03/2018 - 04:15

when and where ever. the MAX for the next range can be shown next the row where the formula starts. So say the MAX for A2:A169 is show in B2, then A170:A337 can be shown in A170

User 30/03/2018 - 04:17

im trying to redownload the data i have for some reason my excel file went corrupt

User 30/03/2018 - 04:18

thank you for your patience

Excelchat Expert 30/03/2018 - 04:18

In your previous chat, you wish to return the MAX wherever you want it?

User 30/03/2018 - 04:19

im sorry what?

Excelchat Expert 30/03/2018 - 04:20

I mean, you made an example that for the MAX of a2:a169, it could be in B2, while the MAX of the next set of 167 rows could be in B 170 or anywhere right?

User 30/03/2018 - 04:20

yes sure!

Excelchat Expert 30/03/2018 - 04:21

I see. Unfortunately, we will have to manually edit the formula then, everytime we would want a value return. This is since Excel will need to have reference points to know what set it would calculate already.

User 30/03/2018 - 04:22

right thats the issue im having

User 30/03/2018 - 04:22

how do you do that??

Excelchat Expert 30/03/2018 - 04:22

We wouldn't be able to automate that, unfortunately.

Excelchat Expert 30/03/2018 - 04:23

We need to manually input a formula every time, if that's the case.

User 30/03/2018 - 04:23

hmm okay I see, thats what I was trying to figure out to do without doing it manually

Excelchat Expert 30/03/2018 - 04:23

True. If you wish to have the MAX of A170:A337 in cell G67, we need to manually input=MAX(A170:A337) in that cell.

Excelchat Expert 30/03/2018 - 04:24

I would suggest, regarding this, is for us to approach it in a systematic manner. Like displaying it in a top down manner.

User 30/03/2018 - 04:24

okay what do you suggest

Excelchat Expert 30/03/2018 - 04:24

or what is shown in column B. Then we could just include labels

Excelchat Expert 30/03/2018 - 04:25

I would suggest the formula in column B actually. That's a simple way to automate things by simply dragging them down. Then we just need to make labels for them

Excelchat Expert 30/03/2018 - 04:27

Columns D and E shows my suggestion. Column D will include the labels and E will include the actual MAX value.

User 30/03/2018 - 04:28

okay so if we were to do that, would I have to go back and delete all the data that doesnt include the proper data ranges. say i need D2:D169 so then the next cell will show the formula of D3:D170, then D4:D171...would i have to go and delete all of the info until I get to D169

Excelchat Expert 30/03/2018 - 04:29

No. Basically, what the formula does is that it will cycle every 167 rows for every row the formula is dragged down.

Excelchat Expert 30/03/2018 - 04:29

For example, in E1, it will capture A1:A167

Excelchat Expert 30/03/2018 - 04:30

When I drag the formula down to E2, it will capture A168:A335

User 30/03/2018 - 04:30

okay cool so can you automate that or do you have to manually enter every ranfe

User 30/03/2018 - 04:30

range

Excelchat Expert 30/03/2018 - 04:31

If I drag it down again to E3, it will capture the MAX of A336:503. And this will be repeated as you drag it down.

Excelchat Expert 30/03/2018 - 04:31

You dont' need to manually input the range.

Excelchat Expert 30/03/2018 - 04:31

Dragging it down one row will cycle to the next set of 167 rows in column A.

Excelchat Expert 30/03/2018 - 04:32

in column F, I'll try to manually input a formula for the sets of rows.

Excelchat Expert 30/03/2018 - 04:32

This is for us to check if the formula in column E is accurate.

Excelchat Expert 30/03/2018 - 04:34

Done!

Excelchat Expert 30/03/2018 - 04:34

What I did for column F is manually inputting a MAX formula for the sets of rows

Excelchat Expert 30/03/2018 - 04:35

And that would be pretty hard to do if you're dealing with 50k of values

User 30/03/2018 - 04:35

cool. the issue is i dont want to do that manuall for 40000+ rows

Excelchat Expert 30/03/2018 - 04:35

Yes, that's why we have the formula in column E.

Excelchat Expert 30/03/2018 - 04:36

To demonstrate it to you, just drag down E1 down to E5, and you'll see same results.

Excelchat Expert 30/03/2018 - 04:36

Magic, right? :)

User 30/03/2018 - 04:37

but then doesnt E1 give me the max for A1: A169 and E2 gives me A2:A170?

User 30/03/2018 - 04:37

based on that formula

Excelchat Expert 30/03/2018 - 04:37

no it doesn't. the formula doesn't work that way.

Excelchat Expert 30/03/2018 - 04:38

As I've explained a while back, the formula will cycle every 167 rows in column A for every row that the formula is dragged down.

Excelchat Expert 30/03/2018 - 04:38

So if you drag down the formula in E1 to E2, it will not take the range of A:2:A170. Instead, it will cycle to the next 167 rows, which is A168:A335

Excelchat Expert 30/03/2018 - 04:39

And if you drag it down again to E3, it will cycle to the next 167 rows, which is A336:A503

Excelchat Expert 30/03/2018 - 04:39

And so on while you drag it down further.

Excelchat Expert 30/03/2018 - 04:40

look at cell G2.

User 30/03/2018 - 04:40

doesnt the formula say starting at row 5 take the max of row 5 through the next 167 cells?

Excelchat Expert 30/03/2018 - 04:40

no it doesn't. it actually says, starting at row 1, take the max of row 1 through the next 167 cells.

User 30/03/2018 - 04:41

oh really! can you explain to me how to read that formula please

Excelchat Expert 30/03/2018 - 04:41

sure!

Excelchat Expert 30/03/2018 - 04:41

basically, the formula is a combination of INDEX and MAX

Excelchat Expert 30/03/2018 - 04:41

and ROW

Excelchat Expert 30/03/2018 - 04:43

Let's dissect it

User 30/03/2018 - 04:43

yes please!

Excelchat Expert 30/03/2018 - 04:43

you see (ROW(1:1)-1)*167+1

User 30/03/2018 - 04:43

yes

Excelchat Expert 30/03/2018 - 04:43

basically, what that does is it takes the row number inside the ROW(). ROW(1:1) would mean a row number of 1

Excelchat Expert 30/03/2018 - 04:44

But 1-1 = 0, and 0*167+1 would result to 1.

Excelchat Expert 30/03/2018 - 04:45

ROW(2:2) will result to 2 since that's the number of its row

Excelchat Expert 30/03/2018 - 04:46

If we're to dissect the formula in E1, ROW(1:1)-1_*167+1 will result to 1, while the ROW(1:1)*167 will result to 1*167=167.

User 30/03/2018 - 04:46

right so how come that does not take row 2 through 171 instead of row 168:335

Excelchat Expert 30/03/2018 - 04:46

now, the formula would be like =MAX(INDEX(A:A,1):INDEX(A:A,167))

Excelchat Expert 30/03/2018 - 04:47

okay, let's look at formula in E2.

Excelchat Expert 30/03/2018 - 04:47

in the formula, you'll see (ROW(2:2)-1)*167+1 and (ROW(2:2)*167) right?

User 30/03/2018 - 04:47

yes

Excelchat Expert 30/03/2018 - 04:48

ROW(2:2) will result to a value of 2, right?

Excelchat Expert 30/03/2018 - 04:48

(because it takes the row number of the reference range, which is 2)

User 30/03/2018 - 04:49

right so wouldn

Excelchat Expert 30/03/2018 - 04:49

So, substituting 2 in ROW(2:2), it would be (2-1)*167+1 = 1*167+1 = 168

User 30/03/2018 - 04:49

okay

Excelchat Expert 30/03/2018 - 04:50

if we do this as well with (ROW(2:2)*167) , it would be 2*167 = 334

User 30/03/2018 - 04:50

thats strange I used that formula before and the numbers came out differently compared to entering each Max range manually

Excelchat Expert 30/03/2018 - 04:50

Maybe you had an error back then? Look at G2

Excelchat Expert 30/03/2018 - 04:51

it's the MAX value of A2:A170, and it doesn't equal to E2. that proves that the formula in E1, when dragged down to E2, doesn't cover A2:A170. Instead it covers A168:A334

Excelchat Expert 30/03/2018 - 04:52

(which is the result in F2 formula)

User 30/03/2018 - 04:52

Gotcha!

Excelchat Expert 30/03/2018 - 04:52

User 30/03/2018 - 04:52

so I need to use the formula in column E