Excel - Making a Chart behave - Trendlines, copy format, stacked chart.

These are some personal notes, but if you find them useful then go for it!

In Excel, you cannot take a stacked chart and add a trendline to it.  But I wanted to.  I had resorted to using Paint in the past, but this particular chart was going to be rather complex, and I had several of them to build, so I wanted to figure a "better way (TM)".  Here's what I did in Excel 2010.

Select your data and create the stacked chart.  That is the easy part.  For a stacked chart, you would normally have groups with quantities such as this:

Month	Widget 1	Widget 2	Widget 3	Total
March	26	6	13	45
April	21	9	11	41
May	25	7	12	44

Create the chart without using the totals column.

Right click on the chart and "Select Data".  In the "Legend Entries (Series)" pane click on "Add".  Select "Total" for the Name, and add the "Total" data to the Series Values.  Now, the "Totals" are stacked on top of the other data and you should have "Total" in your Series pane.  Click "OK".

Right click on the "Totals" data and select "Format Data Series".  On the "Series Options" tab click the "Secondary Axis" radio button under "Plot Series On".  The "Totals" data will now be hiding the stacked data.

Right click again on the "Totals" data and "Change Series Type" to a regular column.  Click the "Layouts" tab on the Excel Ribbon, click the "Totals" column, and now "Trendline" should be an option.  Select the trendline you want to use, in my case a linear trendline.

Now you have the trendline, but your stacked data is hidden.  Right click again on the "Totals" series and click on "Format Data Series".  Change the "Fill" to "No Fill", change the "Border Color" to "No line", click "Close.

There you go!

Now to copy this to multiple charts without having to walk thru all of the steps.  Begin by creating your stacked chart, but this time include the totals column.  Right click on the first chart and "copy".  Click on the new chart, and, on the Ribbon, under the "Home" Tab, click on the little arrow under "Paste" and select "Paste Special.  On the dialog box, select the radio button for "Formats".  All done!!


Credit to mrexcel.com and techrepublic.com for the tips that I put together to create this.

What do I do?

I often get asked what I do at St. Joe's and find it difficult to explain.  I work as a System Support Admin, which basically means that I boss a computer around :).  I'm also a sort of Systems Analyst, which means that I look at numbers a lot.

I'm part of a team working for the CHI Franciscan Heart Center.  The team follows patients that have heart procedures at any of our Franciscan facilities, and we collect data.  What sort of data?  Well, just about anything that might have some sort of impact on their disease, on their care, on their health, on their outcomes.  How much anesthesia, how much they weigh.  And it is all used for research, to help us provide better care for them and for future patients.  If something doesn't work, we tell the doctors and surgeons about it so that they don't do that again.  If something works better, we help make certain that the technique gets used more often.  We help keep an eye on things to make sure that patients are getting appropriate medications and treatments at appropriate times and places.  We try to encourage the doctors to give patients all the tests they need for proper diagnoses, and to not order tests that are not helpful.

My role is to aggregate and report on the data that the team enters into our database.  At any time, an administrator or physician may want to know about a specific data point; I get them the answer.  How many patients had multiple bypass last month?  I can answer that question.  How many cardiac patients were prescribed statin drugs last month?  I can answer that question.  How many patients last week did NOT get all of their meds on time?  I can answer that question.   How many patients two days ago did not get appropriate ventilator weaning?  I can answer that question.  What are the chances for successful outcome of a specific surgery on a specific patient?  With the right data I can give you an answer.

Besides using our data internally for our own research, I also submit data to the National Registries such as the Society of Thoracic Surgeons and the American College of Cardiology.  These are databases that collect all of the data on all heart procedures done across the U.S. for use by researchers. There is also a Washington State database that I report to.

The data from these Registries are all held to a high standard.  Every few years we are audited for accuracy and completeness by outside firms.  We also participate in user groups that audit each other, in an attempt to make sure that we are all collecting the same data in the same way, so that we are comparing apples to apples across the nation.

For 20+ years this data has been mostly looked at by students and physicians, but the last couple of years some of this data has been coming to light in public places.  Because of the reliability of the data, Insurers are using this data to determine the quality of programs, and then recommending good programs to their clients.  And now, Consumer Reports is also using this data to report directly to the public (YOU!) so that you can choose the best programs for your needs.  Here are a couple of recent news items that demonstrate the work that our team puts out.

