Adding two formula's together in one cell?

Anonymous
2011-11-06T19:38:01+00:00

I'm now working on a separate work book for our ebay sales, and in one of the cells that outputs the insertion fee is the following:  (Be gentle with me lol, if I've done this the wrong way or it's over winded and could have been done simpler :)  Any way it pulls the insertion fee's from another sheet so that if at a later date they put them up I can just start a new workbook and alter the fee's in that one sheet, but the problem is we sometimes list the same item in two categories so in theory our insertion fee is doubled, so to get around this I have added another column , so now have:  one column with Category One P10, and column two with Category Two R10,  in cell Z10 is the total insertion fee.  But at the moment it just shows the fee's for Category One P10how can I also use the same formula but just add them together?

=IF(AND(N10="Auction",P10="All Categories",T10>=0.01,T10<=0.99),'_Settings'!$M$8,IF(AND(N10="Auction",P10="All Categories",T10>=1,T10<=4.99),'_Settings'!$M$9,IF(AND(N10="Auction",P10="All Categories",T10>=5,T10<=14.99),'_Settings'!$M$10,IF(AND(N10="Auction",P10="All Categories",T10>=15,T10<=29.99),'_Settings'!$M$11,IF(AND(N10="Auction",P10="All Categories",T10>=30,T10<=99.99),'_Settings'!$M$12,IF(AND(N10="Auction",P10="All Categories",T10>=10),'_Settings'!$M$13,IF(AND(N10="Auction",P10="Media Related",T10>=0.01,T10<=0.99),'_Settings'!$M$15,IF(AND(N10="Auction",P10="Media Related",T10>=1),'_Settings'!$M$16,IF(AND(N10="Buy It Now",P10="All Categories"),'_Settings'!$AC$10,IF(AND(N10="Buy It Now",P10="Media Related"),'_Settings'!$AC$11,""))))))))))

Many many thanks in advance for any help, I'm very grateful.

Linda

PS the work book is so that I can see what fee's we are paying and our profit after fees, and so adjust accordingly.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-11-07T10:01:34+00:00

Linda wrote:

Cheers Joe,  here's the linkhttp://www.lindaoraya.talktalk.net/eBay Sales WorkBook.zip

Thank you Joe, still haven't got to bed as yet.  Daughter is ill so, looks like it's going to be a long night :(

Hope you and your daughter managed to get some sleep.

In the meantime, I had a chance to review your past explanations and look at your uploaded file.  I realized that I had misunderstood some of the intended design, mostly due to my ignorance of eBay.

I have uploaded a file with some changes (click here).  That file is an Excel 2003 file.

The primary change is in column Z.  The formula is a correction to my previous efforts, to wit:

=IF(OR(N10="",AND(P10="",R10=""),T10=""), "",

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

IF(P10="",0,IF(P10="Media Related",

IF(T10<1,_Settings!$M$15,_Settings!$M$16),VLOOKUP(T10,_Settings!$G$8:$M$13,7)))

+IF(R10="",0,IF(R10="Media Related",

IF(T10<1,_Settings!$M$15,_Settings!$M$16),VLOOKUP(T10,_Settings!$G$8:$M$13,7)))))

A significant change is:  you do not need the table I suggested in X1:Y6.  Your existing table in _Settings!$G$8:$M$13 will suffice.

I suspect that the values in P10 and R10 are mutually exclusive if both are specified.  That is, if P10 is "All Categories", I suspect that R10 is "Media Related" or empty; and vice versa.  I also suspect that P10 will never be empty when R10 is non-empty.  That is, you always use Category 1 first and only optionally also use Category 2.

