Excel-Fu: Selection Boxes and Cost

I thought it might be handy to post some of the tips and tricks I've been learning lately in various software programs. Because in order to teach myself I had to go scouring the internet for different sources, and maybe someone else is trying to do the same thing (plus the best way to remember something seems to be to teach it to somebody else, or if that fails write it down in the indelible ink of the internet).

Say you're considering building an assembly and have different options for different parts that would result in total cost differences. For my example I'm only using two components, but if you had a lot you might want something more automated. In the end what I needed was several drop down boxes where each option would reflect its cost and then add to a total below.

So here's how you'd do that which should be helpful if you've been trying to implement drop downs in Excel, use a lookup table, or figure out how to pull and use data from a selected drop down value.

In order to use a drop down you need what Excel considers developers tools. In order to turn this tab in, you go to File-> Excel Options. There, depending on your version of Microsoft Office, it might be under the popular tab, or might be under the "customize ribbon" tab. You want to have the "show developer tab" checked.

Then you go into your developer tab, click "insert" and there should be a drop down of various options. The one I'm using is the Form Controls -> Combo Box. But I imagine a lot of this stuff would work on either types of controls and boxes.

This will turn your mouse into a little plus sign. Go ahead and drag how big you want your box over wherever you want it. Then right click it and select Format Control.

But let's backtrack. First you're going to need some data for all this. I have two sets of options for my two widgets. You can locate this in another sheet of your excel document, but here I've stuck mine on the same sheet so it's clear what I'm doing.

Now back to your format control. It gives you three options to fill in in the "control" tab. Input range, cell link, and drop down lines. If I'm building the box for my first widget (options A through C) my input range is I3:I5. You can type this in, or just put your mouse in the box then drag your mouse over the cells you want. For right now, you don't care about the cost, only your "names". Cell link is the actual cell where your drop down value is going to be stored. I usually select the cell that seems most inhabited by my drop down box. Drop down lines is the number of values I have, 3 in this case, and I usually select 3-D shading cause why not, it's pretty.

Now you've got a drop down box that has some values in it that you can select. If it's bugging out at this point, you've probably messed something up in switching between sheets, so re-check your values and make sure they're still calling out the cells you want.

Next you'll need two columns to the right. One I've hidden because its sole purpose is to grab the "name" from the drop down. The problem with excel and drop down menus is it just correlates values to numbers. If you try to copy the value of your drop down it will just be a number of 1 through N where N is the number of options you have. In order to get that number to correspond to the value, you have to use a kind of silly workaround formula, the OFFSET formula.

In this case it's =OFFSET(the cell directly above my "names" table,the cell that my drop down box is storing its info in,0). The 0 is the actual offset value. Or in my case, =OFFSET(I2,C3,0). This is all important too because it means you need a row above your names table as you can see I had above. Now no matter which value you select in drop down, the column should mirror that value. Since you don't really need this column again later you can hide it (right click the whole column, select hide).

Next to callout the price value of your component you'll use the tables I showed a few figures up. This is the next colum over and uses the VLOOKUP function.