Blue Distinction Certification

Consumer Reports

Download Ratings published by CR

Be informed!!

Dog Pack attacks Gator in Florida

Be sure to read to the end - it's surprising!

At times nature can be cruel, but there is also a raw beauty,
and even a certain justice manifested within that cruelty.

The alligator, one of the oldest and ultimate predators, normally considered the "apex predator", can still fall victim to implemented 'team work' strategy, made possible due to the tight knit social structure and "survival of the pack mentality" bred into the canines.

See the remarkable photograph below courtesy of Nature Magazine.

Note that the Alpha dog has a muzzle hold on the gator preventing it from breathing, while another dog has a hold on the tail to keep it from thrashing.

The third dog attacks the soft underbelly of the gator.

Not for the squeamish...

 

GOTCHA!!!

Laughter is good for the soul...

 

Go get your Gators!!

The Nine Billion Names of God

The Nine Billion Names of God is a short story written by Arthur C. Clarke back in 1953.  It tells a story of a group of Tibetan Monks who hire IBM to install a mainframe to print out the Nine Billion Names of God; they believe that the world was created to discover the Nine Billion Names and that when the purpose of the world was completed the world would end.  I’m not going to tell you the rest of the story, you will need to find it out yourself.

But I don’t want to talk about the god of Tibetan Monks.  I want to talk about the God of the Judeo-Christian Traditions.  “God” is a generic term for a deity, or a power mightier than man who interacts with man. 

There is a ton of speculation about the origin of the word “god”. http://en.wikipedia.org/wiki/God_(word) has quite a bit on the subject. 

Personally, while I agree that the idea that our English “god” comes from the Germanic “gott”, I think that there is a deeper undercurrent to the meaning.  In the article on “Gad” in the Jewish Encyclopedia  it is mentioned that Gad is the name of the Canaanite god of Fortune, pronounced “gawd”.  And today, we wish each other “Good Luck”, or we say “how fortunate” or “how lucky” a person is, which is similar to saying “Gad be with you”.  Of course, “luck” is Loki, the Norse god of fortune, “fortune” is the Roman goddess “Fortuna”. 

Where this all started for me was a couple of years ago in discussion with an acquaintance about “God”.  This acquaintance was arguing that since I worship “God”, and Muslims worship “God”, and members of the Church of Latter Day Saints worship “God”, and many others claim to worship a “God” or “gods” that we should all just get together and worship “god” together, or better yet, just live and let live and let each one worship his or her own “god”: the way they want to.  I mean, like, you know, its all the same anyways, right man?

Well, no, it isn’t all the same, at least not to me.  See, my “God” requires me to worship him a certain way, and not only that, my “God” claims to be the only true “god” and claims that all others are not really “gods” at all.  If only one “god” said that then it would not be a problem, but practically every “god” around makes similar claims.  So now what do I do?

I have settled to my own satisfaction that the “God” I worship is, for me, the true “God”.  I’m not going to go into the details here and now, but for a good place to start you can try “Mere Christianity” by C.S. Lewis.

In the Hebrew Old Testament, or Tanakh, what is translated usually as “God” is usually the Hebrew for “Elohiym”; which may be loosely translated as “Mighty One”.  In Genesis 1:1 it says that Elohiym created the heaven and the earth.  Some scholars think that Elohiym is the plural for Eloahh and that Elohiym should be translated as "Mighty Ones"; there is great debate on this matter.  As near as I can tell it is all speculation and no-one really knows for sure; much data has been lost about the Hebrew language.  Although, considering how little we know about other languages from 4-5,000 years ago, we know quite a lot about the Hebrew language.  Interesting, is is not?

In Genesis 2:4 is the first appearance of what is sometimes called the Tetragrammaton, or The Name, or YHWH, or Jehovah, or as I read it, Yahuah (ya HOO ah).  For arguments on the pronunciation of the Tetragrammaton see the article in Wikipedia or in the Jewish Encyclopedia.

