The Army… 8 years later. Part 2

Continuing the story from yesterday.

We filed into a large, sterile, echoing room, past a table with sandwiches, juice boxes and bottles of water. We sat on benches, three to each. A civilian woman walked to the front and began our first of countless orientations. We were assigned numbers based on the order we were sitting. I was now 58A.

Once again I filled out countless pieces of paper. We were offered another chance to dispose of any contraband we suddenly realized we were carrying. To the side of the room there was a display of some of the treasures that had been collected through this process: a bile vial of mouth wash, playing cards galore, every brand of cigarettes you could imagine, nail clippers, knives, firearms and more.

Next we filed through several rooms and were issued hideously rough brown towels that would barely fit around our bodies, a light green laundry bag to carry these items, a pair of black exercise shorts, one gray t-shirt, and a gray sweat suit, each with black lettering the reflected our new owners. Glancing at my watch I saw that it was almost eleven thirty. I need to change my watch to military time, I thought to myself.

Thirty minutes later we arrived at the barracks where we would bunk as we went through the Welcoming Battalion processing. Following an announcement that wake-up call would be at four, we were silently assigned bunks in a cavern-like dark room where other females were already sleeping. Females on guard shift directed us with red lighted flashlights to gray metal bunk beads guarded on each end with tan metal lockers. We were instructed to put on the clothing we had been issued – civilian clothing was not to be worn again until after graduation – lock up all issued items, along with our personal bags, and get to sleep.

***

“Get up. Get out of that bed before I get in there,” bellowed through the room’s intercom system. Having been assigned a top bunk I groggily, but carefully, maneuvered to the floor. I unlocked my locker, grabbed the hideous towel, soap and a toothbrush. I rushed to the latrine hoping to beat the other hundred females.

As I walked, I notice a female across from me who had propped her cast right leg against the bunk across from her. I learned later that she had torn some ligaments while marching to chow a few weeks prior and was stuck here until it healed. Everyone called her Crutches. Next to her was another private who had graduated Basic, shipped to AIT (Advanced Individual Training) and found out she didn’t qualify for her selected job. What had followed was some type of lawsuit and she had been assigned a new job and was waiting for security clearance. The female on the top bunk across from me spent quite a lot of time crying having left her new baby at home with his alcoholic father.

After using the latrine I returned to my bunk. I pulled on my sweatshirt feeling quite official with the word ARMY displayed across my chest and put on my running shoes. I quickly locked up my locker which was to be secured if we were more than an arm’s length away. This along with marking all items with our names was to guard against stealing. Apparently theft was a common occurrence in the military and explains why I still have white granny underwear with my last name permanently written on the elastic band.

This morning was my first experience with the phrase “Hurry up and wait.” Up at 0400 and yet I sat there waiting for what felt like forever. And in those few minutes I met the three battle buddies that I would treasure throughout the experience and after: Stick, H and M. H had just gotten engaged and was head over heels in love. M was signed up to be a parachute rigger and would be heading to jump school if she passed all the physical demands it would require. Stick was anything but a stick, but had a sweet spirit about her. All four of us became fast friends and were discharged four months later. Stick had a vertical hip fracture, H a sprained ankle that refused to heal even after months of physical therapy and M and I with a friction disorder that caused our knee caps to pull sideways when we bend our legs. H was the first to get injured, I was the second. But I was the last to be pulled from training. I was the only one to attempt the final physical fitness test; the only one to fail.

The Army… 8 years later. Part 1

It’s been 8 years since my discharge from the Army. Today I thought I’d share a piece that I wrote a few years ago about my experience:

I’m often asked, “What made you do it?” Honestly, I’m not sure exactly what I was thinking. I wish I could say it was some profound sense of patriotism, but I think it was probably boredom. when the Recruiter called I was looking for something challenging. So I did it. I enlisted in the US Army in the midst of The Global War on Terrorism.

I can still remember Sergeant T, dressed in his Army Greens, metals and all, knocking on my apartment door. He sounded like a used car salesman; he talked fast, used big words and made it sound like my life would hit the fast track if I enlisted. Not to mention serving my country, learning new skills and finally the killer jab to my unchallenged self: adventure.

