News:

Thank you for your patience during the Forum downtime while we upgraded the software. Welcome back and see this thread for some new features and other changes to the forum.

Main Menu

Microsoft Excel

Started by kphoger, July 15, 2020, 03:44:24 PM

Previous topic - Next topic

kalvado

Quote from: jeffandnicole on July 15, 2020, 09:15:44 PM
One thing I learned about spreadsheets, especially complicated, formula-intense spreadsheets: try not to make any changes that will take effect on Friday, or before taking time off. If there's an error that's not discovered until late in the day, the effects can last all weekend, or you have to deal with it on your time off.

In the example kalvado posted above, those issues can be incredibly hard to detect. But it also speaks to the limitations many people have with Excel. It's like driving a car. Some people may say they're good drivers, but are they?  Some people may say they're good Excel users, but when you find out the basic sum formula confuses them, it's too late.
forgetting a factor of 2 somewhere is more on a misprint side of things. And disadvantage of excel here is lack of ability to overview the formula and setup proper unit tests. If you use 6-line formulas in your spreadsheets, question is not IF you have mistakes in there, but HOW MANY you have.
ANother quote from Humble Pi
QuoteIn the aftermath of the Enron scandal of 2001, the Federal Energy Regulatory Commission published the results of its investigation into the corporation and the evidence behind it — which included around 0.5 million emails [....] a lot of spreadsheets as attachments.
Hermans and her colleagues searched through the email archive and were able to assemble a corpus of 15,770 real-world spreadsheets
[...]
Hermans found that 2,205 spreadsheets had one or more Excel error messages. Which means that around 24 per cent of all formula-containing spreadsheets contained an error. And those errors had company: the error-prone spreadsheets had an average of 585.5 mistakes each.  An astonishing 755 spreadsheets had over a hundred errors, and one spreadsheet took first place with 83,273 errors.
[...]
But this is only a tiny subset of mistakes in spreadsheets: the obvious ones. Many more formula errors will be unaccounted for



GaryV

To expand on my comments, I definitely use Excel for tracking things.  Sure, I could use some project manager software, but for what I need to do that would be overkill.

For example, today I had to evaluate a list of jobs in our database, find out if they were active or scheduled, who is responsible for maintaining them, and find whether they did some specific activity on a specific server for which we will need to make changes.  A colleague gave me a list in Excel, and I filled in the details about the jobs.

My wife uses a spreadsheet to calculate how much she is spending on buying music for church - song titles, publisher info, number of copies needed, price.  Then calculate copies*price, apply any discount, and come up with a grand total.  Sure, that's a great use for Excel.

But please don't make me try to consume Excel data into a database.  The database has strictly defined columns.  You can put anything you want into a spreadsheet cell.  I had to attempt to use one spreadsheet that had 3 different dates in one cell, and then determine how those dates were being used.  We finally gave up on it.

formulanone

#27
I use Excel a bunch, though not as seriously as before. Back around 1997-98, I taught myself how to use it by using a lot of pointless data. I rarely need as many formulas as before, but it still comes in handy for a lot of different things and I sometimes enjoy pointlessly formatting the heck out of a spreadsheet to make look just right.

I think it's one of the few Microsoft* products that's intuitive enough to use easily, or with complexity, because it usually tells you when and what mistake you've made. Contrast that with Word, which still has a mind of its own, requiring too many manual set-up restraints just to achieve a lot of things which could be easily performed in good 'ol Wordpad. I find myself typing or copying a lot more into Notepad and Wordpad and then copying it into Word for formatting to get around of its stubborn difficulties.

* copied Lotus 1-2-3

kphoger

Quote from: Max Rockatansky on July 15, 2020, 06:02:50 PM
I spend most of my time picking apart other people's Excel documents for the data that I want which fortunately is incredibly easy to do.

This is primarily what I do at work.  I hardly ever build an Excel file from the ground up, except for small personal endeavors.  For work, I get daily and weekly Excel reports from our MSO, and manipulate those files for my purposes.
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

RobbieL2415

My Office hierarchy:

Outlook -> Word -> Access -> Excel -> Powerpoint -> Publisher

jeffandnicole

Quote from: kalvado on July 16, 2020, 08:32:32 AM
Quote from: jeffandnicole on July 15, 2020, 09:15:44 PM
One thing I learned about spreadsheets, especially complicated, formula-intense spreadsheets: try not to make any changes that will take effect on Friday, or before taking time off. If there's an error that's not discovered until late in the day, the effects can last all weekend, or you have to deal with it on your time off.

