Shorten Formula

  • Thread starter Thread starter GTVT06
  • Start date Start date
G

GTVT06

Can someone help me shorten this formula? I'm not sure if the
mathamatics can be shortened by altering the formula or using a
different formula to figure out the problem, but the path name makes it
exceed the max amount of charachters. Changing the linking files path
location is not an option. I know I can achieve it by putting different
formulas in a couple of seperate cell's but I'm trying to get the final
result by only using one cell, I know the path name can be shortened by
using '[pn] instead, but the name of the spreadsheet changes every
month, so I can't easily find a replace 200607.xls to 200608.xls next
month for the cell's with these formulas like I can on all of the other
formulas, since the formula will not contain 200607.xls if I use '[pn].

*The value of I56 is a date

I would like to be able to shorten this formula to be able to show the
files full path location:

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$18:$AG$18/(SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$19:$AG$19)+SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA information'!$D$18:$AG$18))))

This is how I currently have it, but it creates problems, when the path
needs to be changed every month:

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18/(SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$19:$AG$19)+SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18))))
 
Have you considered using the old DOS command SUBST to replace the path with
a single drive letter e.g.

SUBST x: C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\DAILY Daily
DBMA information

then use x:\[Daily account DBMA information 200607.xls] in your formula
 
GTVT06 said:
Can someone help me shorten this formula? I'm not sure if the
mathamatics can be shortened by altering the formula or using a
different formula to figure out the problem, but the path name makes it
exceed the max amount of charachters. Changing the linking files path
location is not an option. I know I can achieve it by putting different
formulas in a couple of seperate cell's but I'm trying to get the final
result by only using one cell, I know the path name can be shortened by
using '[pn] instead, but the name of the spreadsheet changes every
month, so I can't easily find a replace 200607.xls to 200608.xls next
month for the cell's with these formulas like I can on all of the other
formulas, since the formula will not contain 200607.xls if I use '[pn].

*The value of I56 is a date

I would like to be able to shorten this formula to be able to show the
files full path location:

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$18:$AG$18/(SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$19:$AG$19)+SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA information'!$D$18:$AG$18))))

This is how I currently have it, but it creates problems, when the path
needs to be changed every month:

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18/(SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$19:$AG$19)+SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18))))
Click to expand...

Couldn't you name a cell "ThisFile" (Insert/Name/Define) or suchlike, and
put the full path in the named cell, then put ThisFile in your formula? I'm
sure there'd be a way... I'm not familiar with how to, but there must be...

Beege
 
Beege said:
GTVT06 said:
Can someone help me shorten this formula? I'm not sure if the
mathamatics can be shortened by altering the formula or using a
different formula to figure out the problem, but the path name makes it
exceed the max amount of charachters. Changing the linking files path
location is not an option. I know I can achieve it by putting different
formulas in a couple of seperate cell's but I'm trying to get the final
result by only using one cell, I know the path name can be shortened by
using '[pn] instead, but the name of the spreadsheet changes every
month, so I can't easily find a replace 200607.xls to 200608.xls next
month for the cell's with these formulas like I can on all of the other
formulas, since the formula will not contain 200607.xls if I use '[pn].

*The value of I56 is a date

I would like to be able to shorten this formula to be able to show the
files full path location:

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$18:$AG$18/(SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$19:$AG$19)+SUMPRODUCT(--(''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$10:$AG$10<=I56),''C:\Departments\Operations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA information'!$D$18:$AG$18))))

This is how I currently have it, but it creates problems, when the path
needs to be changed every month:

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18/(SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$19:$AG$19)+SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18))))
Click to expand...

Couldn't you name a cell "ThisFile" (Insert/Name/Define) or suchlike, and
put the full path in the named cell, then put ThisFile in your formula?
I'm sure there'd be a way... I'm not familiar with how to, but there must
be...

Beege
Click to expand...

Also Check out the CELL("filename", A1) function...

Beege
 
You can create a defined name in your workbook referring to the other
oba=[yourfilename.xls]

then use indirect for which the source file must be OPEN
=INDIRECT(oba&"yourworksheet!d140")
 
GTVT06 wrote...
Can someone help me shorten this formula? I'm not sure if the
mathamatics can be shortened by altering the formula or using a
different formula to figure out the problem, but the path name makes it
exceed the max amount of charachters. Changing the linking files path
location is not an option. . . . ....
I would like to be able to shorten this formula to be able to show the
files full path location:

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$18:$AG$18
/(SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$19:$AG$19)
+SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$18:$AG$18))))
Click to expand...
....

