Having used Excel for many years, and having posted questions on MrExcel for the last three years, I thought it would be fun to think of reasons why I have (and haven’t) posted, and to offer a semi-scientific-organized list of these reasons. (So, what comes below reflects some of my thoughts and is not a typical question-based post - please hit the “Exit Button” now if you’re not interested!)
First, come the times that I don't need to post. This includes times when I have made a stupid and instantly recognizable spreadsheet error. For example, my boss calls and says: “Accounting was on the phone and wondered why the sales number for Product A in Latin America doesn’t tie with the inventory reduction amount”. So I look, and it’s because the formulas in Column C12 to C24 should refer to F12:F24, not E12:E24. And, I remember that I was rushing on that part of the model and copied from another table and forgot about the extra column I had inserted in the new table, and now, six months later, this is the first time Latin America has had sales of Product A...
I've only had a few of these, but they're memorable! (And are they all due to faulty memory?) Anyway, no need to paste to MrExcel for these!
The next type of issues are the ones that I probably should have avoided, but are not formula errors. For example, the same boss says, “All the Group B products should be classified as belonging to Region A, but Product 4 is shown as Region C”. Well, I got the data from Susan and she pulled a wrong number... But, OK, I could have put a consistency check in my workbook to catch this. This is more of a data issue than an Excel issue, and nothing to post about.
Now we get to MrExcel-worthy issues.
To start with are the “How can I do it better?” questions. I find these are often around reducing the number of columns. For example, before I knew about SUMPRODUCT, I could always get to the right results with an extra column or two, but SUMPRODUCT saves the space (and is it quicker too?).
Other areas of help I have received information on are such things as Array Formulas and SUM(OFFSET())s etc. – all helping me to reduce columns.
A brilliant example of is the “Lookup Last” help I received from Aladin, Domenic, and others by using:
(LOOKUP(2,1/(A2:A7="A"),B2:B7)
Another group of problems is not about how to do something better, but rather how to do it at all (other than manually). For example, today I posted to find out how to strip out “*” (asterisk) characters from a text string – and got the answer to use SUBSTITUTE(A1,”*”,””).
Next come Macros - and this is probably where I have had the most help from the Message Board. For example, I tried and failed with:
Worksheets("Cost Reports").ChartObjects("Stacked").SetSourceData Source:=Sheets("Cost Reports").Range("Cost1Data")
Pgc01 corrected it with:
Worksheets("Cost Reports").ChartObjects("Stacked").Chart.SetSourceData Source:=Sheets("Cost Reports").Range("Cost1Data")
I sometimes wonder where experts learn these things. Are there certain books, training courses, schools, or secret societies that pass the “Konwledge” on from Master to Master?!!
And by the way, there’s always the question of when to post a question. Have I really exhausted my search in Excel Help? Have I really done a good search of previous MrExcel posts? Sometimes laziness takes over and the post goes straight in.
The last group of problems are around Excel itself doing something strange. At one extreme would be the notorious (and practically harmless) “multiplying-77.1*850, -which-should-give-you-65,535,-was-actually-displaying-100,000” error.
Others are “softer” issues, such as Chart Titles not re-centering as you would expect. Here, it’s good to post to MrExcel to see if other people have experienced these, and to discover that you’re not going mad!
Anyway, Excel is a brilliant tool for mankind, the problems are few, and I will keep on posting!
MikeG
First, come the times that I don't need to post. This includes times when I have made a stupid and instantly recognizable spreadsheet error. For example, my boss calls and says: “Accounting was on the phone and wondered why the sales number for Product A in Latin America doesn’t tie with the inventory reduction amount”. So I look, and it’s because the formulas in Column C12 to C24 should refer to F12:F24, not E12:E24. And, I remember that I was rushing on that part of the model and copied from another table and forgot about the extra column I had inserted in the new table, and now, six months later, this is the first time Latin America has had sales of Product A...
I've only had a few of these, but they're memorable! (And are they all due to faulty memory?) Anyway, no need to paste to MrExcel for these!
The next type of issues are the ones that I probably should have avoided, but are not formula errors. For example, the same boss says, “All the Group B products should be classified as belonging to Region A, but Product 4 is shown as Region C”. Well, I got the data from Susan and she pulled a wrong number... But, OK, I could have put a consistency check in my workbook to catch this. This is more of a data issue than an Excel issue, and nothing to post about.
Now we get to MrExcel-worthy issues.
To start with are the “How can I do it better?” questions. I find these are often around reducing the number of columns. For example, before I knew about SUMPRODUCT, I could always get to the right results with an extra column or two, but SUMPRODUCT saves the space (and is it quicker too?).
Other areas of help I have received information on are such things as Array Formulas and SUM(OFFSET())s etc. – all helping me to reduce columns.
A brilliant example of is the “Lookup Last” help I received from Aladin, Domenic, and others by using:
(LOOKUP(2,1/(A2:A7="A"),B2:B7)
Another group of problems is not about how to do something better, but rather how to do it at all (other than manually). For example, today I posted to find out how to strip out “*” (asterisk) characters from a text string – and got the answer to use SUBSTITUTE(A1,”*”,””).
Next come Macros - and this is probably where I have had the most help from the Message Board. For example, I tried and failed with:
Worksheets("Cost Reports").ChartObjects("Stacked").SetSourceData Source:=Sheets("Cost Reports").Range("Cost1Data")
Pgc01 corrected it with:
Worksheets("Cost Reports").ChartObjects("Stacked").Chart.SetSourceData Source:=Sheets("Cost Reports").Range("Cost1Data")
I sometimes wonder where experts learn these things. Are there certain books, training courses, schools, or secret societies that pass the “Konwledge” on from Master to Master?!!
And by the way, there’s always the question of when to post a question. Have I really exhausted my search in Excel Help? Have I really done a good search of previous MrExcel posts? Sometimes laziness takes over and the post goes straight in.
The last group of problems are around Excel itself doing something strange. At one extreme would be the notorious (and practically harmless) “multiplying-77.1*850, -which-should-give-you-65,535,-was-actually-displaying-100,000” error.
Others are “softer” issues, such as Chart Titles not re-centering as you would expect. Here, it’s good to post to MrExcel to see if other people have experienced these, and to discover that you’re not going mad!
Anyway, Excel is a brilliant tool for mankind, the problems are few, and I will keep on posting!
MikeG
