0

I need to calculate the maximum number between 2 consecutive non-blank cells in A, but using the values in B. ie. use a formula in column C that will give me the results I manually plotted in D. Bearing in mind the amount of non-blank cells between figures in A is completely random. calulations:

D1 = max(B1:B2) {values in range determing by x}
D3 = max(B3) {values in the range determined by y}
D4 = max(B4:B6) {values in the range determined by z}

  A B C D
1 x 0 1
2   1   
3 y 1 1
4 z 2 3
5   0
6   3

I've tried using these, but no luck with either: 

INDEX(A2:A25;MATCH(FALSE;ISBLANK(A2:A25);0))

and

MAX(IF(NOT(ISBLANK (B2:B25)), ROW(B2:B25),0))
Improve this question
4
  • I don't understand how the values in D relate to those shown in B and A. Could you add a description showing how you derived D? You might need to add row numbers, in order that you can say something like "D1 is the maximum number between B1 (matches x) and B2 (row above y)" -- write in plain English what you actually did to get column D.
    – Andrew Leach
    Jul 6, 2015 at 12:57
  • Your question is unclear - please elaborate more -
    – Prasanna
    Jul 6, 2015 at 13:05
  • Can you accept VBa?
    – Dave
    Jul 6, 2015 at 13:29
  • Why is the value in D4 =MAX(B4:B6)? Is that to assume that A7 is non-blank (we can't see that cell in your table)?
    – XOR LX
    Jul 6, 2015 at 15:12

1 Answer 1

Reset to default
1

Try this formula:

=IF(A1<>"",MAX(OFFSET(B1,0,0,IFERROR(MATCH("*",OFFSET(A2,0,0,COUNTA(B:B)-ROW()+1),0),COUNTA(B:B)-ROW()+1))),"")

enter image description here

To get the height of your data I used COUNTA() on column B, it will work correctly only if you don't have blank cells in column B.

Improve this answer
1
  • Yes that works, thank you. However, my example was a simplified version. In reality, it's columns AHE I'm talking about, hence A=A; B=H; C=E. To make things a little more complicated, I would only use the values in H given the value in I for a certain row is blank. Does this make sense? So the table I'm using goes from A1:I1069 The formula you provided would hence be plotted into E2 as: =IF(A2<>"";MAX(OFFSET(H2;0;0;IFERROR(MATCH("*";OFFSET(A3;0;0;COUNTA(H:H)-ROW()+1);0);COUNTA(B:B)-ROW()+1)));"") But this should only be used if there is no value in I2. @andrew
    – Elena
    Jul 7, 2015 at 8:31

Not the answer you're looking for? Browse other questions tagged .