Dougal Ross said:
I have 2 formulas I am using to look for conditions in my data.
One to find when one of two criteria is met:
=SUMPRODUCT((V4:V20>1.05)+(X4:X20>8.05))
and one to find when BOTH criteria are met:
=SUMPRODUCT(--(V4:V20>1.05),--(X4:X20>8.05))
I am regularly adding to the data and I only want these formulas to
always work for the last 16 rows only (rows 4-20). At the moment
however if I insert a row the formulas change from row 20 to 21.
How do I fix it at 20 so when I insert a new row it doesn't change?
I'm confused. If you "want these formulas to always work for the last 16
rows only", don't you want V4:V20 and X4:X20 to change to V5:V21 and X5:X21
when you add one new row, for example, instead of "fix[ing] it at 20"?
(If you want to "fix it at 20", you might use INDIRECT("V4:V20") for
example. Otherwise....)
Assuming you want V5:V21 and X5:X21, and assuming that column V and X always
have the same number of lines of data, I would suggest that you put the
following formula into some cell, e.g. Z1:
=MATCH(1E300,V:V)
If you know that the data will never go beyond row 10000, say, it would be
better to write:
=MATCH(1E300,V1:V10000)
The MATCH formulas produce the row number of the last number in column V
(and X presumably). 1E300 is presumably larger than any expected number in
volumn V.
Then, your SUMPRODUCT formulas can be (with some other unrelated corrections
and improvements):
=SUMPRODUCT(--((INDEX(V:V,Z1-19):INDEX(V:V,Z1)>1.05)
+(INDEX(X:X,Z1-19):INDEX(X:X,Z1)>8.05)>0))
=SUMPRODUCT((INDEX(V:V,Z1-19):INDEX(V:V,Z1)>1.05)
*(INDEX(X:X,Z1-19):INDEX(X:X,Z1)>8.05))
Note the important correction in the first formula. Your intention is to
count the number of rows when V>1.05 or X>8.05. If both conditions are
true, your original formula would count as 2. I presume you want to count
as 1. Right?
Also note the simplication(?) in the second formula. It is certainly easier
to write. It might also be more efficient.
Finally, if you are using XL2007 or later, you can replace the last
SUMPRODUCT with the following:
=COUNTIFS(INDEX(V:V,Z1-19):INDEX(V:V,Z1),">1.05",
INDEX(X:X,Z1-19):INDEX(X:X,Z1),">8.05")
FYI, I use the form INDEX:INDEX instead of OFFSET(V:V,Z1-19,0,20,1), for
example, because OFFSET is a volatile function, whereas INDEX is not.
Formulas that use OFFSET are recalculated every time any cell in any
worksheet in the workbook is edited. Not so bad if you have only a few such
formulas. It can slow thing down significantly if you have thousands of
such formulas.