I took the Aptitude Test and scored high enough that I was allowed to choose from any of the non-combat jobs. And it was one of these jobs that finally captured me. The US would send me to language school, give me a large cash bonus and put me in a position where the CIA highly recruited. In exchange I would simply sign away the next five years of my life.

Next I had to tell my family. The shocked silence that transmitted over the phone line was deafening when I told my Dad; my Mom’s fearful look despite her encouraging words pierced my heart; the concern of my friends and coworkers remained with me through the months that followed. I spent the majority of my time before shipping out waffling between fear, excitement, alarm and euphoria.

***

Well, four months after my decisions I was in LA going through final processing. I filled out what must amount to at least ten tree’s worth of paperwork, signed away my frist born, peed in a cup as a nurse watched, performed awkward exercises in my underwear and had a complete physical. And I do mean complete. Having passed I was given orders to ship.

So in the dead of winter, I, the Southern California Girl, shipped to the middle of Missouri. My journey from the Military Entrance Processing Center in Los Angeles to Fort Leonard Wood, fully gender integrated training facility, began at four in the morning on 18 January 2005. The first twelve hours was spent between plane rides and layovers in Arizona, Oklahoma and finally Saint Louis.

The last three hours of travel was a bus ride. I sat next to Private M. She was a black girl from the South and spoke constantly about all the items she had packed, ranging from a silk nightgown, cosmetics and “hair care products.” She said “hair care products” in the most amazingly slow southern drawl I’ve ever heard. We ended up bunking the same sleeping bay during all of basic. Each morning her battle buddy would yell and scream at her to hurry up because shi did everything in the same slow manner as she smoke. Drill Sergeant A constantly said that she must have Ice Cream music playing in her head because she always had a silly smile plastered on her face, along with the hair that was plastered to her head with the hair care products that lined the top shelf of her metal locker. Her obsession with her hair always shocked me. She would stay up after lights out at 2100 to fix her hair despite the fact that each morning began at 0400. She also must have had an unusually large amount of testosterone in her body because she grew a semi-goatee on her chin and had the most leanly defined muscles I’ve ever seen on a girl.

***

In the pitch dark the bus continued giving me the sense that we were going to the middle of nowhere and even should I attempt to escape there was nowhere to go except into the deep void of the night.

As we pulled up to the base entrance, the bus stopped and the driver passed out black garbage bags to collect contraband. I tossed in the piece of gum I was chewing along with the rest of the pack. Others got rid of cigarettes, chewing tobacco, mouth wash containing alcohol, playing cards and almost anything else you could imaging ranging from bongs to Walkman.

We drove on. Out of the darkness rose an enormous illuminated building. From this building came an equally enormous black man. His drill sergeant hat evident as the light silhouetted his body. The shape of that hat put the fear of God into my soul, and still does.

He jumped into the bus opened his mouth revealing a gold front tooth and began screaming, “Get off my bus. Get off my bus right now! Line up. Line up! Females on the left. Males on the right. Get moving. You think I’m joking with you, Private? When I say move, I mean now!”

“You better move. Get into a straight line. You think that’s straight? STRAIGHT! You: face the other way. Now! Yes you. You see anyone else facing that way?”

Once we were in a somewhat straight line we filed into the building. All I could think was “Whew I made it through those five minutes without any of his attention focused on me.” I decided that for the next ten weeks I would try to make myself as invisible as possible. That didn’t really work out for me. On day 0, I was given the name “Private Retardo” by the meanest, shortest woman I’ve ever met. She made it her mission in life to make my life miserable. Thankfully I had no idea the she existed and so I continued on with excitement for the adventure that was about to start.

Tune back in for part 2 tomorrow.

Project Management and Rapid Redirection

Four weeks ago a major project came through the marketing department like a tsunami. The rapid transition from projects that already were underway to something of this magnitude caused me to think about managing projects and tasks in a way that allows for quick redirection.

I strongly believe that as a project owner/manager, it’s important to take ownership seriously; to be invested, set personal goals and push it forward. I’ve also learned (the hard way!) that it’s important to keep a loose grip on that ownership. Every day there’s the possibility of something arising that is strategically or corporately more important than what I’m currently doing.

