Adding two formula's together in one cell?

Question

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.

Answer 1

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.

Answer 2

Linda wrote:

because we sometimes list in Two Categories, I have had to add another identical column Called "Category Two" R10  with the same format as Category One, as in a drop down list with the two options "All Categories" and "Media" to allow for when we list in Two Categories (As the fees double when we do this) 

But I now can't figure out how to have two copies of the same formula as above in the Z10 Total Insertion Feescell, and have the outcome of each added together to give the total eBay Insertion fee for when we list in two categories.

I'm still not sure I fully understand what you mean by "two copies of the same formula".  But dovetailing my recommended reformulation for P10, is this what you have in mind:

=IF(AND(N10="Buy It Now",P10="All Categories"),'_Settings'!$AC$10,

IF(AND(N10="Buy It Now",P10="Media Related"),'_Settings'!$AC$11,

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

IF(AND(N10="Auction",P10="All Categories"), VLOOKUP(T10,$X$1:$Y$6,2), ""))))

+

IF(AND(N10="Buy It Now",R10="All Categories"),'_Settings'!$AC$10,

IF(AND(N10="Buy It Now",R10="Media Related"),'_Settings'!$AC$11,

IF(AND(N10="Auction",R10="Media Related"), IF(T10<1,'_Settings'!$M$15,'_Settings'!$M$16),

IF(AND(N10="Auction",R10="All Categories"), VLOOKUP(T10,$X$1:$Y$6,2), ""))))

As you can see, I simply duplicated the P10 formula and literally added the copy, substituting R10 for P10.

But as I recommended previously (our posting crossed in time), I would prefer to break up such a long formula into 2 cells:  one for the R10 expression, and one for the addition of the two formulas.

That makes it easier to maintain in the future, IMHO.  And it avoids the risk of making the (combined) formula too long, exceeding the limit for your version of Excel.

Answer 3

Linda wrote:

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:

[....]

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?

I believe you have provided insufficient information to answer your questions, namely:  how to include category two from R10.  It is unclear how R10 should factor into the selection criteria.  Did you intend to simply duplicate the selection criteria, substituting R10 for P10?

In any case, why are you intent on combining two complicated formulas?  It might be easier to maintain if you put each formula, one for P10 and one for R10, into separate cells (columns), then "combine" their effect (in some unspecified manner) in a third cell (column).

Regardless, it might help to start by simplifying your formula for P10.  Try the following:

=IF(AND(N10="Buy It Now",P10="All Categories"),'_Settings'!$AC$10,

IF(AND(N10="Buy It Now",P10="Media Related"),'_Settings'!$AC$11,

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

IF(AND(N10="Auction",P10="All Categories"), VLOOKUP(T10,$X$1:$Y$6,2), ""))))

where X1:Y6 is:

0         '_Settings'!$M$8

1         '_Settings'!$M$9

5         '_Settings'!$M$10

15      '_Settings'!$M$11

30      '_Settings'!$M$12

100    '_Settings'!$M$13

Notes:

1. I assume you have a typo where you wrote IF(AND(N10="Auction",P10="All Categories",T10>=10).  I assume that should be T10>=100.
2. I assume T10<0 is never true.  Correct?
3. You could use '_Settings'!M8 (relative reference) in Y1.  Thus, you can create Y2:Y6 simply by copying the formula down.

Answer 4

Hi Pete,

Thank you for your reply!

I need to add another exact copy of the above in the same cell, it just needs to add the two results together.  

Basically the above looks in N10 (Selling Method) in this case there are two options from the drop down list  "Auction" or "Buy It Now"  then it looks into cell P10 Category One(which contains the Categories you can list under on eBay) to see which option has be selected there, again two options available  "All Categories" or "Media", then depending on the combo of both, it pulls the relevant eBay fees from the _Settings sheet, and is displayed in Z10 Total Insertion Fees

BUT..  there's always a but isn't there lol..  because we sometimes list in Two Categories, I have had to add another identical column Called "Category Two" R10  with the same format as Category One, as in a drop down list with the two options "All Categories" and "Media" to allow for when we list in Two Categories (As the fees double when we do this)

But I now can't figure out how to have two copies of the same formula as above in the Z10 Total Insertion Feescell, and have the outcome of each added together to give the total eBay Insertion fee for when we list in two categories.

I hope this is making sense?

Answer 5

I put your formula into Word so that I can see its structure more clearly by introducing some hard-returns before each IF, as follows:

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

It seems to me that you should be using VLOOKUP to replace all those multiple IF statements. What values can N10, and P10 take? And what about the R10 that you mention in your post? What is the layout of your _Settings sheet?

Hope this helps.

Pete