So the values I want here are =VLOOKUP(the value I'm converting, my WHOLE lookup table, 2, FALSE) or in my case =VLOOKUP(F3,I3:J5,2,FALSE). The 2 is because the value I want is in the 2nd column. If you were selecting a larger table you could specify any column you want. The F3 cell is where I used offset to grab the "name" value from the drop down. The false is to indicate I want only an exact match (for the name) rather than a partial match.

So now you can have drop downs with selected values, connect them to prices, and of course you can use a sum value (=SUM(G3:G7) ) to nab your costs and make a total further down.

Some of these methods are probably cheap workarounds, but still I hope you learned something. If you didn't and were bored sorry. Maybe you can write in with your badass software tricks.

Design Fridays: Volvo's new flywheel

Volvo's claiming their new flywheel design will be able to give a four cylinder the equivalent of six cylinder power as well as save 20% of combustion energy.

 

A flywheel is just a name for a rotational device that's able to store energy. It does this by using inertia and saving rotational energy. The amount of energy that can be stored is based on the moment of inertia and rotational speed, where moment of inertia is based on mass and radius of the wheel. Typical flywheel designs might include differing weights at certain points in the wheel that help keep the rotational speed more consistent and higher.

 

In your typical automotive application, a flywheel sits on the end of your crankshaft and converts the energy and movement of the crankshaft into a more consistent rotational speed.

 

Volvo is claiming that their flywheel will be able to store so much energy that when the engine is idling it can actually turn off and the energy stored in the wheel is enough to start combustion again. If so that could explain their 20% fuel savings number as engine idling is a well known waster of energy (why electrical cars and the Prius shut off at a stop). The flywheel also sounds like it is using aspects popular in hybrids and electric cars: regenerative braking. There've been a lot of attempts to incorporate regenerative braking into designs, the idea that you can get back part of the energy you're wasting to brake. But generally these designs haven't been efficient enough to be practical (plus once you add complications, you add more room for failure, and the more components the heavier your car thus reducing your energy savings).

 

They're supposed to start field testing this sometime later this year.

Bernanke's Economic Outlook

So on Tuesday Bernanke spoke to some banker's association in Atlanta and covered some important topics: like, is the humidity there not craaaazy?

 

Okay, not really. He re-iterated the same stuff he always does. That they'd rather pick from their tool belt of regulatory options to affect the economy rather than doing interest rate changes. Why? Does he not crave absolute power? Well the real answer is the interest rate is already so low, it's no longer a tool that the federal reserve really has available to them. But if they admit to this maybe consumer confidence will implode...or something. Not to mention continuing to keep interest rates incredibly low is really working out for the banker elite who's making a killing on borrowing money from the government at 0% and then lending it back to the government via treasury bonds for 3 or 4%.

 

He mentions trying to keep inflation low, though it's clear from low rates for so long that this is not a priority for the voting majority of the federal reserve board. Instead it's more cheap talk to buoy up certain people (large debtors like banks who have a bunch of mortgages or the government itself) rather than worry about those who are at a critical point (the poor and the elderly living on fixed incomes).

 

He states that his objective is to keep inflation low and keep the value of the dollar high and his excuse for why this is not actually happening is the importation of oil. Gas prices, something the reserve doesn't even look at in its core inflation index, are wildly inflating and somehow contributing to a falling dollar value. The US actually only imports about 51% of its oil. Something like 350 billion a year. With a 14 trillion GDP, 1 trillion handed to banks as free money, and a looming national debt, Bernanke really wants to blame this one on gas prices? If anything the falling price of the dollar might be contributing to speculation on oil as a commodity which could be one of the main reasons for the rising price of gas at the pump.

 

His only concessions to the real people is that they are working on achieving "maximum employment" (almost as if he is admitting to a possible future of long term high unemployment rates). I guess he is working on that like I am working on being nicer to rich, elite bankers. Which is to say, not at all. Then he only briefly mentions that the lower growth rate the GDP is seeing is somehow contributing to what people really care about (employment, real wages) because it is "frustratingly slow". I'm not sure why he'd connect GDP with employment. That's like connecting stock market prices, dividends, and shareholder profits with real wage gains for the working class. The two are not correlated. Or if anything, are negatively correlated.

Perspective

There's been a lot of discussion in the news lately about the James Webb telescope. It's planned orbit is about 1.5 million miles from earth. I was curious how this compared to existing space objects so I rigged up a scale representation. The larger dotted circles are just for clarification of finding the much smaller centers and locations of the objects. The moon is about 250,000ish miles out from the earth and the hubble telescope, at 366 miles, would have been visually on top of earth in this view so I left it off. What do you think, does it properly convey the scale and ambition of the Webb telescope? 

Leave Your Mark

An interesting gallery of John Rennie's engineering legacy. A scottish civil engineer in the 18th and 19th centures he was responsible for design of many canals, bridges, waterways, docks and harbours scattered around Great Britain. When we look at great buildings and wonders we don't always think of the engineer who designed it but they are as much a part of it as the society, labor and technology that went into the construction.

Looking for trends in all the wrong places

I found the above graph here. It's not so important that it doesn't include the last five years or so for my purposes.

 

I was looking at MegaCorp's new hires and discovered that in the last three months of intense intern hiring (it's the season for interns!) 24% of them have been female. I wondered how this compared to engineers hired. Turns out in the first 5+ months of this year, 10% of our new hire engineers have been female.

 