Some typos: you've doubled single quotes before the drive letter in all
the external refernces. I'll remove the extraneous ones, reformat and
rewrite your formula with the following replacements: <p> for path, <f>
for filename and <s> for worksheet name.

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18
/(SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$19:$AG$19)
+SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18))))

The way you have it written, you're dividing each cell in
'<p>[<f>]<s>'!$D$18:$AG$18 by the sum of two SUMPRODUCT calls. Unless
you're working with values on the order of 1E300, you can & should
defer the division. That is,

Sum[condition, a / (Sum[condition, b] + Sum[condition, c])]
= Sum[condition, a] / (Sum[condition, b] + Sum[condition, c])

Further, since your condition array is the same for all SUMPRODUCTS,
you could reduce your formula to

Sum[condition, a] / Sum[condition, (b + c)]

And since your condition array is 1D and b and c arrays are adjacent,
you could reduce this further to (quasi-Excel syntax)

Sum[condition, a] / Sum[condition * b:c]

That is, try

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18)
/SUMPRODUCT(('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56)
*'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19))

Even so, you're dealing with 3 static ranges,

'<p>[<f>]<s>'!$D$10:$AG$10
'<p>[<f>]<s>'!$D$18:$AG$18
and either '<p>[<f>]<s>'!$D$18:$AG$19 or '<p>[<f>]<s>'!$D$19:$AG$19

You could create three defined names, e.g.,

Condition referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10

CurVal referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18

BothVals referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19

and reduce your formula to

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--(Condition<=I56),CurVal)
/SUMPRODUCT((Condition<=I56)*BothVals))

then you'd just need to change the defined names each month.
 
That worked like a charm!!! Thanks alot!!!


Harlan said:
GTVT06 wrote...
Can someone help me shorten this formula? I'm not sure if the
mathamatics can be shortened by altering the formula or using a
different formula to figure out the problem, but the path name makes it
exceed the max amount of charachters. Changing the linking files path
location is not an option. . . . ...
I would like to be able to shorten this formula to be able to show the
files full path location:

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$18:$AG$18
/(SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$19:$AG$19)
+SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$18:$AG$18))))
Click to expand...
...

Some typos: you've doubled single quotes before the drive letter in all
the external refernces. I'll remove the extraneous ones, reformat and
rewrite your formula with the following replacements: <p> for path, <f>
for filename and <s> for worksheet name.

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18
/(SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$19:$AG$19)
+SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18))))

The way you have it written, you're dividing each cell in
'<p>[<f>]<s>'!$D$18:$AG$18 by the sum of two SUMPRODUCT calls. Unless
you're working with values on the order of 1E300, you can & should
defer the division. That is,

Sum[condition, a / (Sum[condition, b] + Sum[condition, c])]
= Sum[condition, a] / (Sum[condition, b] + Sum[condition, c])

Further, since your condition array is the same for all SUMPRODUCTS,
you could reduce your formula to

Sum[condition, a] / Sum[condition, (b + c)]

And since your condition array is 1D and b and c arrays are adjacent,
you could reduce this further to (quasi-Excel syntax)

Sum[condition, a] / Sum[condition * b:c]

That is, try

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18)
/SUMPRODUCT(('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56)
*'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19))

Even so, you're dealing with 3 static ranges,

'<p>[<f>]<s>'!$D$10:$AG$10
'<p>[<f>]<s>'!$D$18:$AG$18
and either '<p>[<f>]<s>'!$D$18:$AG$19 or '<p>[<f>]<s>'!$D$19:$AG$19

You could create three defined names, e.g.,

Condition referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10

CurVal referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18

BothVals referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19

and reduce your formula to

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--(Condition<=I56),CurVal)
/SUMPRODUCT((Condition<=I56)*BothVals))

then you'd just need to change the defined names each month.
Click to expand...
 
Thanks again Harlan so how can I re make the formula to only calculate
data from the past week?
I56 is my date value I didn't know if there was a way to add a
=I56&<=I56 or <=I56-7, or something in the formula. I pasted the formula that I would like to tweak below (The one you fixed for me) If you could figure this out, I would greatly appreciate it!
Click to expand...

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18)
/SUMPRODUCT(('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56)
*'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19))
Harlan said:
GTVT06 wrote...
Can someone help me shorten this formula? I'm not sure if the
mathamatics can be shortened by altering the formula or using a
different formula to figure out the problem, but the path name makes it
exceed the max amount of charachters. Changing the linking files path
location is not an option. . . . ...
I would like to be able to shorten this formula to be able to show the
files full path location:

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$18:$AG$18
/(SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$19:$AG$19)
+SUMPRODUCT(--(''C:\Departments\Operations\DBMA Team\DBMA\
DBMA Reports\DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
''C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\
[Daily account DBMA information 200607.xls]
Daily DBMA information'!$D$18:$AG$18))))
Click to expand...
...

Some typos: you've doubled single quotes before the drive letter in all
the external refernces. I'll remove the extraneous ones, reformat and
rewrite your formula with the following replacements: <p> for path, <f>
for filename and <s> for worksheet name.

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18
/(SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$19:$AG$19)
+SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18))))

