How do I prevent a formula range from changing when Iinsert a column within said range?

[George]

e.g.
Column A Header = 100%> 4-week
Column A = =COUNTIF($D$2:G$2,">=1.0")
Column B Header = >80% 4-week
Column B = =COUNTIF($D$2:G$2,">=0.8")
Column C Header = >80% 8-week
Column C = =COUNTIF($D$2:J$2,">=0.8")

Each time I insert a New Column D to add a new week of data, the Formulas adjust to include another week.

Column A Header = 100%> 4-week
Column A = =COUNTIF($D$2:H$2,">=1.0")
Column B Header = >80% 4-week
Column B = =COUNTIF($D$2:H$2,">=0.8")
Column C Header = >80% 8-week
Column C = =COUNTIF($D$2:K$2,">=0.8")

How do I prevent this from happening?


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Bob Phillips]

Try

=COUNTIF(OFFSET($A$2,0,3,1,5),">=1.0")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

One way:

=COUNTIF(OFFSET($D2,0,0,1,4),">=1.0")

e.g.
Column A Header = 100%> 4-week
Column A = =COUNTIF($D$2:G$2,">=1.0")
Column B Header = >80% 4-week
Column B = =COUNTIF($D$2:G$2,">=0.8")
Column C Header = >80% 8-week
Column C = =COUNTIF($D$2:J$2,">=0.8")

Each time I insert a New Column D to add a new week of data, the Formulas adjust to include another week.

Column A Header = 100%> 4-week
Column A = =COUNTIF($D$2:H$2,">=1.0")
Column B Header = >80% 4-week
Column B = =COUNTIF($D$2:H$2,">=0.8")
Column C Header = >80% 8-week
Column C = =COUNTIF($D$2:K$2,">=0.8")

How do I prevent this from happening?

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Ron Coderre]

Try something like this:

This formula will ALWAYS calculate on cells D2:G2
=COUNTIF((INDEX(2:2,1,4):INDEX(2:2,1,7)),">=1.0")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

Clarification....

=COUNTIF((INDEX(2:2,1,4):INDEX(2:2,1,7)),">=1.0")

Always calculates on columns D:G

Inserting rows above Row_2 would change the row reference.


***********
Regards,
Ron

XL2002, WinXP