Now, my local university says that about 18.5% of engineering degrees are conferred to women. So the intern numbers seem to be, if anything, on the high side.

 

Compare the new hire numbers to the historical chart at the top and you'd see that we'd have to be averaging 30 years of experience for the engineers we hire for 10% to be a reasonable number. It's unlikely our average new hire engineer is 50 years old or more.

 

What does this mean? Why is the effort being made on the intern level to bring in more women but we don't see it when it comes down to hiring full-timers? It's possible, I suppose, that women are graduating with degrees, and working internships, but then somehow not going into engineer at all. If they are going into completely different fields after getting an engineering degree, and in high numbers, that could explain it.

 

Or is it part of the general trend that women tend to work in lower paying occupations so it's easier for a woman to get hired on as an intern than it will be for her to get hired as an engineer. Or maybe HR is trying to push diversity but can only manage to do so as a part of its intern hiring program but can't convince managers to hire more experienced women.

 

This might make a lot of sense if both numbers were on the low side or on the high side. Then you could draw some conclusion about MegaCorp's particular industry or maybe locality differences. As it is it looks a little strange.

Workplace Politics and the Engineer

People in general have a tendency to over estimate their own skill level in comparison to their peers. I am guilty of this, likely having seen myself as an "above average" employee since the minute I started working. All this despite struggling with impostor syndrome where I am prone to doubts and being found out as a fraud not as capable as I'm pretending to be.

 

So this was kind of in the back of my head when I read this article from Evil HRLady at BNET about whether you should dispute a performance review. The employee was marked average on everything, and felt they should be above average on some things and below on others. Evil HRLady's response seemed practical and correct:

 

 

Problem:  You are, actually, pretty average. Ouch.  Sorry.  But average is average and if you're better in some areas (as you said) and worse in others (as you said) that is going to average out to be, well average.

 

 

Sounds pretty correct. Then the original poster piped up in the comments and tried to stress that they were in fact above average. They pointed out all the things they do (working long hours, helping customers, sorting sections that had been abandoned, volunteering and covering for other employees). This got me to thinking. Their employer doesn't care about all these things. Does an employer really care about your "effort"? Maybe if you're a grad student, beyond that your boss wants to see output, results, to-do lists knocked off. They don't care that you "help" or "share" with your coworkers. It's a known fact that those who are good at working with others and training new employees are rarely promoted.

 

I suspect parallels in academia would be a professor who is an especially good teacher for which they get no credit for. Their research maybe places them at "average" at their university, and in their eyes exceptional teaching in addition to average research makes them above average.

 

I think the key is in order to not kill ourselves at work is make sure you're focusing on what's actually important to your boss. Mine likes to see a lot of written output even when the actual problem is not solved. Tracking progress, tasks and investigations look good to who he has to show his numbers to. Theoretically if you make your boss look good, they will value you. They may take credit for some of your actions (though more often in my opinion even a good employee doesn't always realize the value of direction and ideas from superiors that add to their projects). But in the end if you can separate what you think makes you an "above average" employee with what's important to those doing your review you can probably save yourself a lot of angst and heart burn focusing on things you might think matter but your organization doesn't actually value.