So, as someone else might say that their God is Allah, I will say that my Elohiym is Yahuah, because I believe, worship, and trust the Hebrew deity.  Depending on my audience, I may say “God Bless you” or I may say “Elohiym bless you”.  I may say “Praise the Lord” or I may say “HalleluYAH”.  However, in my own personal worship and prayer, I normally use the Hebrew names.  And I will rarely tell a person "Good Luck" or say how lucky someone is, because to me, that is a referent to a different god.

Word Study #1 - 27 March 2014

I’m going to start a series of posts, we’ll see how long I can keep them up.  Probably post once or perhaps twice a week.

I don’t claim to be a teacher, although I do enjoy teaching.  I prefer to call myself a student, because I like to study.  And, one of my favorite things to study is the Bible. 

What I am going to attempt to do is share some of the things I am learning.  I don’t claim these words to be the doctrine of any specific church or body of believers, although I think that some believers have held at least some of these beliefs at various times in history.  I don’t claim that any of these are correct, just that this is what I personally have come to believe.  Your mileage may vary.  Objects in the rearview mirror are closer than they appear.

The point of my study for the last couple of years was sparked by a person that saw me reading the Bible and asked me to tell him about what I was reading (he was functionally illiterate).  I began reading to him, and I don’t think I made it thru half a verse before he interrupted me to ask what “that” word meant.  I shortly realized that I wasn't certain myself what that particular word meant, which lead to the realization that most ‘religious’ people use buzzwords and that they have no idea of the real meaning behind those words.  This is true of many groups; as a computer geek I can use words that sound big and impress many, but usually I’m careful to know what II'm talking about.

One of the first things that I had to learn was that the Bible wasn't written by an American, or an English speaking person, or even a member of modern western civilization.  For the most part the Bible was written by members of a backwater tribe of middle eastern nomads over a period ending no later than 2,000 years ago and beginning perhaps 3,000 years before that.  The closest that I can imagine comparing them to is the indigenous North American peoples such as the Sioux or the Apache or the Dakota people.  They owned no property.  The family was the nucleus of the society.  Survival of the family came before anything else.  They didn't go to the supermarket or McDonald’s when they got hungry, in fact, for much of their history farming was unusual.  They hunted and gathered.  As nomadic shepherds, they lived from one good grazing spot to another.

The ancient Hebrew language reflected that lifestyle.  It is full of words that describe the concrete day to day struggle for life   One example:  The past is known, the future is unknown.  The Hebrew lived his life with his past always before him, he could see it.  The future was behind him, it was unknown.  This is a very fundamental change in outlook from what the modern day member of western civilization has. 

How ya doin’?

Let me introduce myself.  I’m Earle; my wife’s name is Sierra.

I’m chronically ill.  I’m obese, I have high blood pressure, high cholesterol, and diabetes.  Sierra is the healthy one in our family.  She almost never goes to the doctor, is rarely sick, and eats a healthy, balanced diet, while I’m the original junk food junkie.

On October 2, 2013, Sierra had a massive stroke.  Her blood pressure was off the scale (over 300), and she burst blood vessels in her brain.  She was airlifted to Harborview Medical Center, where she underwent emergency surgery to remove part of her skull and cauterize bleeding blood vessels.  Testing revealed evidence that she had long standing problems with high blood pressure.  As one surgeon said, getting your blood pressure tested once a year is not diagnostic, it doesn't tell you anything except that at that particular moment you do or don't have high blood pressure.

There are several ways to find out if you have high blood pressure.  One is to go see doctor.  That is what Sierra did, once every couple of years, and her test results were always normal or “pre-hypertension”, 120’s to 130’s.  A better way is to use the free testing machine that is available in many stores.  Every time you walk past one, stop and sit down for a few minutes and test your pressure.  If your top number is over 135 more than once, you need to check with a doctor, sooner rather than later.  They may not do anything until it is over 140, but you need to get busy controlling it.  Another way is to spend $15-$20 and get an inexpensive automatic wrist cuff blood pressure monitor.  They are not terribly accurate, but if you take it with you when you see your doctor or when you use a tester at the store you can compare readings, and if you test yourself daily at the same time, you should be able to keep an eye on your blood pressure.

Sierra came out of the ICU 10 days later paralyzed on the left side, but able to speak and see and hear and comprehend what was happening.  Good thing too.

