Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select only every third cell in formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select only every third cell in formula
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 "Buddy" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select only every third cell in formula
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 "Buddy" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to point to (select) a cell to the left from a cell where I enter the = equal sign? | Excel Discussion (Misc queries) | |||
How to select preceeding cell in a formula? | Excel Worksheet Functions | |||
I cannot select a single cell or pull down cell contents | Excel Worksheet Functions | |||
Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) | |||
select multiple cell ranges in "sumif" formula? | Excel Worksheet Functions |