I’ve found that adjusting focus works best for me if I take a few minutes to set aside my current work and make sure I have something written down that shows where I’m at with each project and tasks currently on my radar. Because the very nature of my job means that I regularly get completely redirected, I’ve been working on a personal organization system that helps me do this quickly.

Getting Stuff Done

I like to follow the Getting Things Done (GTD) model of writing down all tasks in a way that is guaranteed to remind me to do them (I currently use Wunderlist). Otherwise tasks constantly bounce around in my mind saying, “Do me. Do me.” Or I’ll run into that moment when the task that I couldn’t get out of my mind disappears right when I’m trying to remember it. I started using GTD 3 years ago. It helped me increase my capacity significantly. I continue to use GTD because I’ve found that it helps me:

  • Switch projects quickly without losing all of the momentum on the one I’m temporarily putting aside.
  • Switch mental gears so I can pour my focus onto the current assignment

Weekly Recaps

I do weekly recaps that are meaningful to me. My supervisor has asked for a weekly report. I could do it just because it’s required, but I see it as an opportunity to take a few minutes to get out of the details and look at where I’m at, where I want to go next week and what could get in the way.

1) I typically ask myself:

  • Are there projects that are almost finished that I can wrap up with a little extra effort next week? If so, I add those tasks to my list for next week and identify a time when I’m hoping to work on it.
  • Are the right projects in my do now/do later buckets? If not I switch them.

2) I go through and give a quick written (since I process best in writing) update on EVERYTHING I’m working on grouped by projects that are “on fire” “do now” or “do later”. I invest more time on the “on fire” and “do now” items. I use this time (normally 15-20 minutes) to set goals for myself for next week. I rarely reach all of my goals, but achieving them isn’t the point. The point is to provide me with direction/focus for what I should do if no other emergencies land in my lap. And by writing them down, if an emergency comes my way I can refer back to this update when I’m ready to move forward.

3) I highlight key items. I do this is because I send my weekly report to my boss with an even longer list of open discussion items and follow-up items for him. Highlighting draws attention to the stuff we really need to cover. I typically highlight things where I:

  • Need his ok to proceed
  • Need something from him to move forward
  • Need his help to get around a road block
  • Want him to be aware of

By preparing ahead of time, I can go over a 4-page list of updates in a 30-45 minute meeting.

After the water receded

After 3 weeks of intense and long hours, it’s taking a while to get back up to speed. My first day “back to normal” I couldn’t figure out where to pick back up. Then I realized that I could look at my weekly recap from three weeks before and start there. It was so useful! It didn’t solve the exhaustion but it helped make this week more productive that it would have been otherwise.

 

Remembering Emily Faith

Exactly two years ago, a sweet baby girl came into the world too early and ten hours later left us too soon. Emily Faith you are missed.

Awake but Dreaming of You

It was a journey that began abruptly
A little warning with blood and contractions
But with hope that the Great Physician would
Take care of the precious life
That the Creator would sustain

I saw healing and restoration as I prayed
And thanked Him for His provision
Before it came
Trusting in His unending care

Anxiety as the flight took off
Carrying sister and child
Dear God, please help us
The only words

Thankfulness and respite
For a moment
As all seemed calm and well

Shock and fear
Emily Faith was born today
She survived

It’s a long journey
Thirty percent
four to eight months

Lord God, give us strength for this journey

Hope for healing
A strong heart beat
All her fingers and toes

Dear God, thank You for life

In a meeting
Phone vibrates
Shock, horror, grief, disbelief

“Emily did not make it”

Focus.  Where were we at?
Let’s continue
Let’s wrap this up

Feel the darkness coming
Trying to stave it off

WHAT?!?!

Phone Rings

Mom

I um.

Point to phone
Room next door is empty

Sit on couch
Answer phone
Silence with shock and sobs

Barely speak
Hang up

Shock

I don’t understand

Walk to desk
Grab purse
Emily didn’t make it.
I’m leaving

Want to give a hug
But can’t

Can’t hold it together

An endless walk
To car and safety

Sit.

Text prayer warriors

Drive

Parking lot
Like a movie
Sobbing on steering wheel

Oh God, How are we going to make it?
My family can’t handle this, too!

 

Oh, my God!

Oh, my God!

