Posts

Posted on

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 on

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 on

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 on

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 on

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.

[youtube=http://www.youtube.com/watch?v=4kkQq3mH8dM]

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.

[youtube=http://www.youtube.com/watch?v=z8UfSotzIiA]

[youtube=http://www.youtube.com/watch?v=pgzblTEVb0U]

Posted on

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.

[youtube=http://www.youtube.com/watch?v=n4aFXHwxuhs]

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 on

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 on

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 on

Frugal Fix Friday: Washing Machine

The washing machine was broken. It wasn’t agitating the clothes and therefore not washing them so much as just rinsing them in water. A visit from the repairman would cost at least $60. No, thank you!

We dissected the agitator and found these little flaps with worn teeth. I later found out these are called “Agitator dogs”.

With a quick online search for our machine’s model number at Searspartsdirect.com we found the exploded diagram of our machine. Using this diagram we could identify the part number of the pieces in our hands. We looked them up and ordered replacements from Sears. It cost about 9 dollars.

A couple days later the part arrived and less than 10 minutes later the wash machine was working great!

I can’t believe we used to pay people to fix things!

I wanted to post some pictures to show where this part is, but while looking for a good pic I found this video that goes through the entire repair instead.
[youtube=http://www.youtube.com/watch?v=V5wIN1J70-Y]