You see, Sierra and I had only been married a bit over 2 years.  Because of my health issues, when we were married I made sure that Sierra was listed as my next of kin, as the beneficiary on my policies, etc.  But we hadn’t worried too much about her paperwork, after all, she is healthy!  And Sierra had a legal name change when we got married (her name was not ‘Sierra’), and much of her paperwork still carried her birth name on it.

Sierra recovered enough to be able to give me a Durable Power of Attorney, to file paperwork to make me her beneficiary on her policies.  We started getting her name changed on all of her paperwork, and adding my name onto her accounts.

After a couple of months, Sierra passed away on December 19, 2013.  As I write this, I have had a month of working on finishing up all the paperwork.  I’m still finding things that need to be taken care of, but I have all the paperwork that I need to complete it.

So why am I writing this?  Like many people, we had not adequately planned for the future.  Almost daily we passed a sign on our street advertising “Aging Options”.  We joked with each other about what kind of options there are, after all, you grow old and you die, right?  And we were planning on going together, one wasn’t supposed to leave without the other.  We had light conversations about what to do if something happened, but we never sat down and looked at “what if” scenarios.  Sierra was barely 51, I’m a couple years older, we are still young.  We don’t need to worry about this stuff right now, plenty of time later.  And we both knew better; we both had attended, separately and together, seminars put on by our employers dealing with this subject.  We had even talked to her parents about putting together a plan (they did).  But we never did anything ourselves!  We talked about doing something (“next Saturday we should sit down and talk…”) but something else always came up that was more important at the time.

We were, however, prepared for one thing.  We knew where she was going when she died; we knew that she was going to heaven, to be with her Master and Savior.  You see, Sierra had repented of her sins and asked Jesus to be her Master.  The Bible says that “All have sinned and fallen short”.  All of us have broken at least one of the 10 Commandments.  All of us have at some point in our lives considered some “thing” to be more important than God.  All of us have worshipped something other than God, whether it was money or things or other people.  Most of us have not shown honor to our parents at least once.  Most of us have not committed murder, but Jesus said that the intent of the law included anger, and most of us have been guilty of that.  Many of us have not committed adultery, but Jesus said that the intent of the law covered lust, and there are few who can say that they have never lusted after another.  Do I need to continue?  Who has not coveted what your neighbor has?  Or stolen a pencil?

Sierra had repented of her sin.  This is more than just saying “I’m sorry” and paying penance.  There is no penance great enough to pay for any sin other than a life.  Yes, God demands that you pay for your sin with your life.  Death is the penalty for sin.  Repentance is admission of your sin to God, and a determination on your part that you will not commit that sin again.  And when you repent, you can accept the gift that Jesus gave of his life.  Jesus had no sin, the only person to have ever lived in complete obedience to the commandments.  And he died to pay the penalty for our sin, but ONLY if you accept the gift.  And the gift is a conditional gift.  The Bible says that we are slaves to sin, but when we repent, we become slaves to Jesus.  And Jesus does not accept grudging slaves, he only accepts willing slaves, slaves that obey him out of love.

Sierra didn't have a religion.  She had a relationship with Jesus.  She loved Jesus, and wanted to please him more than anything else.  She wanted to please Jesus more than she wanted to please me, more than she wanted to please herself, more than life.  She was looking forward to being with Jesus.  Some people have the idea that heaven will be boring.  Sierra knew that heaven would be exciting, because she would get to be with the one person who she loved more than anything else.  She was looking forward to going home.

Sierra loved “Action Points”; here they are:

1.  Check your blood pressure.  And I would add, check your cholesterol and hbA1c.  Many drugstores now offer the blood test for as little as $35 each.  The Cholesterol and hbA1c tests give you a summary of the condition of your blood, for most people once a year is sufficient unless those tests show a problem.  Personally, because of my health, I get mine tested every 3 months or so.

2.  Sit down with your spouse or significant other or son or daughter or whoever will need to make decisions should you become incapacitated.  Make sure that they are OK with those decisions.  Write a document that details what you want done under certain circumstances.  I was totally blind-sided when I discovered that I needed to make decisions for Sierra when she was in a coma, and I just “winged” it.  Not a good place to put someone.  Sit down with them, talk about it, write it out.  Make sure that they understand and are OK with your instructions.  Perhaps you do not want extraordinary measures done to keep you alive.  Will they be OK with telling the doctor to “pull the plug”?  If you are incapacitated then you need to be confident that the person you designate will follow your instructions.