Oh, my God!

 

Why have you forsaken me?

 

Rocking sobs
Racking grief

Get out of car
Walk in building
Into elevator
Moving up

Phone Rings.

Mom.

Answer.

 

Emily is alive.

 

Is this a joke?
No.

No.

It’s

A

Miracle.

Miraculous.
Wonder.

Hushed
Reverent
Oh, my God
On a sigh
Of thankfulness

Just think in twenty years,
We’ll tell how God
Miraculously brought her back to life

The little girl Jesus healed.

How great is our God.

Go to appointment
In shock
Can barely speak
Then can’t stop

Am I crazy?

My finite mind can’t keep up

Fear
Peace
Anxiety
Hope
Despair
Renewal

All the juxtapositions of opposites

Hope soars
And blossoms
Into dreams

Talk to sister
Doing well

Long road

Should I come?

I don’t know

Do what you want.

Head to friends

Share my news

Hugs and love

Driving home

Return to work

Work, work, work.

Share story with a friend

Praying, praying, praying

Turn off computer

Phone vibrates

 

“Emily has passed away”

 

A second walk
To a distant car
That’s not really very far

Sit in darkness

Numbness

A whispered
Oh, my God

Texted back

“So sorry. I love you.”

No words

Call friend

“Can I come over?”

“What happened?”

“She died.”

“Ok.”

Sitting
Laughing
Distractions.

Can’t stay awake
Can’t go to sleep
Restless
Streaming tears

Oh, my God.

Visions of Emily
Safe in His arm

“Antie Krista
I love it here
I wish you were here
Can’t wait for you
To come”

She’s happy
And dancing
With sweet ‘lil ones
My friends have
Lost

Loss

“I’m glad you’re there
But I wish you were
Here

I didn’t get to hold you
I wish I could”

I should have gone on Monday

But, I didn’t.

I trust God’s prompting
And His timing

But sweet, girl,
I wish I had touched you

Just a second
So I could remember
The feel of your little
Fingers

The softness of your head
All ten toes.

My sweet, sweet niece
I love you more than I can say

You and Maddie.
I love
more than I ever thought I could

And you are gone

Never to be held in my arms.

I know someday I’ll hold you
I’ll hold you forever

I guess I’ll share you with your mommy
And daddy and everyone else

But I can’t wait to just hold you
Hold you and spin
And laugh with joy to
Just be with you

Sweet, precious Emily Faith
I’m so glad your parents named you

The perfect name.
For a perfect tiny
Short
Life

Ten hours

So short but long time

Ten hours and life has changed

COMPLETELY

The world’s axis has shifted

So tired
Can’t sleep

Tears streaming
Awake
But dreaming
Of you

——————————————————–

You can read Emily’s story on my sister’s blog.

Gifts you did not earn: thoughts on Deuteronomy 6

The first chapter in my plan to meditate on scripture is Deuteronomy 6. The gist of the passage is that the people of Israel are being reminded of the law before they enter the Promised land after 40 years of wandering in the desert. For several days I read through the chapter multiple times using different versions: New Living Translation, NASB, Amplified Bible, etc.

A few years ago Larry Warner taught me to meditate on scripture by reading a passage and then identifying the portions that I felt resistance to or that I felt particularly drawn to. Through this technique, I’ve seen God use scripture to radically transform my perspective of who He is, who I am and who others are. As a teenager I was actively involved in inductive Bible studies through Precept ministries. One of my favorite techniques from those studies was (and still is) identifying keywords and themes that repeat throughout a passage. As I mentioned recently, my perspective on the Gospel has been radically changed as a result of several books written by Elise Fitzpatrick. She challenges readers to look at passages in terms of who God is instead of what we’re supposed to do. One thing she said has stuck with me. The paraphrase is: in our quest for godliness have we left God behind?

So I’ve started my reading/meditation with these 3 things in mind: looking for what a passage says about God instead of just identifying “action items,” looking for keywords, and listening for what resonated in my soul (whether positive or negative).

