• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advice on maintaining long formulas

D

dnessim

Member
Hi all,

I have been working on a data entry project in Excel. I have a Data Entry worksheet that has about 13 columns.

some of the headers are:

Indicator Name

Indicatory Category

Date

Goal

Facility

Unit

Unit Group

Numerator

Denominator

Base Rate

Calculated Rate


This worksheet will get very long since the user will enter several indicators for each month.

I have several other worksheets that get populated by this data automatically by using very long formulas. One of the worksheets is a Dashboard/Scorecard for these indicators.

On the dashboard I have the indicators listed in a column and the months/quarters in a row at the top.

If I want to print out the Numerator/Denominator carriage return Rate the formula would look like this”


( I know the month can be combined with the year using TEXT but I find this a little easier to manage)

=IF(SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*(YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9),Rate_Base)<>0,SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Numerator)))&"/"&SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Denominator)))&CHAR(10)&ROUND(SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Numerator)))/SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Denominator)))*VAE_Rate,2),"")


What makes this even more complicated is that I also have conditional formatting for Green, Yellow, Red based on the value of the Rate.

This must be in every cell and its very hard to maintain.


So even thought I can copy this formula horizontal the month will increment correctly , then I can copy vertical and the indicator names will increment correctly . I still find myself having to edit each cell because I cannot always drag this to adjunct cells.

There are several areas where I feel like I have created a nightmare to maintain.

Any advice to make this easier to maintain and manage?

What are my options.


Thanks

Dave
 
Instead of the initial If(complex sumproduct formula <>0

you could probably just use an Iferror(Formula, "")


eg:

=Iferror( SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Numerator)))&"/"&SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Denominator)))&CHAR(10)&ROUND(SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Numerator)))/SUMPRODUCT((MONTH(Entry_Date)=MONTH(BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G9)*(Facility=$F$3)*(Unit_Group=$H9)*(Denominator)))*VAE_Rate,2),"")


If might be worthwhile if you want to post a sample of your file so we can have a better look

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Hui,

That first if statement doesnt look for an error but it looks for a value in one of my dropdown lists on the data entry page. If the value is 0 then nothing should display. That would help a little but not really solve my problem of having to maintain this formula on a worksheet in aprox 300 cells.


I wanted to know if there are any other options.

This data is healthcare related so I cant post.

Thanks

Dave
 
Hi Dave ,


If at all you are going to be working on this workbook for some time to come , my suggestion is to do away with the entire structure , and redo all the formulae once and for all , using helper columns.


Long , overly complicated formulae only satisfy bloated egos ! Or point to a lack of clarity of thought.


There are very few formulae which cannot be simplified using helper cells / columns ; very few applications where the data cannot be structured properly ; a proper data structure eases the job of creating elegant formulae.


Any decent application should be created for the long-term , and trying to even understand , leave alone maintain a formula such as the one you have posted , three months from now , can only give nightmares.


If you can use VBA , then you can simplify such formulae by encapsulating their logic in a UDF , so that any change at a later date needs only the UDF to be changed ; none of the 300 cells or their formulae needs to be touched.


Narayan
 
Hi Narayan,

I do know some VBA and I have been torn as to redo this project using vba.

You are so right about trying to understand this formula in the future.

Thanks,

Dave
 
@Narayan,

Hi

I have sanitized a sample of my workbook. Can you help me to understand some of my options to manage such long formulas. Maybe an example of a UDF you suggested?

I wasn’t able to get a udf to do what I wanted. Is it possible for a UDF to change the color of a cell?


https://www.dropbox.com/s/if3gv9wbjplsxkt/ChandooExample.xlsm


Here is what I have .

The plan is to have users enter data only once, in the Data Entry worksheet and it will populate several other worksheets.

The user first selects an indicator, this does some vlookups and populates some of the columns for that specific indicator.

The user then fills in the numerator and denominator , and a monthly sir and a quartile sir. There are several user validations I need on this data entry, I can get to that later.


The CAR-Dashboard is just one example of how complex this has become.

The requirement is for the dashboard to display several different renditions of the data contained in the data entry ws.


So for the first indicator here is a suedo query as an example

IF Indicator Name = “CLABSI” AND MonthDate = “Jan” AND YearDate = 2013 AND Facility = “Carrollton” AND Unit Group = “ICU” then

Print this in the cell

Sum numerators & “/” & sum denominators & char(10)

QuartlySIR


So the formula to do this is something like this:

=IF(SUMPRODUCT((MONTH(Entry_Date)=MONTH($BZ$2))*(YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G3)*(Facility=$F$3)*(Unit_Group=$H3),Rate_Base)<>0,SUMPRODUCT((MONTH(Entry_Date)=MONTH($BZ$2))

*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G3)*(Facility=$F$3)*(Unit_Group=$H3)*(Numerator)))&"/"&SUMPRODUCT((MONTH(Entry_Date)=MONTH($BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G3)*

(Facility=$F$3)*(Unit_Group=$H3)*(Denominator)))&CHAR(10)&SUMPRODUCT((MONTH(Entry_Date)=MONTH($BZ$2))*((YEAR(Entry_Date)=$E$1)*(Indicator_Name=$G3)*(Facility=$F$3)*(Unit_Group=$H3)*(SIR))),"")