In the example kalvado posted above, those issues can be incredibly hard to detect. But it also speaks to the limitations many people have with Excel. It's like driving a car. Some people may say they're good drivers, but are they?  Some people may say they're good Excel users, but when you find out the basic sum formula confuses them, it's too late.
forgetting a factor of 2 somewhere is more on a misprint side of things. And disadvantage of excel here is lack of ability to overview the formula and setup proper unit tests. If you use 6-line formulas in your spreadsheets, question is not IF you have mistakes in there, but HOW MANY you have.
ANother quote from Humble Pi
QuoteIn the aftermath of the Enron scandal of 2001, the Federal Energy Regulatory Commission published the results of its investigation into the corporation and the evidence behind it — which included around 0.5 million emails [....] a lot of spreadsheets as attachments.
Hermans and her colleagues searched through the email archive and were able to assemble a corpus of 15,770 real-world spreadsheets
[...]
Hermans found that 2,205 spreadsheets had one or more Excel error messages. Which means that around 24 per cent of all formula-containing spreadsheets contained an error. And those errors had company: the error-prone spreadsheets had an average of 585.5 mistakes each.  An astonishing 755 spreadsheets had over a hundred errors, and one spreadsheet took first place with 83,273 errors.
[...]
But this is only a tiny subset of mistakes in spreadsheets: the obvious ones. Many more formula errors will be unaccounted for


(apologies...this is gonna be long!)

I, of course, take exception to long formulas are prone to errors.  When creating large spreadsheets that are data intense, formulas can be written 2 ways: Long, which often includes multiple imbedded formulas, or short, but more helper cells, rows and columns are needed.  Both strategies work based on the level of the user and what info you want to see along the way.  My various spreadsheets use both options. 

I can't comment about the Enron quote you cited because I don't know anything about it.  But you can imagine the data that comes into play if a spreadsheet has over 83,000 errors.  A lot of it comes down to testing, as you hinted.  When I create my large spreadsheets, I know the results I want.  If there's an error, it's usually clear cut...but not always.  However, an error can happen within that long formula just as easily as it could happen in the original data, a simple formula, or a helper cell.  I've seen errors occur as simple as this:  In cell B2, the formula is =A1.  Except, it should've been =A2.  Copy this down 100,000 rows, and you have 100,000 errors. 

And with testing, comes more testing.  Get the answer right once, and you got one right answer.  One error didn't reveal itself until a Monday, and this goes back to what I said earlier about Fridays and taking a day off:  The error happened on a Friday.  I wasn't in work that day. Mid-morning that Monday, someone from another department was confused why their activity on Friday wasn't recorded.  I took a quick glance and saw we received their data.  Then I took a look at another sheet and saw the numbers were wrong.  I realized quickly what happened: When we enter negative figures into a field, the result is a certain dataset of accounts are used.  When positive numbers are entered, which in this case only happens on Friday, I would get a different set of accounts.  But because of a change I did earlier in the week within the workbook, the change caused something else to change within the workbook which I wasn't expecting...and the error happened. 

So, for a few days the result I predicted would happen did.  On that Friday, it wasn't.  And the error lasted all weekend long, rather than being caught by me the same day it happened if I was in, or the next day, if it happened to occur on another day of the week.

To relate this to the Enron issue...which could have happened is someone wrote the formulas.  They looked fine, and may have even resulted in correct answers....that day.  But when more info was compiled, and without continuous secondary checks, the data may have resulted in incorrect answers that went undetected for a long time.

Another issue is copying and cutting.  For what we do, with the formulas we utilize, I try to get my team to copy, paste, then delete the original data, rather than cut and paste.  The formulas looking at the specific cells can change dramatically when cutting.  This happens more so when the first or last cell in a formula is affected.

And, with apologies to Webny, I think too many people take a class in Excel, then believe they are sharper than most.  Excel is a huge program, with thousands and thousands of formulas.  A semester-long class will give someone knowledge of a few formulas, builds pivot tables, create a graph (bar AND pie), and learn how to widen margins.  In my Division of 60, I'm one of 3 that are regarded as having above average Excel skills.  And I probably know a few dozen formula/function types.  However, what I also excel in (get it...excel, Excel...lol) is that I can look up other formulas and understand how to use them.  And if I need help, there's some websites that will show you examples for almost anything you need, and if they don't, there are websites forums that are great to seek out answers.  Mr. Excel is one such website.  The people in there know their shit better than everyone on this forum combined about Excel. 

And even of us 3 that know Excel well, none of us use it the same way.  Take the Copy function.  I like to right click, Copy.  Another may use the ribbon and go to Home, then Copy.  And the other person uses Control-C.  None of us are wrong.  It's just personal preference. 

Here's a formula I use in a spreadsheet, and it's a fun one because of the data that is revealed

=if(iferror(index('Schedule'!A:A,match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),"")<$H$2,iferror (index('Schedule'!A:A,Match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),""),"")

