Adding two formula's together in one cell?

Linda wrote:

I'm sorry not sure if I'm just being really dense tonight or it's the lack of sleep

Perhaps the latter.  Or perhaps it is I who does not understand what you are not getting.  Forgive me if the following completely misses the target and belabors what you already know.

Note:  Ordinarily, I might suggest that you use the Evaluate Formula feature of Formula Auditing.  But at least in XL2003 SP3, Excel bellies up for unknown reasons when using EF to step through the evaluation of this construct.

Linda wrote:

I understand everything (well sort of) up until it selects the correct fee for price related listings as in the Auction fees for All Categories!  I mean how does it know which is the correct fee?  [....] can you pretend you're explaining it to a 10yr old :)

I think you are saying that you do not understand how VLOOKUP(T10,_Settings!$G$8:$M$13,7) works.  Or are you saying you do not understand how the nested IFs even get to the VLOOKUP?

For the operation of the nested IFs, I will use the "v1" construct.  Conceptually, the "v2" SUM array formula is simply a compact way of expressing the same thing.

Part of the problem with understanding the nested IFs might be the formatting.  Does the following pseudocode make it any clearer?

IF(N10="Buy It Now",

    IF(P10="",

        0,

        IF(P10="All Categories", _Settings!$AC$10, _Settings!$AC$11))

    +IF(R10="",

        0,

        IF(R10="All Categories", _Settings!$AC$10, _Settings!$AC$11)),

//else N10="Auction"

    IF(P10="",

        0,

        IF(P10="Media Related",

            IF(T10<1, _Settings!$M$15, _Settings!$M$16),

        //else P10="All Categories"

            VLOOKUP(T10,_Settings!$G$8:$M$13,7)))

    +IF(R10="",

        0,

        IF(R10="Media Related",

            IF(T10<1, _Settings!$M$15, _Settings!$M$16),

        //else R10="Categories

            VLOOKUP(T10,_Settings!$G$8:$M$13,7)))))

Of course, //else is not really part of the Excel syntax.

Alternatively, does the following PHP pseudocode make it clear?

if (N10=="Buy It Now")

    {

    if (P10=="")

        p = 0;

    elseif (P10=="All Categories")

        p = _Settings!$AC$10;

    else  // P10=="Media Related"

        p = _Settings!$AC$11));

    if (R10=="")

        r = 0;

    elseif (R10=="All Categories")

        r = _Settings!$AC$10;

    else // R10="Media Related"

        r = _Settings!$AC$11));

    return p+r;

    }

else // N10="Auction"

    {

    if (P10=="")

        p = 0;

    elseif (P10=="Media Related")

        {

        if (T10<1)

            p = _Settings!$M$15;

        else

            p = _Settings!$M$16);

        }

    else // P10=="All Categories"

        p = VLOOKUP(T10,_Settings!$G$8:$M$13,7);

    if (R10=="")

        r = 0;

    elseif (R10=="Media Related")

        {

        if (T10<1)

            r = _Settings!$M$15;

        else

            r = _Settings!$M$16);

        }

    else // R10=="All Categories"

        r = VLOOKUP(T10,_Settings!$G$8:$M$13,7);

    return p+r;

    }

For the operation of  VLOOKUP(T10,_Settings!$G$8:$M$13,7), conceptually it works as follows.  In the _Settings worksheet, you have the following table:

          G        M

8:      0         0.00

9:      1         0.15

10:    5         0.25

11:    15       0.50

12:    30       1.00

13:    100    1.30

Imagine how you might look up the value of T10 in column G, then look across to get the corresponding value in column M.

1. If T10>=100, return 1.30
2. Elseif T10>=30, return 1.00
3. Elseif T10>=15, return 0.50
4. Elseif T10>=5, return 0.25
5. Elseif T10>=1, return 0.15
6. Elseif T10>=0, return 0.00
7. Else return #N/A error

VLOOKUP does a ">=" test here because I did not specify a 4th parameter; ergo, the 4th parameter defaults to TRUE, which means "match approximate value" instead of "exact value".  For this kind of lookup, column G must be in ascending order.  And it is.

Note:  In fact, VLOOKUP is presumed to be more efficient than that.  It probably performs a binary search [1], much like you might do if you looked up a word in the OED.

Does this help?  Or should we ask your daughter to study this, then explain it to you? :-) :-)

---

[1] I actually "proved" that VLOOKUP does indeed use a binary search when the last parameter is TRUE or omitted by testing VLOOKUP with carefully constructed out-of-order lookup tables.  Normally, in that case, the results are unreliable.  But knowing how a binary search algorithm should be implemented, I was able to control what results were returned for various lookup values.

I'm sorry not sure if I'm just being really dense tonight or it's the lack of sleep, I understand everything (well sort of) up until it selects the correct fee for price related listings as in the Auction fees for All Categories!

I mean how does it know which is the correct fee?  I'm so sorry if you're going over it lol..  can you pretend you're explaining it to a 10yr old :)

Linda

Linda wrote:

The merged columns where so I could position exactly the Totals at the top of the document. I know I'm pedantic hehehe.. I like everything to LOOK perfect! What am I like? :) 

Not I !! :-)

Seriously....  I did see that.  But since the totals at the top were aligned on the left with pairs (or quads) of columns, you should be able to get the same effect (or close enough) simply by "doubling" the width of individual columns.

The original columns widths were 6.  When I tried a column width of 12, the text was clipped on the left.  So I simply use a column width of 13; 1 character-width to account for the cell border.

Again, study my "v2" of the file that I uploaded.

PS:  Aha!  It is true that the totals on the upper right we aligned starting in column U, midway between the merged pair T:U.  I think the operative word is "close enough".  Even perfectionists like us need to choose reasonable compromise occassionally. :-)

The real point is:  this will be come a "necessary" step if you want to adopt my design for supporting more than two categories (TBD).

Will do it now.

Linda