Formula Advice

[Ken]

I must have used a subject for an earlier post by someone else and
this showed up twice in their reply, so I have changed the subject
name.....
Hi Group!
I'm back again, trying to solve a problem that Don Guillett so
correctly pointed out that needs to be addressed, and I thank you for
that Don!
I have a database that dynamically updates from log entries, which
in turn updates my charts, and they are working extremely well. The
problem is that I'm using SUMPRODUCT in every cell in my daily
database, and that is causing the log entries to become slower to
enter because the database recalculates on every entry, and my
formula
includes far too many cells in that calculation. I have entered a
dynamic named range "MyRange" that correctly includes only the rows
that are populated, but I haven't been able to include that named
range into the calculations for SUMPRODUCT. I have screenshots here
to
illustrate how everything is laid out:
http://www.elodgingatbristol.com/SUMPRODUCT.htm
Can that named range be used in the formula to limit how many
calculations that Excel has to do? Any advice is greatly
appreciated...I've beat my head against the wall for a couple of
weeks
trying different tips that I've seen in the groups, but can't get it
to work. Thanks very much in advance....
Ken

 

[Bob Phillips]

Of course

=SUMPRODUCT(--(myRange>=1700),--(myRange<1800))

--
---
HTH

Bob

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

 

[T. Valko]

It looks like your dynamic range height is based on the last numeric value
in column A and the range is 20 columns wide.

To use that named range you have to wrap it inside an INDEX function and
tell SUMPRODUCT which column of the range to use. Like this:

=SUMPRODUCT(--(INDEX(MyRange,,1)="this"),--(INDEX(MyRange,,2)="that"),INDEX(MyRange,,3))

In the above I'm referencing the first, second and third columns of the
dynamic range.

See this for suggestions on improving calculation efficiency:

http://www.decisionmodels.com/

 

[Pete_UK]

You might like to switch the file to manual calculation while you
enter the data - Tools | Options | Calculation tab, click on Manual.
This will stop the sheet from recalculating while you put data in. You
can then press F9 to recalculate once you are ready, or Tools |
Options | Calculation tab, click on Automatic.

Hope this helps.

Pete

 

[Ken]

You might like to switch the file to manual calculation while you
enter the data - Tools | Options | Calculation tab, click on Manual.
This will stop the sheet from recalculating while you put data in. You
can then press F9 to recalculate once you are ready, or Tools |
Options | Calculation tab, click on Automatic.

Hope this helps.

Pete



- Show quoted text -

Thanks Pete for the advice...I'll certainly use it!
Ken

 

[Ken]

It looks like your dynamic range height is based on the last numeric value
in column A and the range is 20 columns wide.

To use that named range you have to wrap it inside an INDEX function and
tell SUMPRODUCT which column of the range to use. Like this:

=SUMPRODUCT(--(INDEX(MyRange,,1)="this"),--(INDEX(MyRange,,2)="that"),INDEX­(MyRange,,3))

In the above I'm referencing the first, second and third columns of the
dynamic range.

See this for suggestions on improving calculation efficiency:

http://www.decisionmodels.com/

--
Biff
Microsoft Excel MVP






- Show quoted text -

Biff, your formula worked extremely well....the calculation speeds
have been significantly faster....again thanks for your help! Bob
thanks for your help also!
Ken

 

[T. Valko]

It looks like your dynamic range height is based on the last numeric value
in column A and the range is 20 columns wide.

To use that named range you have to wrap it inside an INDEX function and
tell SUMPRODUCT which column of the range to use. Like this:

=SUMPRODUCT(--(INDEX(MyRange,,1)="this"),--(INDEX(MyRange,,2)="that"),INDEX­(MyRange,,3))

In the above I'm referencing the first, second and third columns of the
dynamic range.

See this for suggestions on improving calculation efficiency:

http://www.decisionmodels.com/

--
Biff
Microsoft Excel MVP






- Show quoted text -

Biff, your formula worked extremely well....the calculation speeds
have been significantly faster....again thanks for your help! Bob
thanks for your help also!
Ken
You're welcome. Thanks for the feedback!

 

[Pete_UK]

Glad to hear that - thanks for taking the trouble to feed back.

Pete