![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
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.