And what stuck out to me was the phrase “which you did not” in verses 10-12: “And when the Lord your God brings you into the land which He swore to your fathers, to Abraham, Isaac, and Jacob, to give you, with great and goodly cities which you did not build, And houses full of all good things which you did not fill, and cisterns hewn out which you did not hew, and vineyards and olive trees which you did not plant, and when you eat and are full, Then beware lest you forget the Lord, Who brought you out of the land of Egypt, out of the house of bondage.”

As the Old Testament story continues it is clear that the Israelites did in fact forget the God that gave them all these things that they did not build, fill, hew or plant. Which made me wonder why God gave them these things since they simply took for granted. Why does He graciously give any of us anything? Day after day He gives me things that I did not work for; He has graciously provided job after job that I didn’t apply for, He has brought so many mentors into my life, He has provided not one, but two families, to allow me to live with them rent-free during tough times. My life is filled with things that I have been given that I did not earn. I am afraid that I will forget the Lord. I also proudly think, “there’s no way I could forget who He is and what He’s done.”

And that’s where it became apparent how quickly I take a portion of scripture and drill straight into action items. (i.e. I need to make sure I don’t forget the Lord.) What does this say about the Lord? He gives abundantly. He fulfills His promises (Deut 6:23).

What do you think? What has God given you that you did not earn?

Bible study plan for 2013

For 2013 I’ve decided to take a different approach to Bible study and daily devotionals. I can’t tell you how many times I’ve started down the “read the Bible in a year” route. I’ve read 60-80% of the Bible many times over the years. But some of those minor prophets? I’m not sure I’ve read them at all. It’s always the same story: fervent commitment for a few months (sometimes even half a year) and then somewhere something happens and I get behind. One year I think my attempt to read the whole Bible carried over to about 18-months before I started a different “system.”

I’m a system person – I’m always coming up with a better system to accomplish things. The problem is I’m not a good maintainer. I hate doing the same thing day-in and day-out. The drudgery really gets me down. I guess it makes sense that this would affect the way I do Bible study.

Now that I work at a company that creates software for Bible study, it seems fitting that I renew my commitment to the activity. But I wanted to come up with a more realistic plan/goal for myself. As I mentioned in this post, I’ve recently found that meditating on an entire chapter has been really encouraging to me. So, for 2013, I’ve done a little research and picked 38 chapters I’d like to meditate on.

Why 38? Well, I decided that 52 (a chapter per week) was not realistic. There is a 100% chance that I will fall off the wagon a few times during the year. There’s also a chance that I’ll want to meditate on a chapter for more than a week. I also didn’t want to pick anything that made it feel like the plan was set in stone (i.e. no 1 chapter every 2 weeks, or 3 chapters a month, etc.). That type of rigidity freaks me out because I feel obligated to stick with it. I’m not even committing to read all of them in a year or at all. It’s amazing how that type of freedom is so invigorating to me.

How did I pick my 38 chapters? Well, I did some Google searching and compared the results. Ultimately I chose to study all the chapters listed in the table of contents for Spurgeon’s Commentary on Great Chapters in the Bible, plus some recommendations from these blogs: Bahula.ca, Codex Markianus, and Ekklesia. I’ve really wanted to dig into some of the prophetic books, so a good chunk of what I chose came from those books.

  • Deuteronomy 6
  • Matthew 5-7
  • Isaiah 6-7
  • John 1
  • Isaiah 14
  • Psalm 23
  • Isaiah 35
  • John 14
  • Isaiah 40
  • Psalm 51
  • John 15
  • Isaiah 52
  • John 17
  • Isaiah 53
  • Acts 2
  • Isaiah 55
  • Psalm 199
  • Jeremiah 18
  • Romans 8
  • Jeremiah 31
  • 1 Corinthians 15
  • Lamentations 3
  • Psalm 139
  • Ephesians 1
  • Ezekiel 8
  • Colossians 1
  • Ezekiel 10
  • Ezekiel 33
  • Psalm 103
  • Hosea 14
  • Daniel 9
  • Psalm 22
  • Zechariah 14
  • Hebrews 11
  • Malachi 4

 

Lead me in the way everlasting

My heart cries: lead me in the way everlasting;
the everlasting way of the Lord.

You are everlasting
and your way is eternal.
The flowers may fade
but Your Word remains.
Lead me Your everlasting way;

The way of the Lord
Is illuminated by
the light of the Word.
Light of the World
Lead me in your everlasting way

