Adding two formula's together in one cell?

Anonymous

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.

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

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.

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.
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.
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.

0 comments

43 additional answers

Anonymous

2011-11-07T19:55:51+00:00

[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.

Ah just realised this is the data for the drop down lists, usually I have it coloured the same colour as the page. (so you don't see it) I did the drop down lists to make sure no one miss spelt words which would have returned false. So hence the items that HAD to be a certain word I did the drop down list.

Still reading, daughter keeps interrupting me lol. kids, so have to keep stopping : D Back in a few.

Linda
Was this answer helpful?

0 comments No comments
Anonymous

2011-11-07T20:57:18+00:00

No, tbh it's really only IE that doesn't adhere to protocol, hence why most web designers have to write work arounds in their CSS to allow for IE going into quirks mode.

Which is why (in newer versions) you see the compatibility mode option in IE, the little icon next to the address bar that looks like a piece of paper torn in two lol... If only they adhered to protocol they wouldn't need to have that! IMHO Firefox is the best, as it's open source and usually any holes that are found are plugged pretty quickly. As there are so many thousands of people around the world working to keep it secure. Were as MS are renowned for not sharing their source, hence why so many viruses are written and aimed at them lol.. So in short I think it's just convenience as in less hassle to them..

It can be dangerous to allow html, as some people abuse it by adding scripts to spread viruses etc. But if it's been coded correctly it's not dangerous.

Yes I use a php script to change code entered into the sites I design. Some times it's just so it doesn't brake your code, and other times it's for aesthetic reasons.

Linda
Was this answer helpful?

0 comments No comments
Anonymous

2011-11-07T21:00:11+00:00

Ok Joe the thing I can't work out how you did, and don't understand is how it knows which price band is used when listing price is the factor?

Maybe it's just my brain tonight lol..
Was this answer helpful?

0 comments No comments
Anonymous

2011-11-07T21:05:54+00:00

Linda wrote regarding data in 'eBay Sales'!T1011:U1017:

Ah just realised this is the data for the drop down lists, usually I have it coloured the same colour as the page. (so you don't see it) I did the drop down lists to make sure no one miss spelt words which would have returned false.

Oh, sure. I should have realized. I would think that when I do "trace dependents", it would show a line from that data to the cells with dropdown menus. But not so in XL2003. That's why I missed it. Excuses, excuses, excuses. ;-)

Linda wrote:

Still reading, daughter keeps interrupting me lol. kids, so have to keep stopping : D

I am in awe of people like you, usually women, who have so many different jobs. You have your day job, you're eBay work, you're a mother, and you're probably the housekeeper. Wow!

I used to work 60-80 hours 6-7 days a week for decades, juggling several major projects and wearing 2 or 3 different hats. But that really was just one job -- one area of focus. Much easier, I think.

Linda wrote elsewhere:

[joeu2004 wrote:]

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.

In preparation for this redesign, I came up with a simpler(?) formula that might be worth doing even for just two categories. See the uploaded "eBay Sales v2.xls" file (click here) for details.

Basically, replace the formula that used to be in column Z with the following array formula [*]:

=IF((N10="")+(COUNTBLANK(P10:Q10)=2)+(R10="")>0, "",

SUM(IF(N10="Buy It Now",

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

IF(P10:Q10="",0,IF(P10:Q10="Media Related", IF(R10<1,_Settings!$M$15,_Settings!$M$16),

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

[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter. Excel will display an array formula surrounded by curly braces in the Formula Bar, i.e. {=formula}. You cannot type the curly braces yourself. If you make a mistake, select the cell, press F2 and edit, then press ctrl+shift+Enter.

In order for the formula to work, the merged columns N:O, P:Q, R:S and T:U must be unmerged. For aesthetic reasons (the right side of the title line), other columns also must be unmerged. I did this in the uploaded example file.

This causes a renaming of some cells referenced in the formula, namely: R10 becomes Q10 (category 2), and T10 becomes R10 (item price), and Z10 becomes U10 (insertion fees).

I also reworked the block of data on the right side of the title line for aesthetic reasons.

In fact, I don't know why you used merged cells in the first place. I would recommend that you unmerge the remaining merged columns.

The steps are relatively easy. For select pair of merge columns; unmerge by deselecting Merge in the Alignment tab under Format Cells; select the lefthand column and change the Column Width to 13; then select the righthand column, and delete it.

To change the Column Width, select the column, then right-click and click on Column Width.

Apparently, you do not need to unmerged columns in the _Settings worksheet; only in the "eBay Sales" worksheet. However, you might consider unmerging columns in the _Settings worksheet anyway, just to make things consistent, "clean" and easier for making future changes.

Explanation of the array formula above....

=IF((N10="")+(COUNTBLANK(P10:Q10)=2)+(R10="")>0, "",

*** return the null string unless N10, R10 and one or both of P10 and Q10

*** are nonblank.

*** OR() and AND() functions do not have their intended effect in array formulas.

*** instead, we use addition (+) to have the effect of OR() and multiplication (*)

*** to effect AND().

*** the test >0 is part of the OR() functionality because each comparison

*** returns 1 or 0. we want to proceed with SUM only if all three comparisons

*** returns 0.

SUM(IF(N10="Buy It Now",

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

IF(P10:Q10="",0,IF(P10:Q10="Media Related", IF(R10<1,_Settings!$M$15,_Settings!$M$16),

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

*** for each of P10 and Q10, evaluate the logic explained previously,

*** summing the two results
Was this answer helpful?

0 comments No comments