Need proper formula for max

[jackt05]

Dear All Hi,

Location	KM	Needed Output
CHIKHALI	70	70
KARVE NAGAR	44	0
DHANKWADI	38	0
BHOSARI	60	60
KASARWADI	48	0
NARHE	50	0
AMBEGAON PATHAR	48	0
KATRAJ KONDHWA ROAD	36	0
LOHEGAON	42	0
NIGADI	73	73
DHOLE PATIL ROAD	24	0
DHANKWADI	38	0
FURSUNGI	20	0
CHINCHWAD GAON	68	68
THERGAON	64	0
BALEWADI	60	60
NARHE	50	0
NDA	50	0
MUNDHWA ROAD	12	0

<tbody>
</tbody>

I have the above table

In column 3. I want the expected results shown already shown.

I want to use this formula =IF(MAX(B2:B4)=B2,MAX(B2:B4),"") This is to be used only for rows above blank rows.
but the cell ranges changes gradually and for 2nd row the formula range becomes B3:B5
Which results in Wrong answers.
Please if anyone can give proper formula so that the range is always between the blank rows.

Hope I am able to explain the problem.

Thank in Advance
Jack

 

[Fluff]

In that case use

Sub jackt05()
   Dim Rng As Range
   For Each Rng In Range("[COLOR=#ff0000]J:J[/COLOR]").SpecialCells(xlConstants).Areas
      Rng.Offset(, 1).FormulaR1C1 = "=IF(MAX(" & Rng.Address(1, 1, xlR1C1) & ")=rc[-1],MAX(" & Rng.Address(1, 1, xlR1C1) & "),"""")"
   Next Rng
End Sub

 

[jackt05]

Hi Fluff

Somethings not working the formula comes only in the 1st row that also half as this =IF(MAX($J$1)=J1,MAX($J$1),"")

 

[jackt05]

Hi Fluff,

Sorry it is working perfectly as expected you saved a lot of work.

First it did not work because column J contained a formula which after removing it worked.

Thanks for your valuable help.

Jack


Marked as Complete

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

If you are still interested in a direct formula solution, here is an array-entered** formula that should work (put it in cell K2 and copy down)...

=IF(J2="","",IF(J2=MAX(INDEX(J:J,MAX(IF(J$2:J2="",ROW(J$2:J2)))+1):INDEX(J:J,MIN(IF(J2:J$25="",ROW(J2:J$25)))-1)),J2,0))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

NOTE: The red highlight cell reference must be one (or more cells) below the last data cell in Column J.

 

[jackt05]

Thanks Rick,

This also works exactly as required.
I was searching the same thing earlier.

Jack