Sum By Color in Excel 2013

To sum by color in Excel 2013 (and previous versions) you must create a VBA Module. Don’t worry, it sounds scarier than it is. Follow these steps to create a new function called SumByColor to your workbook.

  1. Press ALT+F11 to open Visual Basic for Applications
  2. Click Insert | Module
  3. Paste in the following code:

    Function SumByColor(MyRange As Range, MyColor As Range)

    SumByColor = 0

    TheColor = MyColor.Interior.Color

    For Each cell In MyRange

    If cell.Interior.Color = TheColor Then

    SumByColor = SumByColor + cell.Value

    End If

    Next cell

    End Function

  4. Press CTRL+S to save your workbook.
  5. Close Microsoft Visual Basic for Applications
  6. Use your new function in your workbook using this syntax:
    =SumByColor(Range,Color)
    • Range is the Range of cells you want Summed.
    • Color is a cell that contains the color you want summed.
  7. Example:
  8. To preserve this functionality in the workbook, you will need to save it as an “Excel Macro-Enabled Workbook
Posted in Computers and Internet | 2 Comments

DIY: Garage Storage Part I

Our new house has a nice, attached, 2-car garage. Our 2 vehicles fit side by side with no room to spare. It feels like that’s all there’s room for in there but we also want to fit holiday storage, bicycles, garbage/recycling bins, and ideally a workbench. Here is a horrible Microsoft Paint sketch of our garage space before we tore out the shelving:

These past 2 weekends we built new shelving and workbenches to make it feel a little bigger. The first thing we did was rip out the old shelving. Yay, a fresh canvas!

I had already drawn up some plans for what I wanted the new shelving to look like on my trusty graph paper. So next it was a trip (or 2) to Menard’s to get supplies. We built shelving 2′ down from the ceiling that wraps around the perimeter of the garage (as much as it can until the garage door gets in the way). We wanted it to be suspended shelving because our main priority in the project was to still be able to park cars in there and we have to park close to the walls to fit. Therefore we could not have any ground supports for the shelving. The shelves on both sides were constructed the same, just mirrored. Here are some rough (by “rough” I mean that I didn’t bother to add any hardware. They are drawn to scale) CAD drawings of the shelves from the right-side:

Overhead Storage From Above

Overhead Storage From Below

 

We also built a wall-mounted workbench on the right side in front of Amy’s car. In fact, we built 2 workbenches! The second is on wheels and the top can be raised/lowered so that it can be tucked under the wall-mounted one when not in use.

Here are the rough CAD sketches of those:

Permanent Workbench – From Above

Permanent Workbench – From Below

Mobile Workbench – Raised

Mobile Workbench – Lowered

Mobile Workbench – From Below

Here’s a sketch of the whole garage with all the new stuff:

And lastly, here are pictures of the garage after it’s full of stuff:

Right Side

Left Side (Has been modified to use an angle brace on the back wall instead of the vertical support shown here that blocks access to the shelf)

 

If you are interested in plans (they’re pretty simple and easy to adapt!) I intend to do a follow-up including my original sketches and measurements.

Posted in DIY | 2 Comments

Calculating Length of time in Microsoft Excel

I had a document full of employment start dates in column “A” and end dates in column “B”. Wanted column “C” to calculate length of time in the following format: “1 Year, 2 Months, 3 Days”.

I didn’t find a function for it in Excel (I even searched in “Help”).

So, I did it the hard way:

