Learned: Spreadsheet calculations that update totals when filtering!

Ever created a spreadsheet with calculations on a long list, and then experienced frustration when filtering your list because the calculated totals don’t change when cells are hidden? Yeah me too*.
I did some searching and found these two macros that directly solve the problem. You’ll need them both, and remember to save your worksheet in a macro friendly format.

This function only reads visible cells:

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
'Example =SUM(G15:G30) becomes =SUM(VIS(G15:G30))

      Dim Cell As Range
      Application.Volatile
      Set Vis = Nothing

      For Each Cell In Rin
         If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
            If Vis Is Nothing Then
               Set Vis = Cell
            Else
               Set Vis = Union(Vis, Cell)
            End If
         End If
      Next Cell
End Function


This function replaces COUNTIF to only tally visible cells:

Function COUNTIFv(Rin As Range, Condition As Variant) As Long
'Same as Excel COUNTIF worksheet function, except does not count
'cells that are hidden

      Dim A As Range
      Dim Csum As Long
      Csum = 0

      For Each A In Vis(Rin).Areas
         Csum = Csum + WorksheetFunction.CountIf(A, Condition)
      Next A

      COUNTIFv = Csum
End Function

Happy spread-sheeting, if there is such a thing.

* I’m currently working on a huge VOC project documenting user’s pain-points, that automatically generates heat maps of the pain-points as they apply to parts of the end to end process.

Advertisements
Posted in Interactive Conversation | Tagged | Leave a comment

Link: The 5 Ways to Know if Your Product Idea is a Winner

I recently found this post by Jim Semick, Founder/Chief Strategist at productplan.com.

The process he describes nearly parallels the UCD process (obviously on a more market-based approach). and why shouldn’t – there’s great power in leveraging your user’s input.

Thousands of new products launch every month. Yet only a fraction of those get enough traction to be considered successful.

Of course, there are the exceptions — the breakout successes that we all hear about: Snapchat, Uber, and of course Pokémon Go.

Even though that’s not likely to be your product, you can still knock it out of the park. But how do you test market demand early to know if your idea is a winner?

Throughout my career I’ve helped launch a dozen successful software products including GoToMeeting, AppFolio, and ProductPlan. I have learned five powerful techniques that entrepreneurs use to learn whether their product will be successful — before they launch their product.

These methods won’t guarantee success, but will dramatically increase your odds. In my case, these techniques resulted in products that today now generate hundreds of millions of dollars in revenue yearly.

Read full article here…

Posted in Interactive Conversation | Tagged | Leave a comment

T-Shirt Sizes: Make good decisions quickly

I’ve been using the agile approach in one way or another now for the last eight years. There are so many different flavors of agile – but all rely on the team’s ability to make good decisions quickly.

Most teams need a method to evaluate, and fall to data that can be gathered and trusted, data like sales, or traffic numbers, or estimated hours. The trouble is, the real data can often lead to over-analyzing the problem when the goal is to evaluate a list of things relative to each other and move on to the task at hand.

Switching to a non-numerical system like T-Shirt sizing removes the implied precision of the numerical data, leaving the team free to think in a more abstract way about the size and complexity of a problem, and how those problems relate in size and complexity to other problems. This is important because we want these people spending their time on creating the actual solutions rather than arguing about them.

Most teams I’ve worked with use a three-bucket system of “Small, Medium, Large”, it’s very fast but it can lack granularity. Others have opted for a five-bucket system of “Extra-Small, Small, Medium, Large, and Extra-Large”, it seems to be a little better at dealing with granularity, while still keeping the process quick.

Obviously, non-numerical scales are less granular, and while they do speed up the process of assessment, it can come at the cost of accuracy. I’ve found the people most concerned with a slight loss of accuracy are usually one or two levels up in the organization and unfamiliar with the agile approach. They may not yet see the direct benefits to moving forward quickly vs being able to argue with-a-doubt the reasons a decision was made.

Get ‘em onboard. There are many team building exercises to help everyone understand the power of group thinking and quick decisions. One way is Leveraging a group for quick decisions, another is guessing the number of jelly-beans in a jar using the average of all guesses from a sizeable group that is surprisingly accurate.

Initially, to have T-Shirt sizing work smoothly, it’s my opinion you will need an experienced scrum master walk them through it – until they get going. Once they do, everyone should see the benefits.

Posted in Interactive Conversation | Tagged

Cloudy Thoughts

