Select only every third cell in formula

[Guest]

Thanks in advance for any help. I have searched these pages for tips and
tried several of the ideas but to no avail. Although they did solve other
issues and add to my understanding.

Here is the problem I am working in Excel 2002
I am trying to defeat a circular reference. I have colums that contain row
cells in groups of 3.

B1 contains (Col Title)
B2 has a function that return either blank or the word"Base"
B3 has a function that returns either blank or "L" or "EX"
B4 has a function that retuns blank if B3 is blank, otherwise returns a cost
value.
The above three cell pattern repeats down the sheet say b2:b100.
(Also col b is first of many col that do same thing.)

Below this say b102 is a cost value which I want to divide evenly to every
third cell in the range if that third cell has a non blank cell above it
containing the values "L" or "EX"

To figure out how many incidents of "L" or "EX" are in column B2:b100 I used
=Countif and put the function in b101

In every third cell I put a formula (if the cell above contains L or EX get
the total cost for row at bottom and divide it by the result of the countif
function (also at bottom of sheet)

This produces a circular reference because the countif selects the entire
range into which the result of dividing the the total cost by the countif
result is placed. I can make it work by setting cal itteration to something
like 9 but I would like to get rid of the circular logic.

I was thinking if there is a way to select in a formula only every third
cell in group and not the rest of the range in implementing the countif
function that it would elimnate the circular reference. If the range was
unchanging in size, I could just use =Countif(b3,b6,b9, etc... but the range
grows or shrinks depending on use so I don't want to have to go back and
check each time that the right number of cells have been counted.

Idealy I could define the range as a pattern and include only those cells in
the formula and the formula would adjust as I added rows into the sheet.

I hope this makes sense.
Thanks for any ideas.

 

[Brad]

Buddy, I wrote a formula that will count every third item back from the last
value in a column:

=INDEX($A$1:$A$201,ROW()-((ROW($AE$201)-ROW())*3))

I actually reverse-engineered this from someone's fine work on a formula
going the other way. Anyway, you can see how the formula uses Row to jump
around. Hope this helps. Brad

 

[Guest]

Brad
THanks for the tip. I will play with it to see if it will work. Part of
the problem seems to be the need to select the whole range in order to get
the data and then trying to put data back into part of the range. Seems to
create the circular reference. Anyway I will let you know how it goes.
Buddy