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
Advertisements
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