A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

compound if equation?



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2010, 09:54 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default compound if equation?

Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more compound that
I've used before.
Thus far, I've tried using an if equation, that looks through a dataset with
a sumproduct, and if the dataset matches, I perform one equation, and if not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.

  #2  
Old April 28th, 2010, 01:57 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default compound if equation?


"Steve" wrote in message
...
Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more compound
that
I've used before.
Thus far, I've tried using an if equation, that looks through a dataset
with
a sumproduct, and if the dataset matches, I perform one equation, and if
not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.


  #3  
Old April 28th, 2010, 02:02 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default compound if equation?

Hi Steve,

1. Why (APN!$E$4:$E$95&""=$A11&"") rather than (APN!$E$4:$E$95=$A11) ?

2. What kind of result do you want when both data sets have matching values
within? Do you want the results in two seperate cells, added together in
one cell, or does one result take priority over the other (and how is that
priority decided)?




"Steve" wrote in message
...
Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more compound
that
I've used before.
Thus far, I've tried using an if equation, that looks through a dataset
with
a sumproduct, and if the dataset matches, I perform one equation, and if
not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.


  #4  
Old April 28th, 2010, 02:03 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default compound if equation?

oops
  #5  
Old April 28th, 2010, 03:36 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default compound if equation?

Hi Steve.
Thank you for the response.

1- The &"" in the criteria source is a datatype nullifier, as it was once
explained to me. I was having troubles with my data in the beginning, and
after posting here back in August/Sept/November of 2006 on the topic, Harlan
Grove and Roger Govier explained to me the various ways of doing the
sumproduct. I.e., Sumproduct(--()*()*()); Sumproduct((--)*()*()), and the &""
elements. For my purposes the &"" worked more consistently. I'm not sure why
the double -- did not work for my purposes, especially since I've used it
more recently and it has worked.

2- My goal is to check if there are two sources, or one. Each set has its
own ratio that occurs when I want to perform a secondary operation on them.
As such, I have to delineate between them, then perform the secondary
operation, and add those two results together.
If there are only values in dataset A, I perform the ratio operation on
dataset A. If dataset B, perform operation on B.
So far it appears that I was able to come up with a solution, but boy is it
u-g-l-y.

After I'd posted this, I talked with a colleague to "clear out my cobwebs"
and came up with the following.

