Need proper formula for max

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
Dear All Hi,


LocationKMNeeded Output
CHIKHALI7070
KARVE NAGAR440
DHANKWADI380
BHOSARI6060
KASARWADI480
NARHE500
AMBEGAON PATHAR480
KATRAJ KONDHWA ROAD360
LOHEGAON420
NIGADI7373
DHOLE PATIL ROAD240
DHANKWADI380
FURSUNGI200
CHINCHWAD GAON6868
THERGAON640
BALEWADI6060
NARHE500
NDA500
MUNDHWA ROAD120

<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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
what version of excel are you using. Then I'm thinking utilis a named range for each group and you insert rows the range will extend and keep it constant
 
Upvote 0
Hi,
I am using Excel 2010

I have around 10 thousand entries as above can't name so many and every day the data is new.
 
Upvote 0
to stop the top range changing use =IF(MAX(B$2:B4)=B2,MAX(B$2:B4),""), which locks the top line
 
Upvote 0
Hi,

We don't get the required range. the outcome result needs from the specific range. here it searches from the top even for the 2nd and third segment.
 
Upvote 0
If the values in col B are hard values try

Rich (BB code):
Sub jackt05()
   Dim Rng As Range
   For Each Rng In Range("B:B").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
 
Upvote 0
Thanks fluff,
it worked perfectly.
Just If I wanted the output in K column what changes do I do.
 
Upvote 0
Are the values still in col B?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top