You have searched me and known me;
You know me intimately.
You embroidered me together
with colors too wonderful to imagine–
with detailed thoughts of how
my way would overlap with Yours.

Lead me in the way everlasting;
the everlasting way of the Lord.

You works are wonderful.
Inspi’ring deep awe and praise.
Your ways are too much for me
to know.

How good it is to taste
the goodness of the Lord.
Like the sweetest candy
or the freshest water.

Lead me in the way everlasting;
the everlasting way of the Lord.

Inspired by: Ps 139 (Amplified version), Isaiah 40:8, Psalm 119:105, Psalm 34:8. John 8:12.

Excel Tips & Tricks: My favorite formulas & features

My co-worker Jonny recently told me that he used to hate Excel until he saw me use it. I think I’m still in shock that someone could hate Excel. I am a huge Excel nerd. There’s very very activities that I love more than getting lost in spreadsheets. A few years ago I converted to Mac at home. I love everything about Macs EXCEPT Excel for Mac. Compared to the PC version, using it feels like torture.

My favorite Excel features:

F4.  Use the F4 key to repeat the last thing you did (i.e. if you just made a cell green, use F4 to make another cell green. If you just deleted 3 lines, use F4 to delete 3 more lines). If F4 isn’t working on your PC, make sure F-lock is disabled. This feature is a no-go in a Mac.

Format Painter. I hate wasting time on formatting. But thankfully, the format painter button means I only have to format once and then I can easily “copy” that formatting to other cells. I use format painter so often that I like to add it to my “Quick Access Toolbar”

QuickAccess

The Quick Access Toolbar in Excel

Customizing the Quick Access Toolbar (only on the PC version). For weeks after I converted to the new ribboned version of Excel, I struggled to find the functions I needed. Thankfully they added the ability to customize the Quick Access Toolbar. There are 2 ways to do this: 1) Selecting the downward facing arrow on the top bar, selecting “more commands”, then look for the function you want. Here’s a step-by-step visual on how to do this. 2) Right clicking on the function then selecting “Add to Quick Access Toolbar”

Filtering. I often use Excel to track large amounts of information and filtering allows me to find the information I need.

  • A tip I’ve learned the hard way: If you add a column after turning on filtering, double-check that the filter icon is showing up in the top cell. If it’s not showing up and you sort your spreadsheet, the information in that column won’t sort. To fix, just turn the filtering feature off and then back on.
Filter

Find/Replace. Find and replace is often a life-saver. Right now I’m putting together a marketing plan for 2013, but because it’s 2012, every date I enter defaults to 2012 unless I type in the year. By using find/replace I can quickly replace all 2012 dates with 2013. The speeds up my data entry. I like to use the short cut Command (or Control on PC) + F

  • Using an asterisk makes it even more handy. If you type a comma and then an asterisk (,*) in the FIND box and then leave the REPLACE box blank, Excel will delete everything to the right of, and including, the comma. If you type an asterisk and a dash (*-) Excel will delete everything to the left of, and including, the dash. If you want to keep the dash, just type a dash into the REPLACE box.

Freeze Panes. When working with a lot of data, this feature is also incredibly helpful. It allows me to see the top and left cells even if I scroll to the bottom or far right of the worksheet. Learn how to use the freeze pane feature.

Copy/Move sheet. If you want to copy an entire sheet to another workbook (that is currently open) you can move the entire sheet, formatting and all, in a few clicks. Learn how.

A few features I really like, but that I don’t use very often:

  • OtherfavsText to column. I use this when I have information in one column that I want to split into multiple columns.
  • Validation. This feature allows for more accurate data entry. I prefer to validate using a list that lives on a different worksheet.
  • Remove duplicates. This button makes identifying unique values in a long list quick and easy.
  • Group. This is great when I’m working with large groups of information that I want to condense or subtotal.

If you don’t mind “tech speak,” this blog has some tips and tricks that I love to use, plus a few that I was really excited to learn.

My Favorite Excel Formulas

In addition to the normal addition, subtraction, multiplication, division and sum formulas, these are the top 3 that I use that make my life easier.

