compound if equation?

[Steve]

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((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)

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.

 

[Steve Dunn]

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((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)

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.

 

[Steve Dunn]

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 Dunn]

oops

 

[Steve]

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((SUMPRODUCT((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$173&""=$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((TestA<>0),IF((TestB<>0),(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]

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=$C11)*($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]

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]

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!