=CONCATENATE(IF(YEAR(B2)-YEAR(A2)-IF(OR(MONTH(B2)<MONTH(A2),AND(MONTH(B2)=MONTH(A2),
DAY(B2)<DAY(A2))),1,0)=0,””,CONCATENATE(YEAR(B2)-YEAR(A2)-IF(OR(MONTH(B2)<MONTH(A2),AND(MONTH(B2)=MONTH(A2),
DAY(B2)<DAY(A2))),1,0),IF(YEAR(B2)-YEAR(A2)-IF(OR(MONTH(B2)<MONTH(A2),AND(MONTH(B2)=MONTH(A2),
DAY(B2)<DAY(A2))),1,0)=1,” Year “, ” Years “))),IF(MONTH(B2)-MONTH(A2)+IF(AND(MONTH(B2)<=MONTH(A2),DAY(B2)<DAY(A2)),11,IF(AND(MONTH(B2)<MONTH(A2),DAY(B2)>=DAY(A2)),12,IF(AND(MONTH(B2)>MONTH(A2),DAY(B2)<DAY(A2)),-1)))=0,””,CONCATENATE(MONTH(B2)-MONTH(A2)+IF(AND(MONTH(B2)<=MONTH(A2),DAY(B2)<DAY(A2)),11,IF(AND(MONTH(B2)<MONTH(A2),DAY(B2)>=DAY(A2)),12,IF(AND(MONTH(B2)>MONTH(A2),DAY(B2)<DAY(A2)),-1))),IF(MONTH(B2)-MONTH(A2)+IF(AND(MONTH(B2)<=MONTH(A2),DAY(B2)<DAY(A2)),11,IF(AND(MONTH(B2)<MONTH(A2),DAY(B2)>=DAY(A2)),12,IF(AND(MONTH(B2)>MONTH(A2),DAY(B2)<DAY(A2)),-1)))=1,” Month “, ” Months “))),IF(B2-DATE(YEAR(B2),MONTH(B2)-IF(DAY(B2)<DAY(A2),1,0),DAY(A2))=0,””,CONCATENATE(B2-DATE(YEAR(B2),MONTH(B2)-IF(DAY(B2)<DAY(A2),1,0),DAY(A2)),IF(B2-DATE(YEAR(B2),MONTH(B2)-IF(DAY(B2)<DAY(A2),1,0),DAY(A2))=1,” Day”,” Days”))))

 

That worked, but took too long to figure out. Later a coworker mentioned a function called “datedif”. A Bing search revealed: 

“This function is available in all versions of Excel  since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided  not to document this function in any other versions. DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation.” – http://www.cpearson.com/excel/datedif.aspx

So, I could have just done it the easy way:

=CONCATENATE(IF(DATEDIF(A2,B2,”y”)=0,””,CONCATENATE(DATEDIF(A2,B2,”y”),IF(DATEDIF(A2,B2,”y”)=1,” Year “,” Years “))),IF(DATEDIF(A2,B2,”ym”)=0,””,CONCATENATE(DATEDIF(A2,B2,”ym”),IF(DATEDIF(A2,B2,”ym”)=1,” Month “,” Months “))),IF(DATEDIF(A2,B2,”md”)=0,””,CONCATENATE(DATEDIF(A2,B2,”md”),IF(DATEDIF(A2,B2,”md”)=1,” Day”,” Days”))))

 

Example

Start: 3/27/2007

End: 7/27/2008

Result: 1 Year 4 Months

It would have been (was) a lot simpler. The formula is still longer than necessary, but I like for it to only show values that are not 0 and I also like when the value is 1 that it uses the correct suffix.

Here is the formula to use if you want to see all 3 values even if the value is 0:

=CONCATENATE(CONCATENATE(DATEDIF(A2,B2,”y”),IF(DATEDIF(A2,B2,”y”)=1,” Year “,” Years “)),CONCATENATE(DATEDIF(A2,B2,”ym”),IF(DATEDIF(A2,B2,”ym”)=1,” Month “,” Months “)),CONCATENATE(DATEDIF(A2,B2,”md”),IF(DATEDIF(A2,B2,”md”)=1,” Day”,” Days”)))

 

Example

Start: 3/27/2007

End: 7/27/2008

Result: 1 Year 4 Months 0 Days

Posted in Computers and Internet | Leave a comment

Running is altering my lexicon

My freshman high school was made of a series of circles. They forced us to run around the gym circle 14 times. They said this was a mile and a half. It was miserable and usually half of it involved walking. Sometimes if I was feeling extra, extra energetic I ran the whole time. This was rare, and if I succeeded, I felt like death afterwards. I never thought of running further than that. It was impossible to run more than 1.5 miles. Well, it was impossible for me. I knew other people could run further, but they are obviously RUNNERS… that’s what they were born for; it’s natural for them to run. Why else would they do it?

I’ve begun running on my own (sort of) in the more recent past. I started out doing the couch to 5k, or c25k as we often refer to it. C25k redefined the word impossible. All of a sudden after that program, impossible was defined as anything over 3.2 miles.