Those observations might lead to some simplification of the formula.  But I suspect only minor simplifications; for example, we probably do not need IF(P10="",0.  But we still need IF(R10="",0.

If you confirm the observations above, I could look to see how much we can simplify the formula, if you wish.  But I think it works well as is, and I suspect it is not overly complicated.

Note that I also made a number of other minor improvements in some unrelated formulas.

  1. I simplified the SUM formulas in C4 and G4.  Note that SUM can have at least as many as 30 parameters (XL2003 limit).  XL2007 might permit more.  I don't know.
  2. I simplified the IF formulas in columns AP, AR and AZ.  Let me know if you have any questions.  In general, it is not necessary to test both of two complementary conditions.  The change in column AZ also includes an arithmetic simplification.
  3. I changed the lower limits in G8 and G15 in the _Settings worksheet from 0.01 to 0.00.  That also required a format change for 0 values (3rd field).  Although column T in the "eBay Sales" worksheet might never be zero, it is better to have 0.00 in the lookup table for technical reasons.  "Defensive programming".  Nevertheless, if you insist on 0.01, it probably will work that way.

Hope that helps.

[EDIT] PS:  I forgot to mention a change that I did not make.  I noticed some content in T1011:U1017 in the "eBay Sales" worksheet.  I'm not sure what they are doing there; I did not find any formulas dependent on that data.  I suspect it should be removed.

Was this answer helpful?

0 comments

43 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-11-07T18:43:50+00:00

    Linda wrote (presumably about highlighting quoted text):

    OMG, they really like to complicate matters here at Microsoft answers lol..  I don't know why they don't allow basic html editing.  It would make life so much easier!  They can always set it to allow only certain html. 

    They should hire you.  But "they" is a third party, not Microsoft.  BTW, they did indeed used to allow HTML editing.  They purposely took away that feature.  I don' t know why.

    Hmm....  I'm not very knowledgeable about HTML, but I wonder if they removed the feature because the supported HTML varies from browser to browser, and they want to ensure compatibility across platforms.  Just a WAG.  Probably wrong.  When I tested the feature in the past, I learned that the server changed the HTML tags after submittal, presumably following a single standard anyway.  In any case, the point is:  perhaps they are trying to avoid some compatibility issues, je ne sais quoi.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-11-07T18:42:50+00:00

     Although it is unnecessary with just the two options now, I could demonstrate the alternative construct (and others might have better suggestions) so that you have it available for future use.  Interested?

    Oh yes pleas, very interested.  Right I'll be back in half an hour I'm just going to have a read of the above, compare to the excel sheet and see what is what.

    Cheers Joe.

    Linda

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-11-07T18:34:44+00:00

    Linda wrote:

    Wow that is so much easier! Though have to be honest don't understand it, but it works perfectly.

    [....]

    Ok I've just got in so I'm going to go make a cuppa and then look at how you did it. I hope you don't mind if I come back later and ask a few questions, so that I can understand how the formula works for another time?

    Feel free to ask any questions.  But I hope the following annotation ("***" lines) helps.

    =IF(OR(N10="",AND(P10="",R10=""),T10=""), "",

    *** return the null string ("") until N10, T10 and

    *** one or both of P10 and R10 appears nonblank

    *** if N10 is "Buy It Now", do the following....

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

    *** if P10 appears nonblank and equals "All Categories", return AC10.

    *** otherwise, P10 must be "Media Related" since you have a pulldown

    *** menu with only those two choices, so return AC11.

    *** similarly, add AC10 or AC11 based on R10 if R10 appears nonblank.

    *** otherwise, N10 must be "Auction" since you have a pulldown menu

    *** with only those two choices.  in that case, do the following....

    IF(P10="",0,IF(P10="Media Related",

    IF(T10<1,_Settings!$M$15,_Settings!$M$16),VLOOKUP(T10,_Settings!$G$8:$M$13,7)))

    +IF(R10="",0,IF(R10="Media Related",

    IF(T10<1,_Settings!$M$15,_Settings!$M$16),VLOOKUP(T10,_Settings!$G$8:$M$13,7)))))

    *** if P10 appears nonblank and equals "Media Related", return M15 or M16

    *** based on T10.

    *** otherwise, P10 must be "All Categories" because of the pulldown

    *** menu choices, so return a value from M8:M13 based on matching

    *** T10 to largest value in G8:G13 less than or equal to T10.

    *** similarly, add M15 or M16 or a value from M8:M13 based R10 and

    *** T10 if R10 appears nonblank.

    Linda wrote:

    P10 will never be left empty it will as you say be used first, with R10 as a second option

    So we could simplify the formula at least as follows:

    =IF(OR(N10="",AND(P10="",R10=""),T10=""), "",

    IF(N10="Buy It Now",

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

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

    IF(P10="Media Related", IF(T10<1,_Settings!$M$15,_Settings!$M$16),

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

    +IF(R10="",0,IF(R10="Media Related", IF(T10<1,_Settings!$M$15,_Settings!$M$16),

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

    Linda wrote:

    it is possible to have two listings in the same category, as there are sub categories. So we could list in two sub categories of All Categories or Two of Media, as well as the obvious one of each.

    If you had to the N10 or P10/R10 pulldown menus, the logic above will need to be changed.  In fact, at that point, it might be wise to use a completely different construct -- perhaps a combination of CHOOSE and LOOKUP or VLOOKUP.

    Although it is unnecessary with just the two options now, I could demonstrate the alternative construct (and others might have better suggestions) so that you have it available for future use.  Interested?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-11-07T18:33:08+00:00

    OMG, they really like to complicate matters here at Microsoft answers lol..  I don't know why they don't allow basic html editing.  It would make life so much easier!  They can always set it to allow only certain html.  Thank's for sharing though, will have a go at this later.

    Right..  will be back in a bit, still looking at your formula.  Slow tonight, after being up all night then working all day, has created the brain to go on a go slow day lol..  Back a bit later with some questions!  :)

    Linda

    Was this answer helpful?

    0 comments