At the request of our Product Manager, I recently took over ownership of the internal identity and access management application for SPH Analytics. My first action as PO was to understand the “state of the world” with the app, and then work towards a SWOT analysis that would ultimately form the product’s roadmap.
My research took me to all the standard places; interviewing developers, researching standards and security. I covered a-lot of ground learning how and why we got to where we are today.
Here’s where it gets a bit interesting, when I got to the external Threats part of the SWOT, my research continuously exposed the question of ROI when creating an identity and access management from scratch. Why do we need a team of people in-house reinventing the wheel, and scrambling to stay current on difficult things like:
  • Server Configuration / Security
  • Penetration testing
  • Support for Single Sign On
  • Platform agnostic support

When there are world class solutions out there with teams of people dedicated and up-to-date on the really difficult stuff. SPHA’s core specialty is doing analytics heavy lifting  to enable healthcare professionals the means to Take Action with distilled pertinent information.

Conclusion: It’s going to be a bit of a wait, but our next version of identity and access management app will use AWS or something that looks a-lot like it. Leaving us again to focus on our analytics game and empowering our users. I’m excited to see how different it will be to manage a product on an outside system.
Posted in Interactive Conversation | Tagged ,

Remember your audience.

I’m a fan of simple solutions, however getting that simplified solution out the door can be difficult. Often the hardest part is communicating the solution to the group creating the solution.  Here’s an example.

When crafting the experience that enabled our 2016 PQRS Client-Administrator user to create and save report templates for their physicians, we sifted through a sea of Centers for Medicare & Medicaid Services (CMS) rules and standards. Distilling that info down into a simple experience took many hours and iterations.

Happy with the approach, we then verified the approach with partner customers with a demonstration and walkthrough. Our Customers validated our approach and latched on to its simplicity as a way to take uncertainty out of the process and reduce the man hours to set up reports. Great news, a solution that solves some big problems, and is verified by a few actual customers – time to start building it.

After writing up all our stories acceptance criteria and attaching all related docs, we hit a brick wall in sprint zero with our offshore development teams. They understood the approach and felt they could build it but were having a hard time getting their heads around the bizarre CMS rules, ambiguous nomenclature and standards for PQRS reporting. It seemed pretty clear we were going to have a QA nightmare testing and correcting the solution if we didn’t provide clarity.

Our mistake? We had distilled the process and information for our clinically based customers – but had not considered all of our audience members – most importantly our non-clinically minded offshore team.

It took us a couple of tries, but we finally found success with a couple of diagrams that worked together to explain the true nature of the beast. (One visually describing what the UI needed to do, and one grid version of the same information – without the flow logic.) I realize it won’t mean much, but since these were difficult to come by, I feel the need to post them (The blog equivalent of putting a “big-win” test on the Fridge perhaps? )

PQRS-ui-flow.png

Ui flow of information & logic for 2016 PQRS Client Administrator report creation

PQRS-UI-flow 2.png

Information & logic for 2016 PQRS Client Administrator report creation

Posted in Interactive Conversation | Tagged , | Leave a comment

#BYOB – for Business?

I’m not talking about the BYOB of your college days – or even dragging your favorite Pinot Noir to that little place around the corner that has great eats but no license, I’m talking about Build Your Own Bootstrap  BYOB.

There are many many Bootstraps out there now, each with its own strengths. Using a bootstrap can be perfect for getting an idea out there fast, but less great when it comes to building brand or standing out from competitors.

That’s where the idea of BYOB comes into play. Making the leap from using a pre-made bootstrap to hunkering down and developing your own common component system – makes sense, but can appear time consuming. The payoff lies down the road, when you want to change design, direction or leverage your base bootstrap to another line of business – your time to market will be vastly reduced.

It can be a rocky road that requires extra time and planning from everyone:

  • A strong product roadmap
  • Extra planning/design to define controls and elements that can be repurposed
  • Robust database / API designs that allow all instances of your product to be extendable yet remain sandboxed from other instances
  • Good Product Management / Design feedback loop to iterate and refine BEFORE coding
  • Refined coding practices to build solid elements that are extensible and portable
  • Automated QA

Benefits:

  • Quickly retool an existing app / site, with reduced coding and QA
  • Offer user facing customization options
  • For SaaS – implementation time to market for new clients can be nearly instantaneous
  • Supporting all customers with a single codebase – bug fixes and upgrades benefit everyone

Realities:

  • You will need a single person or body to actively police all direction and code
  • No shortcuts, meaning absolutely no changes can make it to production unless they are 100% on on point and approved (No matter how crazy the deadline, or angry the customer)
  • All roadmaps must be as solidified as possible
  • All channels between the Ux Team, Product Team, and Development Team must remain open and unrestricted
  • All decisions need to be documented and available to everyone
Posted in Interactive Conversation | Tagged ,

Excel -Kill off #DIV/0!

Are you as sick of that #DIV/0! as I am?

Wrap it in this

=IFERROR(MyFormulaHere,0)

or if you want text instead then use

=IFERROR(MyFormulaHere,"N/A")
Posted in Interactive Conversation | Tagged