It's impossible for me to run more than 1.5 miles... I mean 3 miles... er...

Yesterday I ran 5 miles. I did not walk at all. Amazingly, I didn’t feel that tired afterwards either. My goal is over 6 miles now, and probably further in the future.

Impossible has been redefined for me again. In many contexts that I see it used, impossible can be defined as simply a mental barrier. I’ve realized that has been its true definition in my life thus far. Now that I’ve realized this… impossible is kind of a joke… or maybe it’s a challenge?

Posted in Fitness, Life | Tagged , , | Leave a comment

Frugal Fix Friday: Shattered PSP Screen

This is not my PSP, but it sure looks like how mine did.

I got my PSP for free BECAUSE it looked like the one above.

What happened is that it got sat on, dropped or something like that. I saved it from the trash can, spent $25 on a new screen, and became the owner of a PSP. Yay!

I’ll explain exactly how to fix it. It’s easier than you think!

You will need a set of small screwdrivers, and the ability to remove 4 screws in total.

First thing you need to do is remove the battery, and flip the PSP over. Then remove the 4 screws (marked by red circles).

Now that the screws have been removed and were put somewhere SAFE, flip the PSP over again and pull gently on the faceplate to remove it.

Next, we need to remove the button assembly from the bottom of the screen. I found this to be easiest with a small, flat-tipped screwdriver to gently pry on the left side (Red circle).

With the button bar assembly detached from the screen, we must unhook it’s ribbon cable. to do this, use a small flat tipped screw driver to gently lift up on the dark piece of the connector. You should be prying on the top part of the connector (Red Arrow), the hinge is at the bottom where the dark attaches to the light.

Once the connector is up, the cable will slide right out.

Next, remove the screen. There are 4 little tabs holding it in, grab your flat-tipped screwdriver once again, and release the tabs/pry the screen out at the 4 points (there are 2 on both the left and right sides of the screen, Red arrows).

Once the screen has released, flip it forward. Be gentle! We do not want to hurt the ribbon cable connectors.

Now you can unhook both ribbon cables the same way we did the first one. The only difference is that on this large one, you will want to carefully pry all the way along it or use something wide to pry it up, like a debit card.

Time to unwrap the new screen and connect it. Then re-assemble the PSP by following the previous steps in reverse order.

Once the PSP is all assembled except for the faceplate, I like to clean it. I used a can of compressed air, lens cleaner, and a lens cleaning cloth.

First I cleaned the clear part of the faceplate inside and out using the lens cleaner and cloth. Make sure to clean the inside really well. Any dust or fingerprints in there will be visible on your screen, but you will not be able to clean it without removing the faceplate again.

I removed the left and right bumper buttons and cleaned those off because they get grimy.

Once cleaning is done, then replace the buttons, and the front cover followed by the 4 screws in the back.

Grab your favorite game and pick up where you left off.

Posted in Computers and Internet | Leave a comment

Frugal Fix Friday: The Car Window

I broke my car window a couple years ago. Well, first the button to make it go up and down broke. I didn’t know that at the time. So, I brought it to my local Ford dealership and asked if they’d look at it. They said “Sure, it’ll be $180 to diagnose it and at least $200 to fix.” I replied with “I don’t need to roll down my window that badly.”
Later I figured out that it was the switch that was broken and it costs $150 from Ford. I bought a new one for $60 online. That was EASY to replace, and only took about 10 minutes of my time. The video below IS NOT accurate for an 02 Explorer. It’s similar, but there is an additional step before this. Skipping that first step will break a plastic clip. I’ll add pictures of this later. I just wanted to post this video clip to show how easy the switch is to replace.

A couple months later, that switch died. I got a dud, but it had a lifetime warranty! The bad part was that my car was outside, and it had started raining with my window down when the switch died.

What did I do? I broke the whole power window assembly. I took off the door panel, ripped out the regulator/motor, and put the window up manually. This was a stupid idea (now I have to pay to fix another thing) but I was frustrated and getting rained on. I left it like that for about 2 years because I didn’t really need to roll down my window. Plus, if they were going to charge me $400 for a bad switch… how much would this cost!?

Finally, after 3 or 4 people were nice enough to roll down my window for me and get it stuck down again and again, I decided to fix it.