3. Have you written and notarized a Durable Power of Attorney?  A “fill in the blank” form is available online, just search for the one for your state.  Sierra’s employer required a specific one for their files, we did that and also the state generic one.  And it must be notarized.  We had to have a notary come to Sierra because she could not travel, that cost $60.  But you can get a form notarized for $10 if you go to your bank, or to a mailbox store, sometimes free from your employer.  They usually charge once per document, so if you make 3 identical copies of the Durable Power of Attorney at one time they will charge you one fee.

4.  Review your insurance policies.  Have you updated your beneficiaries?

5.  Review your emergency contacts.  Sierra’s employer called her brother, who then called me when she had her stroke, because I wasn't the emergency contact.

6.  Get your life right with God.  A good place to start is by reading the Gospel of Mark and the book of Romans.  You can read these online at www.biblegateway.com or at https://net.bible.org.  Find a believer, visit a church.  Talk to me.

The Hammer, the File, and the Furnace: 3 viewpoints

Isa 48:10  "Behold, I have refined you, but not as silver; I have tested you in the furnace of affliction.”

Samuel Rutherford in 1637 wrote in various of his “Letters”:

  Oh, what owe I to the file, to the hammer, to the furnace of my Lord Jesus! who hath now let me see how good the wheat of Christ is, that goeth through His mill, and His oven, to be made bread for His own table. Grace tried is better than grace, and it is more than grace; it is glory in its infancy. I now see that godliness is more than the outside, and this world's passments and their buskings. Who knoweth the truth of grace without a trial? Oh, how little getteth Christ of us, but that which He winneth (to speak so) with much toil and pains! And how soon would faith freeze without a cross! How many dumb crosses have been laid upon my back, that had never a tongue to speak the sweetness of Christ, as this hath! When Christ blesseth His own crosses with a tongue, they breathe out Christ's love, wisdom, kindness, and care of us. Why should I start at the plough of my Lord, that maketh deep furrows on my soul? I know that He is no idle Husbandman, He purposeth a crop. O that this white, withered lea-ground were made fertile to bear a crop for Him, by whom it is so painfully dressed; and that this fallow-ground were broken up!

Think therefore of the Lord, as of one who cometh to woo you in marriage, when ye are in the furnace. He seeketh His answer of you in affliction, to see if ye will say, Even so I take Him… Then let our Lord's sweet hand square us and hammer us, and strike off the knots of pride, self-love, and world-worship, and infidelity, that He may make us stones and pillars in His Father's house (Rev. 3:12).

…ye take it as the mark of a lawfully begotten child, and not of a bastard, to be under your Father's rod. Till ye be in heaven, it will be but foul weather; one shower up and another down. The lintel-stone and pillars of the New Jerusalem suffer more knocks of God's hammer and tool than the common side-wall stones…

...Years and months will take out, now one little stone, then another, of this house of clay; and at length time shall win out the breadth of a fair door, and send out the imprisoned soul to the free air in heaven. And time shall file off, by little and little, our iron bolts which are now on legs and arms, and outdate and wear our troubles threadbare and holey, and then wear them to nothing…

                                      **********************************

A.W. Tozer, in "The Root of the Righteous”, Chapter 38, writes

 “It was the enraptured Rutherford who could shout in the midst of serious and painful trials, 'Praise God for the hammer, the file and the furnace.'

“The hammer is a useful tool, but the nail, if it had feeling and intelligence, could present another side of the story. For the nail knows the hammer only as an opponent, a brutal, merciless enemy who lives to pound it into submission, to beat it down out of sight and clinch it into place. That is the nail's view of the hammer, and it is accurate except for one thing: The nail forgets that both it and the hammer are servants of the same workman. Let the nail but remember that the hammer is held by the workman and all resentment toward it will disappear. The carpenter decides whose head shall be beaten next and what hammer shall be used in the beating. That is his sovereign right. When the nail has surrendered to the will of the workman and has gotten a little glimpse of his benign plans for its future it will yield to the hammer without complaint.