It's hard to understand without the rest of the data shown, but the result is it's looking at a different sheet, and if we need more than a certain amount of money over and above what has been placed in other cells, then the result will be a date of when more money is needed. The dates are always listed in order, up to a specific number of dates in the future, which is also a separate input field.

But the point of showing that:  You ain't gonna learn this in a semester course if that course is also showing you how to use a bolded boarder.  This is gonna be learned by continuing to expand your Excel horizons.  And if you like Excel for the basic stuff, find reasons to learn more about Excel.

kphoger

Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
A semester-long class will give someone knowledge of a few formulas, builds pivot tables, create a graph (bar AND pie), and learn how to widen margins.  In my Division of 60, I'm one of 3 that are regarded as having above average Excel skills.  And I probably know a few dozen formula/function types.

And I once took a half-day class on Access that was offered at the company I worked for.  After that class, I knew just enough to have an idea of what Access was capable of, but also to know that I wasn't really cut out to go any deeper.  I then developed a plan for a database I wanted to use at work, but I pawned it off on a co-worker who designed Access databases as a hobby.  Within a few weeks, he handed me the result of his efforts.

Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
Here's a formula I use in a spreadsheet, and it's a fun one because of the data that is revealed

=if(iferror(index('Schedule'!A:A,match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),"")<$H$2,iferror (index('Schedule'!A:A,Match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),""),"")

It's hard to understand without the rest of the data shown, but the result is it's looking at a different sheet, and if we need more than a certain amount of money over and above what has been placed in other cells, then the result will be a date of when more money is needed. The dates are always listed in order, up to a specific number of dates in the future, which is also a separate input field.

But the point of showing that:  You ain't gonna learn this in a semester course if that course is also showing you how to use a bolded boarder.  This is gonna be learned by continuing to expand your Excel horizons.  And if you like Excel for the basic stuff, find reasons to learn more about Excel.

Examples of formulas I use at my job:

=IF(AND(B93=B92,C93=C92),"DUPLICATE","TRUCKROLL")

=VLOOKUP(A26,TechFiles!$A$2:$B$94,2,FALSE)

That's really as complicated as I get.  But, six years ago, I didn't even have a clue how to do either one of those.

Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
This is gonna be learned by continuing to expand your Excel horizons.  And if you like Excel for the basic stuff, find reasons to learn more about Excel.

Absolutely.

The only reason I learned how to do a pivot table is that fundamental changes happened to one aspect of my job, and someone at work showed me how to create one.  Now that I've poked around and figured out how they actually work, I use pivot tables all the time.
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

CtrlAltDel

Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
Excel is a huge program, with thousands and thousands of formulas.

This comment piqued my curiosity, and so I dug around a little bit, finding this list, a quick count of which (I used Excel) shows that there are about 492 different Excel functions.
Interstates clinched: 4, 57, 275 (IN-KY-OH), 465 (IN), 640 (TN), 985
State Interstates clinched: I-26 (TN), I-75 (GA), I-75 (KY), I-75 (TN), I-81 (WV), I-95 (NH)

Scott5114

I would submit that if you're using the IF() function you should probably be writing an actual program to handle the data instead. Conditional logic gets complicated enough when you're using tools made for it; cramming it into a spreadsheet cell is just tempting fate.
uncontrollable freak sardine salad chef

vdeane

I use if a lot with conditional formatting.  It's good for seeing the status of a project at a glance.
Please note: All comments here represent my own personal opinion and do not reflect the official position of NYSDOT or its affiliates.

Scott5114

Conditional formatting is one thing (if it produces the wrong result, it should be obvious). I was more referring to actual data synthesis using IF(), especially nested IF()s, since the syntax quickly becomes write-only and a real pain to debug.
uncontrollable freak sardine salad chef

frankenroad

I use Excel a lot at work and at home.

At work, mostly for tracking daily job activities and purchases, especially items we have used for jobs but have not been invoiced for yet.  For this, it meets our needs better than QuickBooks, which we also use.

At home, I use it for some of my finances.   I built a pretty sophisticated spreadsheet for projecting my income/expenses into retirement.  I can plug in different retirement dates, and have it recalculate income, SS income, etc.

I use pivot tables a lot.  My other favorite tools are CONCATENATE and VLOOKUP.
2di's clinched: 44, 66, 68, 71, 72, 74, 78, 83, 84(east), 86(east), 88(east), 96

Highways I've lived on M-43, M-185, US-127

kphoger

Quote from: frankenroad on July 17, 2020, 03:36:39 PM
CONCATENATE

I didn't know about that one.  What is the advantage to using it?  That is, what is the advantage of this...


