How to define a range from blank cell to blank cell

[TheN]

I'm basically asking how to define a range that checks from blank cell to blank cell

I'd like to be able to find an "average value" on a variable amount of cells. My cell lists can be up/down or left/right.

So, if I have this formula in cell C2, and the group of cells is C2:C8, I want to be able to "drag" my formula down to C8 and not have my range change. I do not mean something as simple as $C$2:$C$8. Basically, I am looking for a way to have it test something along the lines of ISBLANK() in either up/down or left/right direction to define my range of cells.

A sample workbook is attached to illustrate the problem. I used a random function so the cells can actually update their values to make testing it a little easier with changing data. Basically, I want an average formula that goes up/down and one that goes left/right which checks against all cells in each group. The group ends once you hit a blank cell in either direction, instead of defined groups, because I need to be able to add/delete cells as necessary.

Edit: Adding in cells is done by copy pasting a cell oriented the same way, in case someone was questioning that.

Any help would be appreciated working out the formula(s).

[TheN]

I was reading up on dynamic named ranges and thought something similar could be applied to this. Is that possible?

[Glenn Kennedy]

Here's a possible solution (code stolen from Jindon...). Two UDFs, one which SUMS cells with a border and another which counts them. Then you can calculate an average and come up with a CF condition.

Please Login or Register  to view this content.

It requires the cells to have FULL borders. I assume that that'll be OK. Enable macros when opening

[Glenn Kennedy]

Or did you want each shape treated separately???

[TheN]

I want each "group" of cells treated as a single unit.

My actual file has a lot more than that going on, and the example numbers are found with index matches and used solely for the conditional formatting purposes, so those numbers aren't actually the cell values.

So if this could be done with just formulas and no macros, that'd be sweet (that way I could tweak them to work for my purposes).

One note, since you were using borders for some reason, the actual cells would have borders like this in my actual data. There are two cells that I am treating as one unit for each individual cell in the example file.

[TheN]

A better explanation of what I am hoping for, updated thread name to be more appropriate also.

[Glenn Kennedy]

Can B10 and D13 become filled with a number? If so, do the groups merge???

can you guarantee that B3 will always be empty?

[sanram]

I am not sure that what are you trying to do. So please see the attachment and let me know if you are trying this. Then I will do the rest.

[TheN]

Can B10 and D13 become filled with a number? If so, do the groups merge???

can you guarantee that B3 will always be empty?

Yes B10 and D13 can become filled, and then the groups would merge.

Yes B3 will always be empty, in the sense that a up/down group will never "touch" a right/left group. There will always be at least 1 empty cell between them.

[TheN]

I am not sure that what are you trying to do. So please see the attachment and let me know if you are trying this. Then I will do the rest.

possibly, what exactly is this formula?

Please Login or Register  to view this content.

I've never seen CPos or Cavg before. So I can't even begin to understand it since a search turned up nothing also.

[TheN]

I am not sure that what are you trying to do. So please see the attachment and let me know if you are trying this. Then I will do the rest.

Ok, I figured out what they were now, named ranges.

Yes, that method of naming the ranges seems to work (as far as I can tell). If you can kindly explain how they work and/or explain how to refer to the name ranges when using in formulas, that would be awesome. I will continue trying to tear apart your formulas on my own until you respond. Maybe I'll figure it out before then.

For reference:

CAvg:

Please Login or Register  to view this content.

CPos:

Please Login or Register  to view this content.

CRange:

Please Login or Register  to view this content.

The conditional formatting and all that isn't an issue, I can work out the logic on that myself, it was naming the group of cells that I was having the issue with, which you seemed to have solved. Does this only work for vertical culumns? So I could make these vPos, vRange, vAvg, and set up hPos, hRange, hAvg in the same way? I'd defintely like you to explain these a bit if you don't mind.

[sanram]

See the attachment. Here you will see them working both on vertical and horizontal. Only you need to adjust the formula for them.

According to attached file :
1. My 1st approach is the named range "VCRange" for vertical and HCRange for horizontal. To use my procedure you have to leave the Column A and Row 1 Blank. Then click on B2 and start creating the named ranges.

Please Login or Register  to view this content.

VCRange is detecting the range from the starting row of the vertical group for that cell to row 1000 (You can change this 1000 as long as you need) and HCRange is detecting the range from the starting row of the vertical group for that cell to row 1000 (You can change this 1000 as long as you need). To do this for vertical it is finding the 1st blank cell above for that cell. Then taking the next row of that blank cell as a 1st row of the range and 1000 is the last row. And vice versa for horizontal.

2. 2nd approach is VCAvg for vertical and HCAvg for horizontal

Please Login or Register  to view this content.

