0

I new at using Excel and need help. I have a sheet that contains operations and headers that represent names but for confidentiality it is letters. What I am trying to do is find the max,min within each operation(rows) between A - O and avoiding the sum and ops ID numbers; however, if I were to insert a column P is there a way to be able to count that?

I tried using =MAX(2:2) and gives me the the operation ID which isn't what I want. I tried =MAX(C2:Q2) it gives me the max value, but it I add a column P it doesn't take that into account.I was thinking creating an index of A-P and using it to match the index with the headers and find the max within that criteria, but I am just not sure how to do it or if there is a simpler way of doing it.

enter image description here

Dominique
  • 16,450
  • 15
  • 56
  • 112
Sally
  • 1
  • 1
    In your Max function you have used `=MAX(C2:Q2)`. You are limiting the columns from C2 to Q2. If you Add new Column P you need to update the formula Accordingly – Roshan Nuvvula Jun 28 '22 at 02:34
  • Since you are new, I can assure you, you don't need to do that much automatic in your templetes. What @Roshan has suggested that is the best and simple thing to do with. – Saif Ulislam Jun 28 '22 at 07:00
  • first welcome! the formula will update to include column P if column P is inserted. the formula will update to `MAX(C2:R2)` this is only true because the column is within the range not on the edges or outside the range – InjuredCoding Jun 28 '22 at 13:35

3 Answers3

1

If your version of Excel supports LAMBDA() function then can try below functions.

For maximum value =BYROW(C2:S10,LAMBDA(x,MAX(x)))
For minimum value=BYROW(C2:S10,LAMBDA(x,MINIFS(x,x,">0")))

You have to adjust range for your actual case. If you do not want to show 0 values then have to use IF() function inside lambda.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

I believe you are mistaking in your analysis: when you have a formula =MAX(C2:Q2) and you add a column "P", then that column does get taken into account, as you can see from the following example, based on formula =MAX(B2:E2):

enter image description here

Now add an extra column, before "D":

enter image description here

You see that the formula has changed from =MAX(B2:E2) to =MAX(B2:F2).

Dominique
  • 16,450
  • 15
  • 56
  • 112
-1

In your Max function you have used =MAX(C2:Q2). You are limiting the columns from C2 to Q2. If you Add new Column P you need to update the formula Accordingly.

OR

You write a formula to Include other empty columns something like =MAX(C2:Z2). This way if any new column Added the formula will Automatically pick that column values.

Roshan Nuvvula
  • 803
  • 1
  • 8
  • 28
  • to @Dominique's point this is inaccurate. if a column is added within the range the max is looking at the max will automatically include that column. it would be true if a column were added outside or at the edge of the range. – InjuredCoding Jun 28 '22 at 13:37