The way you have it written, you're dividing each cell in
'<p>[<f>]<s>'!$D$18:$AG$18 by the sum of two SUMPRODUCT calls. Unless
you're working with values on the order of 1E300, you can & should
defer the division. That is,

Sum[condition, a / (Sum[condition, b] + Sum[condition, c])]
= Sum[condition, a] / (Sum[condition, b] + Sum[condition, c])

Further, since your condition array is the same for all SUMPRODUCTS,
you could reduce your formula to

Sum[condition, a] / Sum[condition, (b + c)]

And since your condition array is 1D and b and c arrays are adjacent,
you could reduce this further to (quasi-Excel syntax)

Sum[condition, a] / Sum[condition * b:c]

That is, try

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18)
/SUMPRODUCT(('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56)
*'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19))

Even so, you're dealing with 3 static ranges,

'<p>[<f>]<s>'!$D$10:$AG$10
'<p>[<f>]<s>'!$D$18:$AG$18
and either '<p>[<f>]<s>'!$D$18:$AG$19 or '<p>[<f>]<s>'!$D$19:$AG$19

You could create three defined names, e.g.,

Condition referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10

CurVal referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18

BothVals referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19

and reduce your formula to

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--(Condition<=I56),CurVal)
/SUMPRODUCT((Condition<=I56)*BothVals))

then you'd just need to change the defined names each month.
Click to expand...
 
GTVT06 wrote...
Thanks again Harlan so how can I re make the formula to only calculate
data from the past week?
I56 is my date value I didn't know if there was a way to add a
like to tweak below (The one you fixed for me) If you could figure this out, I would
greatly appreciate it!
Click to expand...
....

The condition x >= I56 AND x <= I56 is the same as x = I56, and the
condition x >= I56 AND x <= I56-7 is the same as FALSE (i.e., never
true). And what would x be? TODAY()?

What do you really mean?
 
Your right... x would be the !$D$10:$AG$10 in the formula, which is a
date range for the month (i.e. 7/01/06 - 7/31/06) I56 is a week ending
date. (i.e. 7/22/06) I would like the formula to capture all data for
the week, which would be 7/16/06 - 7/22/06 so actually I may need
something like if I56 >=I56-7 AND <=I56 but I don't know a way of
figuring it into the formula. The way I have the formula right now, is
giving me the month to date percentage. Now I was needing to figure the
formula out for weekly data.
 
GTVT06 wrote...
Your right... x would be the !$D$10:$AG$10 in the formula, which is a
date range for the month (i.e. 7/01/06 - 7/31/06) I56 is a week ending
date. (i.e. 7/22/06) I would like the formula to capture all data for
the week, which would be 7/16/06 - 7/22/06 so actually I may need
something like if I56 >=I56-7 AND <=I56 but I don't know a way of
Click to expand...
....

This particular part makes no sense, but I understand from the first
few lines.

Replace both instances of

--('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]Daily DBMA information'!$D$10:$AG$10<=I56)

with

('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]Daily DBMA information'!$D$10:$AG$10<=I56)
*('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]Daily DBMA information'!$D$10:$AG$10>I56-7)

Note: if you want 7 days between I56-7 and I56, and you want I56 to be
included as the last of the 7 days, then you CAN'T include I56-7 as the
first day. If you did include I56-7, you'd get 8 days rather than 7
days. For example, if I56 were 12, I56-7 would be 5, and I56-7 to I56
would be

5 1st day
6 2nd day
7 3rd day
8 4th day
9 5th day
10 6th day
11 7th day
12 8th day

Alternatively, if you want the most recent week ending on a Sunday,
that week would begin on the preceding Monday rather than the preceding
Sunday.

On the other hand, maybe you want to include I56-7 but not I56. If
that's the case, use

('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]Daily DBMA information'!$D$10:$AG$10<I56)
*('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]Daily DBMA information'!$D$10:$AG$10>=I56-7)
 
Back
Top