I went to an auto parts store, got a new regulator assembly (paid $70, was $150 from Ford), and put it in myself. Since I was learning along the way it took me a little longer to do this. Probably 2 hours or so. The videos below cover the general process pretty well. When it comes time to attach the new regulator to the glass, you will need bolts to replace the rivets that you bored out. Try to find some bolts that are as short as possible while still making it through both pieces with room for a nut. Also, you’ll want them to be as close to the thickness of the hole as possible to prevent rattling. I got 2 small bolts/nuts from the local hardware store for $1.90.

Another important thing to note is that depending on the wiring paths on your vehicle, they could be very close to where you are drilling when you bore out the rivets. They were in my case. Be super careful, and maybe put something over the wiring to protect it while drilling. You don’t want to sever the wires, or else you’ll have another project on your hands!

Have fun. Save money. Gain new skills.

Posted in Computers and Internet | Leave a comment

Fancy Shoes: Spend More to Save Money

Buy expensive shoes. Let me clarify… spend a bit more to buy a pair of high quality shoes. When you buy for quality instead of price, you end up ahead in the long run. Buying shoes at discount stores like “payless” I could get a pair of shoes that would last me a year at probably $30 a pair. Then I started buying “fancy” shoes. At first this was for fashion (I worked at “Express” then…) but now I don’t really follow the latest fashion trends and I still buy them because I can buy a high quality pair of shoes for $100 and wear them for 5+ years. My current favorite pair of black dress shoes is about 5 years old and they look like they’ve got at least another 5 years left to go. I wear them everyday! I also don’t really baby them too much. Outside of wearing them everyday in all weather conditions I also often times don’t bother to change shoes when it’s time to do work, like shovel snow, or fix a car. All I have to do to keep my shoes looking good is give them a nice polishing every few months.
My cheap shoes over 10 years = $30 x 10 = $300
My fancy shoes over 10 years = $100 x 1 +$5 (shoe polish) = $105 = Winner.

So… next time you need shoes find a nice high quality pair in a timeless style. My favorite pair right now is my Florsheim shoes. They are comfortable, and are the ones I was talking about above that I wear everyday.

I’ve been finding lately that this principle will apply to my life with other purchases as well. For example… I bought ALL of my furniture at Ikea, and I love it. It looks so nice!
BUT… it’s made of soft woods. It gets dinged up really easily, and screws can get stripped right out if it’s not moved with extreme care making it weak. I bought my chest of drawers probably 3 years ago. It’s moved homes twice so far. I’ve repaired it with wood glue and screws twice.  I’ll be moving again soon. I’ve got the Gorilla Glue ready. Soon enough it’ll be time for a new dresser. I could(should) have bought a quality one with hard woods, and dovetail joints elsewhere. I would have paid twice as much or more, but it would probably outlive me.

I feel like almost everything is getting cheaper, cheaper, cheaper and I think that’s great. I love getting a deal, and spending less! But is it always as good of a deal as it seems? Am I really spending less? Or am I getting what I paid for? These are the questions I try to ask myself every time I’m making a purchase now.

Posted in Finances, Life | 2 Comments

Frugal Fix Friday: Lawn Mower

mower
Our lawn mower was running ROUGH! Not a little rough, but dislocate-your-shoulder-trying-to-start-it rough. Eventually we could usually get it started, but would most likely get injured in the process.

Starting: We would yank the cord furiously trying to make it start and would have little luck, and every once in a while it would just lock and the cord wouldn’t pull like you’d expect. Ouch. With some persistence it would start though.

Running: It ran rough. It seemed louder than when we had first bought it. There was an occasional clanking sound like metal hitting metal. Also, It couldn’t handle thick grass like it used to. It would get bogged down and stall really easily.

Cause: We suspected at least 2 footballs had been run over as well as a tree root (possibly more than one).

We really were not sure what to do about this. We thought we’d have to go buy a new mower. We really didn’t want to since this one was only a couple years old to begin with. Fortunately, doing some research online led us to other people with similar problems and an easy (and cheap) solution!

It turned out the problem was a tiny little piece of aluminum (or something soft like that) called a flywheel key. We went to a local small engine shop and bought a 2 pack of flywheel keys for $2. Put one in and the engine was running MUCH, MUCH better!