FebDates=WORKDAY(  I use the workday formula for planning purposes. It allows me to calculate when a project should start (if I have the due date) or when a project will be finished (if I know when I’m starting it) and it accounts for weekends and any holidays I want to exclude. Check out this post on how I use the workday formula to project plan.

=CONCATENATE(  I use concatenate when I want the values of multiple cells to be “added together” into one cell or when I have additional text I want to add to other cells. This blog has a good step-by-step instruction for using the concatenate function.

=VLOOKUP(  I use vlookup to compare information from multiple worksheets. Check out this step-by-step guide to using the vlookup formula in Excel.

On occasion I also like to use:

  • =MIN( =MAX( and =AVE( I use these formulas when I want to know the average, minimum and maximum values of a group of cells
  • =IF(  The if formula is more advanced than most of what I covered here. There are a lot of uses for it, but I primarily do a =IF(A2=””,””,FORMULA) calculation that says, if cell A2 (or whatever cell I’m calculating off of) is blank, return a blank value. If it’s not blank, then do this FORMULA. And the calculation I want to do is nested where it says “FORMULA”.
  • =PROPER( =LOWER( = UPPER( These 3 formulas will change the case of text in a cell to Proper Casing, lower casing or UPPER CASING.
  • =COUNT( will tell you how many cells in a range contain a value

Excel formulas can get gnarly really quickly. Just do a google search and you’ll see what I mean. I always suggest keeping it as simple as possible. And to do a sanity test on the results of your formulas.

Other Excel related blog posts:

vlookup formula in Excel for Mac

I recently discovered the value of vlookup in excel. And I keeping finding more and more ways and reasons to use it. Last week a co-worker needed to compare values on a report from two different dates. We were chatting about it and I realized the vlookup would solve her problem.

In this example, I have sales reports from 3 separate days.

3days

I want a quick look at how much revenue I made, by product, over all 3 days.

Step 1: I want a consolidated list of products.

  • Step 1a: I’ll copy the values from column A in all 3 sheets into a new workbook
  • Step 1b: I’ll highlight the column
  • Step 1c: I’ll go to the Data Ribbon and select Remove duplicates
  • Step 1d: When it asks me, I’ll confirm that I want to remove the duplicates

1

 

 

 

 

 

 

 

Step 2: Create Formula. =VLOOKUP(A2,’Day 1′!A:D,4,FALSE)

What this says is in English is:

  • Go to sheet Day 1 [‘Day1’!],
  • Look at the information in columns A thru D [A:D],
  • Find the row that says “Shoes” [A2]
  • Then give me the value that is in column 4 in the row that says “shoes”
  • And only display information if the value in A2 is EXACTLY the same [FALSE].

The logic for the formula is in this order: the value we’re looking for [A2], the place where we are looking to find it [‘Day 1’!A:D], the column with the information we want to display [4], and we want exact values [FALSE]

Formula

Step 4: Use the formula for column C, but link to Day 2’s worksheet.

Step 5: Use the formula for Column C, but link to Day 3’s worksheet.

Step 6: Copy values down for all rows.

This is what you should end up with. Here’s the spreadsheet if you want to practice. You’ll notice that this returned errors for the products that didn’t sell on certain days. There are two solutions to this.

Results

SOLUTION 1: more steps, but less complicated

If you won’t be constantly updating the spreadsheet, you can do a quick and easy copy/”paste value” and find/replace:

Step A: Select all cells

Step B: Copy. I prefer Command (or Control on PC) + C

Step C: Right click, select “Paste Special”, then select “Values”

Rightclick

Step D: Do Find/Replace. I like to select Command (or Control for PC) + F, then select the “Replace” button. Type in #N/A in the Find box and leave the Replace box empty. Select “Replace All”

Replace

Now you have cleaned up data and you can run calculations or create charts with it.

SOLUTION 2: the more complicated formula solution

If you’ll be using this spreadsheet on an ongoing basis, I’d suggest an =IF( formula.

We’re starting with
=VLOOKUP(A2,’Day 1′!A:D,4,FALSE) and we’re going to change it to
=IFERROR(VLOOKUP(A2,’Day 1′!A:D,4,FALSE),””)

What this says in English is: If there’s an error when doing this formula, then give me a blank cell.

NewFormula