So this is just one “rendition” of the formula , since this is just for Jan 2013 , very similar is to calculate the same for quarter 1, 2 , 3 and 4 (see example)


Also some of the indicators don’t use the SIR column they would work off a rate/%

So it need to print to each cell

Sum numerators/sum denominators * rate Carriage Return

%rate


There must be a better way to accomplish this , any suggestions are welcome.

Thanks

Dave
 
Hi Dave ,


I have downloaded your file , and am overwhelmed by the whole thing !


I think if anyone else has to get involved in this cleaning up job , you will have to exercise a lot of patience , and answer several questions. My first one :


You have Entry_Date as a named range , which has a fixed set of dates ; you are using this named range as the source for the drop-downs in column C of the Data Entry tab. Thus , you have ensured that no date which is not in your named range can be entered in any cell of column C.


Consider the following segment of your formula in Q3 :


(MONTH(Entry_Date)=MONTH($Q$2))*(YEAR(Entry_Date)=$A$1)


You are entering the year , say 2013 , in cell A1 ; now there are dates such as 1/26/2013 , 2/26/2013 in row 2 ;


1. What is the basis for these dates ?


2. If A1 contains 2013 , does it follow that all these dates in row 2 will be for the 12 months of the year 2013 ? If so , can these dates not be derived using cell A1 as the year ?


3. Why are these dates having the day as 26 ?


4. The dates in the named range Entry_Date are 11/30/2012 , 12/1/2012 , 1/1/2013 and so on ; can these dates also not follow the same system as the dates in row 2 ?


If all of the above can be changed suitably , then the above formula segment can be changed to :


(Entry_Date=Q$2)


Not only will this shorten the formula a bit , it will also reduce the re-calculation time a bit.


This is just one question ! A lot of other questions will follow.


Narayan
 
Narayan,

Thanks so much for taking the time to download my file and help me.

Some more details that will help explain what my objectives are.

The CAR-Dashboard sheet is a legacy documemnt, meaning I cant change the layout and the the sheet should be read-only to my users, meaning they enter the data in the Data Entry ws and the dashboard will populate automatically. There are several other WS that will leverage the data as well but for our purposes we can focus on the Dashboard.

On the dashboard the dates are in months and quarters. When a user selects an indicator name, they select a month from the dropdown. All dates should be in months only.


1. What is the basis for these dates ?

the year 2013 is in Cell A1, this is really just used as a constant because all the months will be 2013, I thought this would be better than hardcoding the formula.


2. If A1 contains 2013 , does it follow that all these dates in row 2 will be for the 12 months of the year 2013 ? If so , can these dates not be derived using cell A1 as the year ?

yes, if there is a way to break the year up into months and quarters? can this be done?


3. Why are these dates having the day as 26 ?

this is arbitary and sloppy on my part, I think the way my formula is it wouldnt matter right?

What i want is all the indicators where name = "clabsi" AND date = month of january etc...

for example if month(jan 4, 2013) would return 1 and month(jan 26th, 2013) would return 1 right? I wish there was a way to just tell Excel I want the month and not have to deal with the day?


4. The dates in the named range Entry_Date are 11/30/2012 , 12/1/2012 , 1/1/2013 and so on ; can these dates also not follow the same system as the dates in row 2 ?


Again this is just me be sloppy because i have been working on so many iterations of this.

So my named range should really be text with Jan,Feb,March,April....

The user never cares about days, everything will be reported on by month and quarter and year.


If all of the above can be changed suitably , then the above formula segment can be changed to :


(Entry_Date=Q$2)


Not only will this shorten the formula a bit , it will also reduce the re-calculation time a bit.


Let me see if I understand what you are asking

say Q$2 contains 1/26/13


so if we queried my database using my current formula, wouldnt that bring back only records where entry_date - 1/26/2013

What I would want is all records where entry_date = Jan


This is just one question ! A lot of other questions will follow.


I sure hope that explains a littel bit. Let me know, and once again thanks!

Dave
 
Hi Dave ,


To start with , you can try this formula in Q2 , and copy it across :


=IF(MOD(COLUMN()-COLUMN($Q:$Q)+1,4)=0,"Qtr "&INT((COLUMN()-COLUMN($Q:$Q)+1)/4)&CHAR(10)&"SIR",DATE($A$1,COLUMN()-COLUMN($Q:$Q)+1-(INT((COLUMN()-COLUMN($Q:$Q)+1)/4)),1))


This will insert the dates Jan-1 , Feb-1 , Mar-1 in Q2 , R2 , S2 ,.... based on the year entered in A1.


Narayan
 
You must log in or register to reply here.
Back
Top