PC Review


Reply
Thread Tools Rate Thread

Fix columns for sumproduct

 
 
Dougal Ross
Guest
Posts: n/a
 
      15th Mar 2012
Hi,
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?

Thanks
 
Reply With Quote
 
 
 
 
Dougal Ross
Guest
Posts: n/a
 
      15th Mar 2012
Sorry, the headng should say "Fix cells" not columns.
 
Reply With Quote
 
 
 
 
joeu2004
Guest
Posts: n/a
 
      15th Mar 2012
"Dougal Ross" <(E-Mail Removed)> wrote:
> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM
sumproduct? sumif(sumproduct)? =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM
SUMPRODUCT - NO SUMPRODUCT! =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 3 23rd Nov 2005 03:36 PM
Microsoft Security Bulletin MS04-030 - fix fix fix fix fix George Hester Microsoft Windows 2000 1 24th Jun 2005 09:15 PM
Fix fix fix Rick Windows XP Basics 4 30th Jun 2004 07:56 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:33 PM.