VCAvg is calculating the 1/3 of the group for vertical and HCAvg for horizontal. To do so it it is detecting the 1st blank cell in the range VCRange and taking the count up to it's previous row. Now you have the number of items in this group and you are getting 1/3 by dividing 3. And vice versa for horizontal.

3. 3rd approach is VCPos for vertical and HCPos for horizontal.

Please Login or Register  to view this content.

VCPos is detecting the position of that cell in the vertical group and HCpos in horizontal group.

Then I have used the conditional formatting as belows:

For Vertical :

Please Login or Register  to view this content.

For Horizontal :

Please Login or Register  to view this content.

I hope that it will make sense now.

[TheN]

2. 2nd approach is VCAvg for vertical and HCAvg for horizontal

Please Login or Register  to view this content.

VCAvg is calculating the 1/3 of the group for vertical and HCAvg for horizontal. To do so it it is detecting the 1st blank cell in the range VCRange and taking the count up to it's previous row. Now you have the number of items in this group and you are getting 1/3 by dividing 3. And vice versa for horizontal.

Okay. So this is basically what is counting the cells (if I understand correctly). So,

Please Login or Register  to view this content.

is basically my "group", i.e. $B$2:$B2? or is there nothing that can be equated to a cell reference like that? Or am I over-complicating things, and it is simply VCRange? Is there a way to make it so I can do something along the lines of VCRange(B) and it will reference the vertical range in column B. For example making the same logic into a UDF somehow?

This is the part I need to truly understand, because I have a lot of different conditional formatting going on, so I need to be able to tweak each individually (and it will make maintenance easier if I need to change something).

[sanram]

Okay. So this is basically what is counting the cells (if I understand correctly).

Yes. You are correct. It is counting the cells of the respective group. Say, You are on B6. Then it will count the number of cells in $B$4:$B$9 on the example sheet.

If you want to create the range of your group, then click on B2 and create the below named range. It will represent respective group range.

Please Login or Register  to view this content.

[TheN]

I'm having difficulties following this still



I know your ranges are working, but I can't figure out for the life of me how to utilize them for what I want. I'll try to explain as best as I can.

So right now, you have VCAvg and HCAvg finding the first couple cells and last cells of the group. Okay, that proves it recognizes the grouping correctly (they change when I add in cells to complete ranges, etc).

However, when I try tweaking this into anything more complex, I am having...issues. For example, if I want it to return the highest value of the range and format that using max() I can't seem to get the syntax right. Could you possibly show how to make it work nested in other functions that look at ranges/arrays (max, min, match, just to name a few). I basically need to know how to reference a group as an array/range utilizing this. That way I can use other formulas to get desired results.

an example or two would be fine, I just need to be able to apply it in a multitude of ways.

[shg]

One way:

Please Login or Register  to view this content.

	B	C	D	E	F	G	H	I	J	K
2	-283	-445	-531	-575	-419	-103
3
4	-913		-988			-859			-558
5	-177		-850			-756			-235
6	-893		-633			-810			-517
7	-130		-123			-207			-380
8	-103		-838						-920
9	-152		-268			-249			-754
10			-200			-449			-610
11	-796		-252			-273
12	-434		-883			-599			-196
13	-987					-626			-462
14	-928		-306			-329			-673
15	-604		-131			-340			-255
16	-869		-617			-818			-657
17
18	-908	-422	-528	-940	-982	-581	-647	-455	-104	-320

You could use a UDF to return the current region of a cell, but CurrentRegion doesn't work in UDFs.

[TheN]

I'm not familiar enough with VBA (yet) to be able to upkeep that, unfortunately. I'm still trying to learn the ins and outs of formulas before I delve into VBA. I have a little understanding of it, but wouldn't be able to code it from scratch. I'd have to record macro and then look at what it inputs to be able to 'tweak' to what I need. As I said earlier, the values for the CF in cells are found with index(match() in the CF itself, allowing for the cell value to be something else entirely. However, thank you for taking the time to respond and to make that table, I know just how time consuming that can be.

[shg]

You're welcome.

[sanram]

I'm having difficulties following this still

an example or two would be fine, I just need to be able to apply it in a multitude of ways.

Ok. I have created a table to make this clear. Please read my above description carefully and see the outputs on the attached document. Hope that it will make you clear. As you need the group range so I have added this (VGRange, HGRange) and changed the formula of VCAvg, HCAvg, VCPos & HCPos as I have the group range now. I have used INDIRECT with VGRange and HGRange in the table to find max value. But In conditional formatting you can use MAX(VGRange) or MAX(HGRange) as I have created the named range with INDIRECT.

Hope that you will be clear now.

[TheN]

Thank you so much, that is perfect.

[sanram]

You are welcome and thanks for the rep