The video below is a good explanation of how to get at and replace the flywheel key. My only comment is that before you take a hammer to your mower and risk damage. Try just grabbing your flywheel on opposite sites and giving it a pull. Mine came right off without the need to bash it. If that doesn’t work, then give his hammer trick a whirl. His is better (less damaging) than others I’ve seen.

Since we had the mower mostly apart we actually decided to go a little further and we cleaned out the cylinder head and muffler with carb cleaner. This is because we had been getting some black smoke when starting the engine sometimes. We suspect that this is a result of running our engine rough for so long (about a year!)

All said and done we fixed our problem for $2 dollars and went the extra mile for the cost of half a can of carb cleaner.

Posted in DIY | Tagged , , , , , , | Leave a comment

Frugal Fix Friday: Lunch Money

You know what can be a surprisingly expensive aspect of my life at times?

Lunch. I normally wouldn’t think of lunch being expensive but it really can be! I used to always go out to lunch at Chipotle or Panera because those are my favorite lunch destinations. Once I set a goal of saving money though, they were the first thing to get cut since my meals of choice at these places were over $10.
10/day X 20 workdays/month = $200/Month for lunch! That needed an adjustment.

So, I started going home everyday for lunch and eating the leftovers from supper the night before.  Much cheaper! food = $0. Driving back home each day doubles my amount of driving though which means an extra half gallon or so of gas each day. That’s still much better at about $40/month instead of $200.

Next, I decided I wanted to start biking to work instead of driving. Then I don’t have to try to find parking, and I get a little bit of that much needed exercise I’ve been slacking on lately! The idea when I was biking was that I would pack a lunch everyday to bring with me. This worked for about a week. Then I started forgetting nearly everyday. So, since I didn’t have a car I would walk to the nearest eatery, Subway. That’s definitely a step backwards @ $8/day, or about $160/month.

Lunch for a 3-day week.

Lunch for a 3-day week.

Fortunately, I think I’ve found a plan that works for me. I can make my lunches to bring with me everyday, but make them all ahead of time. Then I can’t forget! So, on Sunday nights I prepare 5 lunches (and breakfasts!) for the week. It works great. Each morning I just grab a lunch, and a breakfast out of the fridge, toss it in my bag and I’m off. Not only is it the cheapest route, but I also eat the healthiest this way.

For a typical week, I will make 5 sandwiches, 5 small bags of veggies, 5 small bags of a fruit, and 5 half-cup containers of either unsweetened applesauce, or cottage cheese. Also, I like to pack a small breakfast, which is usually 1 larger fruit (like an apple, orange or banana usually) and something with some protein like yogurt, or hard-boiled eggs. I haven’t calculated the price on this but we buy everything at Aldi, and I opt for the larger containers instead of individually wrapped servings on things like yogurt, applesauce, etc. So, it’s not more than a couple dollars per day.

Posted in Finances, Life | Tagged , , , , | 1 Comment

Frugal Fix Friday: The Dryer

Cat in a clothes dryer

Image via Wikipedia

With the rate things have been breaking at our house lately, we’d be bankrupt in no time hiring people to fix them!

What happened now, you ask? Our dryer. It’ll tumble some clothes like a champ, but apply heat and actually dry them? Um, no.

Fortunately, Sears has a generous amount of documentation online for their appliances. With that and some other resources, I was able to get the flame lit and the clothes dry. Yay!

To fix it, the first thing I checked was the thermal fuse. Apparently it is pretty common for it to trip and then it must be replaced. Ours had continuity, so I moved on. I tested every other thermostat and tester I could find. They all appeared to be fine.

I wasn’t sure what to do next. So, I turned on the dryer and watched the burner while it ran. The ignition coil would glow red hot, then it would make a clicking sound and shut off. It turns out, that clicking sound is the electricity going to the ignition coil being redirected to the gas valve. So, it was apparent that the gas valve wasn’t opening. Looked up that part online and the gas valve is over $100. Not cool. There are also 2 little coils on top of the valve, solenoid coils, they are only $20. It seemed to be worthy gamble to replace those first and cross fingers.

That worked. They were really easy to replace, and only cost $20. Now our dryer is spitting out fluffy warm clothes again. 🙂

Here are some great resources for fixing your dryer:

Posted in DIY | Tagged , , , , , | 1 Comment