=IF((SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$ F$4:$F$95=$C11)*(APN!$B$4:$B$95))0),IF((SUMPRODU CT((APN!$E$97:$E$173&""=$A11&"")*(APN!$F$97:$F$173 =$C11)*(APN!$B$97:$B$173))0),(SUMPRODUCT((APN!$E $4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B $4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7),(SUMPRODUCT((APN!$E$4:$E$ 95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B$4:$B$ 95))/TRIM(LEFT($F$6,4))*$E$6)),(SUMPRODUCT((APN!$E$97:$ E$173&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$ 97:$B$173))/TRIM(LEFT($F$7,4))*$E$7))

I had to start with a compound/nested IF, and then insert the sumproduct
eq's accordingly.
I.e.,
IF((),IF((),(),()),())

Where
IF((TestA0),IF((TestB0),(True ResponseA + True ResponseB),(False
ResponseA)),(FalseResponseB))

While it appears to work, I'd like to reduce its complexity-- and I don't
use the name manager because of prior issues faced with it.

Hope this helps clarify.

Thanks again for your response, and helps.

"Steve Dunn" wrote:

Hi Steve,

1. Why (APN!$E$4:$E$95&""=$A11&"") rather than (APN!$E$4:$E$95=$A11) ?

2. What kind of result do you want when both data sets have matching values
within? Do you want the results in two seperate cells, added together in
one cell, or does one result take priority over the other (and how is that
priority decided)?




"Steve" wrote in message
...
Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more compound
that
I've used before.
Thus far, I've tried using an if equation, that looks through a dataset
with
a sumproduct, and if the dataset matches, I perform one equation, and if
not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.


  #6  
Old April 28th, 2010, 07:35 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default compound if equation?

Hmmm, I think you've been overthinking it. Another way of stating your
formula would be:

=if(a=0, b/c, if(b=0, a/d, a/d+b/c))

which simplifies to:

a/d+b/c

because if a = 0, then a/d = 0 and therefore a/d+b/c would equal 0+b/c or
just b/c. The opposite applies if b = 0, then b/c = 0, therefore a/d+b/c
is equivalent to a/d+0. Leaving a/d+b/c to give the correct answer where
neither are 0.

So, your formula becomes:

=SUMPRODUCT(($E$4:$E$95&""=$A11&"")*($F$4:$F$95=$C 11)*($B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT(($E$97:$E$173&" "=$A11&"")*($F$97:$F$173=$C11)*($B$97:$B$173))/TRIM(LEFT($F$7,4))*$E$7

HTH
Steve D.


"Steve" wrote in message
...
Hi Steve.
Thank you for the response.

1- The &"" in the criteria source is a datatype nullifier, as it was once
explained to me. I was having troubles with my data in the beginning, and
after posting here back in August/Sept/November of 2006 on the topic,
Harlan
Grove and Roger Govier explained to me the various ways of doing the
sumproduct. I.e., Sumproduct(--()*()*()); Sumproduct((--)*()*()), and the
&""
elements. For my purposes the &"" worked more consistently. I'm not sure
why
the double -- did not work for my purposes, especially since I've used it
more recently and it has worked.

2- My goal is to check if there are two sources, or one. Each set has its
own ratio that occurs when I want to perform a secondary operation on
them.
As such, I have to delineate between them, then perform the secondary
operation, and add those two results together.
If there are only values in dataset A, I perform the ratio operation on
dataset A. If dataset B, perform operation on B.
So far it appears that I was able to come up with a solution, but boy is
it
u-g-l-y.

After I'd posted this, I talked with a colleague to "clear out my cobwebs"
and came up with the following.

=IF((SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$ F$4:$F$95=$C11)*(APN!$B$4:$B$95))0),IF((SUMPRODU CT((APN!$E$97:$E$173&""=$A11&"")*(APN!$F$97:$F$173 =$C11)*(APN!$B$97:$B$173))0),(SUMPRODUCT((APN!$E $4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B $4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7),(SUMPRODUCT((APN!$E$4:$E$ 95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B$4:$B$ 95))/TRIM(LEFT($F$6,4))*$E$6)),(SUMPRODUCT((APN!$E$97:$ E$173&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$ 97:$B$173))/TRIM(LEFT($F$7,4))*$E$7))

I had to start with a compound/nested IF, and then insert the sumproduct
eq's accordingly.
I.e.,
IF((),IF((),(),()),())

Where
IF((TestA0),IF((TestB0),(True ResponseA + True ResponseB),(False
ResponseA)),(FalseResponseB))

While it appears to work, I'd like to reduce its complexity-- and I don't
use the name manager because of prior issues faced with it.

Hope this helps clarify.

Thanks again for your response, and helps.

"Steve Dunn" wrote:

Hi Steve,

1. Why (APN!$E$4:$E$95&""=$A11&"") rather than (APN!$E$4:$E$95=$A11) ?

2. What kind of result do you want when both data sets have matching
values
within? Do you want the results in two seperate cells, added together in
one cell, or does one result take priority over the other (and how is
that
priority decided)?




"Steve" wrote in message
...
Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more compound
that
I've used before.
Thus far, I've tried using an if equation, that looks through a dataset
with
a sumproduct, and if the dataset matches, I perform one equation, and
if
not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.



  #7  
Old April 28th, 2010, 10:57 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default compound if equation?

Indeed.....
It does work as you've written it- sans the sheet name.

Thank you Steve.
And as to my "overthinking" it... that's an old habit. I keep trying to
break myself of it, but I can only make small short progressional steps. You
know-- the old 3 steps forward, and two steps back bit.

Again, thank you. This matter is solved.

"Steve Dunn" wrote:

Hmmm, I think you've been overthinking it. Another way of stating your
formula would be:

=if(a=0, b/c, if(b=0, a/d, a/d+b/c))

which simplifies to:

a/d+b/c

because if a = 0, then a/d = 0 and therefore a/d+b/c would equal 0+b/c or
just b/c. The opposite applies if b = 0, then b/c = 0, therefore a/d+b/c
is equivalent to a/d+0. Leaving a/d+b/c to give the correct answer where
neither are 0.

So, your formula becomes:

=SUMPRODUCT(($E$4:$E$95&""=$A11&"")*($F$4:$F$95=$C 11)*($B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT(($E$97:$E$173&" "=$A11&"")*($F$97:$F$173=$C11)*($B$97:$B$173))/TRIM(LEFT($F$7,4))*$E$7

HTH
Steve D.


"Steve" wrote in message
...
Hi Steve.
Thank you for the response.

1- The &"" in the criteria source is a datatype nullifier, as it was once
explained to me. I was having troubles with my data in the beginning, and
after posting here back in August/Sept/November of 2006 on the topic,
Harlan
Grove and Roger Govier explained to me the various ways of doing the
sumproduct. I.e., Sumproduct(--()*()*()); Sumproduct((--)*()*()), and the
&""
elements. For my purposes the &"" worked more consistently. I'm not sure
why
the double -- did not work for my purposes, especially since I've used it
more recently and it has worked.

2- My goal is to check if there are two sources, or one. Each set has its
own ratio that occurs when I want to perform a secondary operation on
them.
As such, I have to delineate between them, then perform the secondary
operation, and add those two results together.
If there are only values in dataset A, I perform the ratio operation on
dataset A. If dataset B, perform operation on B.
So far it appears that I was able to come up with a solution, but boy is
it
u-g-l-y.

After I'd posted this, I talked with a colleague to "clear out my cobwebs"
and came up with the following.

=IF((SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$ F$4:$F$95=$C11)*(APN!$B$4:$B$95))0),IF((SUMPRODU CT((APN!$E$97:$E$173&""=$A11&"")*(APN!$F$97:$F$173 =$C11)*(APN!$B$97:$B$173))0),(SUMPRODUCT((APN!$E $4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B $4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7),(SUMPRODUCT((APN!$E$4:$E$ 95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B$4:$B$ 95))/TRIM(LEFT($F$6,4))*$E$6)),(SUMPRODUCT((APN!$E$97:$ E$173&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$ 97:$B$173))/TRIM(LEFT($F$7,4))*$E$7))

I had to start with a compound/nested IF, and then insert the sumproduct
eq's accordingly.
I.e.,
IF((),IF((),(),()),())

Where
IF((TestA0),IF((TestB0),(True ResponseA + True ResponseB),(False
ResponseA)),(FalseResponseB))

While it appears to work, I'd like to reduce its complexity-- and I don't
use the name manager because of prior issues faced with it.

Hope this helps clarify.

Thanks again for your response, and helps.

"Steve Dunn" wrote:

Hi Steve,

1. Why (APN!$E$4:$E$95&""=$A11&"") rather than (APN!$E$4:$E$95=$A11) ?

2. What kind of result do you want when both data sets have matching
values
within? Do you want the results in two seperate cells, added together in
one cell, or does one result take priority over the other (and how is
that
priority decided)?




"Steve" wrote in message
...
Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more compound
that
I've used before.
Thus far, I've tried using an if equation, that looks through a dataset
with
a sumproduct, and if the dataset matches, I perform one equation, and
if
not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.



  #8  
Old April 28th, 2010, 11:43 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default compound if equation?


You're welcome. Sorry about missing the sheet name, copied and pasted from
sheet I was working on, without engaging brain.

As for "overthinking", I've been known to be guilty of that myself. One
advantage of answering questions rather than asking them is that I'm looking
at the problem in isolation, rather than being in amongst the trees!


"Steve" wrote in message
...
Indeed.....
It does work as you've written it- sans the sheet name.

Thank you Steve.
And as to my "overthinking" it... that's an old habit. I keep trying to
break myself of it, but I can only make small short progressional steps.
You
know-- the old 3 steps forward, and two steps back bit.

Again, thank you. This matter is solved.

"Steve Dunn" wrote:

Hmmm, I think you've been overthinking it. Another way of stating your
formula would be:

=if(a=0, b/c, if(b=0, a/d, a/d+b/c))

which simplifies to:

a/d+b/c

because if a = 0, then a/d = 0 and therefore a/d+b/c would equal 0+b/c or
just b/c. The opposite applies if b = 0, then b/c = 0, therefore
a/d+b/c
is equivalent to a/d+0. Leaving a/d+b/c to give the correct answer where
neither are 0.

So, your formula becomes:

=SUMPRODUCT(($E$4:$E$95&""=$A11&"")*($F$4:$F$95=$C 11)*($B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT(($E$97:$E$173&" "=$A11&"")*($F$97:$F$173=$C11)*($B$97:$B$173))/TRIM(LEFT($F$7,4))*$E$7

HTH
Steve D.


"Steve" wrote in message
...
Hi Steve.
Thank you for the response.

1- The &"" in the criteria source is a datatype nullifier, as it was
once
explained to me. I was having troubles with my data in the beginning,
and
after posting here back in August/Sept/November of 2006 on the topic,
Harlan
Grove and Roger Govier explained to me the various ways of doing the
sumproduct. I.e., Sumproduct(--()*()*()); Sumproduct((--)*()*()), and
the
&""
elements. For my purposes the &"" worked more consistently. I'm not
sure
why
the double -- did not work for my purposes, especially since I've used
it
more recently and it has worked.

2- My goal is to check if there are two sources, or one. Each set has
its
own ratio that occurs when I want to perform a secondary operation on
them.
As such, I have to delineate between them, then perform the secondary
operation, and add those two results together.
If there are only values in dataset A, I perform the ratio operation on
dataset A. If dataset B, perform operation on B.
So far it appears that I was able to come up with a solution, but boy
is
it
u-g-l-y.

After I'd posted this, I talked with a colleague to "clear out my
cobwebs"
and came up with the following.

=IF((SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$ F$4:$F$95=$C11)*(APN!$B$4:$B$95))0),IF((SUMPRODU CT((APN!$E$97:$E$173&""=$A11&"")*(APN!$F$97:$F$173 =$C11)*(APN!$B$97:$B$173))0),(SUMPRODUCT((APN!$E $4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B $4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7),(SUMPRODUCT((APN!$E$4:$E$ 95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B$4:$B$ 95))/TRIM(LEFT($F$6,4))*$E$6)),(SUMPRODUCT((APN!$E$97:$ E$173&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$ 97:$B$173))/TRIM(LEFT($F$7,4))*$E$7))

I had to start with a compound/nested IF, and then insert the
sumproduct
eq's accordingly.
I.e.,
IF((),IF((),(),()),())

Where
IF((TestA0),IF((TestB0),(True ResponseA + True ResponseB),(False
ResponseA)),(FalseResponseB))

While it appears to work, I'd like to reduce its complexity-- and I
don't
use the name manager because of prior issues faced with it.

Hope this helps clarify.

Thanks again for your response, and helps.

"Steve Dunn" wrote:

Hi Steve,

1. Why (APN!$E$4:$E$95&""=$A11&"") rather than (APN!$E$4:$E$95=$A11)
?

2. What kind of result do you want when both data sets have matching
values
within? Do you want the results in two seperate cells, added together
in
one cell, or does one result take priority over the other (and how is
that
priority decided)?




"Steve" wrote in message
...
Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more
compound
that
I've used before.
Thus far, I've tried using an if equation, that looks through a
dataset
with
a sumproduct, and if the dataset matches, I perform one equation,
and
if
not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that
has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.




 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.