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" <(E-Mail Removed)> wrote in message
news:AE79BCCA-5C8D-4B0B-888A-(E-Mail Removed)...
> 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=$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" <(E-Mail Removed)> wrote in message
>> news:F15BCABA-B1BD-4384-ADA8-(E-Mail Removed)...
>> > 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" 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" <(E-Mail Removed)> wrote in message
>> >> news:51329108-4F1C-4BD5-A160-(E-Mail Removed)...
>> >> > 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.
>> >> >
>> >>
>>
Similar Threads