=CONCATENATE(C5, " ", D5)


...over this...


=C5&" "&D5


?

It seems like a bunch of extra work for the same result.  Is there something I'm missing?
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

hotdogPi

Do they perform differently if C5 and D5 are integers (and without the space)? In most programming languages, & does bitwise addition.
Clinched, minus I-93 (I'm missing a few miles and my file is incorrect)

Traveled, plus US 13, 44, and 50, and several state routes

I will be in Burlington VT for the eclipse.

kphoger

Quote from: 1 on July 17, 2020, 04:39:28 PM
Do they perform differently if C5 and D5 are integers (and without the space)? In most programming languages, & does bitwise addition.

Nope, it doesn't appear they perform differently.

Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

SEWIGuy

I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing.  They just never got rid of it.

kphoger

Quote from: SEWIGuy on July 17, 2020, 04:50:43 PM
I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing.  They just never got rid of it.

If that's true, then it makes total sense.  There would be no reason to get rid of it, or else a bunch of users' documents would stop functioning properly.
Keep right except to pass.  Yes.  You.
Visit scenic Orleans County, NY!
Male pronouns, please.

Quote from: Philip K. DickIf you can control the meaning of words, you can control the people who must use them.

jeffandnicole

Quote from: CtrlAltDel on July 16, 2020, 03:37:49 PM
Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
Excel is a huge program, with thousands and thousands of formulas.

This comment piqued my curiosity, and so I dug around a little bit, finding this list, a quick count of which (I used Excel) shows that there are about 492 different Excel functions.

Yeah, I did exaggerate that a bit, but what's relevant is most people know very few functions. That most people rarely use more than a few, maybe a dozen if they're going more intense work with it.

Scott5114

I'm a little annoyed they chose the ampersand for that. Most programming languages use the period as the concatenate operator, or else overload the "+" operator (meaning the formula =A1+66 would return "US 66" if C5="US ", or "76" if C5="10").
uncontrollable freak sardine salad chef

kurumi

Excel's a great tool, but even after (30?) years, it still deals with the system clipboard like this:

My first SF/horror short story collection is available: "Young Man, Open Your Winter Eye"

Eth

Quote from: Scott5114 on July 18, 2020, 02:00:36 AM
or else overload the "+" operator (meaning the formula =A1+66 would return "US 66" if C5="US ", or "76" if C5="10").

*whacks Scott with rolled-up newspaper* no! bad! no coercing numbers into strings!

I guess it's not so much the operator overloading that I'd have a problem with, but IMO if you want string concatenation, you need both operands to be strings (so =A1+"66" would be fine, but not =A1+66).

*grumble* this is why I don't work in JavaScript...

Scott5114

It's okay, I'm allowed to do that! I code in Perl! :P
uncontrollable freak sardine salad chef

Rothman

I have used Excel and Access extensively in my career, although less though now that I'm an...upper middle manager, I suppose.

I don't believe VLOOKUP still requires data to be entered in order.  It just finds the first instance of what you're looking for now.

So...lookup formulas, index/match if needed...there's a lot in Excel.  The new-ish what-if capabilities are also helpful in my line of work.

But regarding Pivot Tables, I am constantly disappointed in them.  I find just using formulas to give my workbooks more flexibility.  Keep waiting for Microsoft to provide less stringent functionality as I hear people rave about them and haven't seen it come to fruition yet.

I also use Excel for non-work purposes as well.  Love crunching numbers.
Please note: All comments here represent my own personal opinion and do not reflect the official position(s) of NYSDOT.

frankenroad

Quote from: SEWIGuy on July 17, 2020, 04:50:43 PM
I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing.  They just never got rid of it.

That well could be - I've been using CONCATENATE for probably 20 years - I've never tried the & - but I will now that I know about it!!  You CAN teach an old dog new tricks.
2di's clinched: 44, 66, 68, 71, 72, 74, 78, 83, 84(east), 86(east), 88(east), 96

Highways I've lived on M-43, M-185, US-127

kalvado

#49
Quote from: frankenroad on July 20, 2020, 12:35:38 PM
Quote from: SEWIGuy on July 17, 2020, 04:50:43 PM
I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing.  They just never got rid of it.

That well could be - I've been using CONCATENATE for probably 20 years - I've never tried the & - but I will now that I know about it!!  You CAN teach an old dog new tricks.
It depends. I still use "+" instead of "=" to start a formula - a leftover from Quattro Pro for DOS days. At least in Office 2016 it still works - input is converted into "=+... "
Now my problem is that Office 365 no longer recognizes that as legitimate approach...



Opinions expressed here on belong solely to the poster and do not represent or reflect the opinions or beliefs of AARoads, its creators and/or associates.