“The file is more painful still, for its business is to bite into the soft metal, scraping and eating away the edges till it has shaped the metal to its will. Yet the file has, in truth, no real will in the matter, but serves another master as the metal also does. It is the master and not the file that decides how much shall be eaten away, what shape the metal shall take, and how long the painful filing shall continue. Let the metal accept the will of the master and it will not try to dictate when or how it shall be filed.

“As for the furnace, it is the worst of all. Ruthless and savage, it leaps at every combustible thing that enters it and never relaxes its fury till it has reduced it all to shapeless ashes. All that refuses to burn is melted to a mass of helpless matter, without will or purpose of its own. When everything is melted that will melt and all is burned that will burn, then and not till then the furnace calms down and rests from its destructive fury.

“With all this known to him, how could Rutherford find it in his heart to praise God for the hammer, the file and the furnace? The answer is simply that he loved the Master of the hammer, he adored the Workman who wielded the file, he worshiped the Lord who heated the furnace for the everlasting blessing of His children. He had felt the hammer till its rough beatings no longer hurt; he had endured the file till he had come actually to enjoy its bitings; he had walked with God in the furnace so long that it had become as his natural habitat. That does not overstate the facts. His letters reveal as much.

“Such doctrine as this does not find much sympathy among Christians in these soft and carnal days. We tend to think of Christianity as a painless system by which we can escape the penalty of past sins and attain to heaven at last. The flaming desire to be rid of every unholy thing and to put on the likeness of Christ at any cost is not often found among us. We expect to enter the everlasting kingdom of our Father and to sit down around the table with sages, saints and martyrs; and through the grace of God, maybe we shall; yes, maybe we shall. But for the most of us it could prove at first an embarrassing experience. Ours might be the silence of the untried soldier in the presence of the battle-hardened heroes who have fought the fight and won the victory and who have scars to prove that they were present when the battle was joined.

“The devil, things and people being what they are, it is necessary for God to use the hammer, the file and the furnace in His holy work of preparing a saint for true sainthood. It is doubtful whether God can bless a man greatly until He has hurt him deeply.

“Without doubt we of this generation have become too soft to scale great spiritual heights. Salvation has come to mean deliverance from unpleasant things. Our hymns and sermons create for us a religion of consolation and pleasantness. We overlook the place of the thorns, the cross and the blood. We ignore the function of the hammer and the file.

“Strange as it may sound, it is yet true that much of the suffering we are called upon to endure on the highway of holiness is an inward suffering for which scarcely an external cause can be found. For our journey is an inward journey, and our real foes are invisible to the eyes of men. Attacks of darkness, of despondency, of acute self-depreciation may be endured without any change in our outward circumstances. Only the enemy and God and the hard-pressed Christian know what has taken place. The inward suffering has been great and a mighty work of purification has been accomplished, but the heart knoweth its own sorrow and no one else can share it. God has cleansed His child in the only way He can, circumstance being what they are. Thank God for the furnace.”

                                         ***********************************

When first I read of Rutherford's "Oh, what owe I to the file, to the hammer, to the furnace of my Lord Jesus!" My first thought turned to my experience in forging steel.  Heating it to the right temperature to make it pliable, but careful not to burn it.  Our Master knows (none better!) the proper temperature to take the temper, the pride out of us.  He knows just how hard to strike the hot metal with His hammer on His anvil, and forge us into a useful tool.  How to quench us and then file us and sharpen us for the day of battle, then heat us again in His furnace to put His own temper into us, resulting in a sword that fits His hand and His purpose.

But we have the volition to exclude ourselves from that process.  We can jump out of the furnace.  In Daniel chapter 3, Nebuchadnezzar gave Shadrach, Meshach and Abed-nego the chance to escape the furnace in verse 15, and in verse 16 they refused the opportunity.  Were they men of God when they refused to bow to the image?  Of course.  Can you imagine how much stronger their faith in God was when they came through and out of the furnace?

I think that those who choose to walk in the Way of the Master will each find their own version of the file, the hammer, and the furnace.  There is no doubt that those who choose to walk in this way will meet these tools, and they will be tailored by the hand of the Master who loves us.