You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Excel how to populate

Advertisement

QUESTION: Hey Tom,

I am working on a template for another facility of ours to help them get TL9000 certified. For this, they request to have a workbook that they will be able to put in all of their data and have it so that at the end of each month it will automatically calculate the metrics they need to submit in order to be TL9000 certified. I have these formulas imbedded into a worksheet (the screenshot I sent you). What I do not know how to do is to make it so that excel will automatically know how to calculate these formulas at the end of each month- as well as keep a history of the monthly data. I want to make it simple. I would like to possibly have a drop down box to be able to select the date and the category of the metric I’d like to see for that month( for example, categories would be NPR, FRT, OFR- in the screenshot). Is there a way to do this? I’m not sure how to go about this, or how to set it up in a way that makes it simple for everybody. Any suggestions?

Attached is a screenshot of the excel spreadsheet of the metrics that need to be calculated. This needs to be done for each month. This is just to give you a better idea of what I need to do.

Thank you so much Tom, I appreciate your help!

Kathryn

ANSWER: Kathryn,

Formulas should update each time the workbook is calculated. So the challenge would be to write the formulas to assess a specific month and as you stated, this month should be selectable.

So you could make a dropdown using the list option in data validation. This could just have a list of the months.

You could use a dedicated column to use this month in formulas to produce more complete critical boundaries. An example would be to calculate the first of the month for the given year and the last day of the month for the given year.

then as an example, you want to count the number of rows that have a date for the selected month. I would assume you are using excel 2007 or later so you can use the new COUNTIFS (or SUMIFS) formulas which allow specification of multiple criteria.

so say M2 has July, 1, 2013 and M3 has July 31, 2013 calculated from the selection in your dropdown. These are stored as actual dates.

then the formula for the metric would be

countif(Sheet1!F:F,">="&M2,Sheet1!F:F,"<="&M3)

so this assumes the rows you want to count have a date in column F. So this will count the dates that occur in July 2013. Dates greater than or equal to the first day in July and less than or equal to the last day in July.

So these are the types of formulas you need to build.

Now you also said to retain past months data. In the approach I outlined, you can change the month and it will recalculate the data. This assumes all data is retained in a "database"

An alternative would be to build a separate sheet for each month, but each month would calculate against the data so they are always live. Alternately, you could calculate for the month, then copy that sheet when the month has ended and then copy and paste values to overwrite the calculated amounts with the amounts calculated. This would make the sheet static after the month has passed - but making it so would be a manual action.

As far as categories of metrics, then perhaps you could have a column dedicated to identifying metric membership for each row and then if you wish to see anything besides all the metrics, you could apply a filter using the membership column.

So there are some ideas based on your question and image. But let me emphasize again that formula will update everytime the sheet/workbook is recalculated so you need to design your formulas so that the calculate across the appropriate period. Obviously for the current month, they would change as more data is added during the month and after all the data was entered, the results for the month would be displayed.

Is that the type of information you were looking for?

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Thank you, that really helped a lot! I would like to ask you another question as well. I am trying to put together some IF statements in my spreadsheet. I have 3 columns. The first is Customer Request Date, the second is Date Delivered, and the third is CRD's Met. Right now I have an if statement in the CRDs Met cells. I have =IF (E11<J11,"0","1")... essentially saying if Customer Request Date is less than the Date Delivered, insert a 0.. if not, insert a 1. It works perfectly fine, but I would like to add another IF statement. If the customer request date is a different month than the date delivered(i.e. Customer Request Date 2/30/13 Date Delivered 3/2/13) I would like an N/A inserted in the cell instead of a 0 or 1. How would I go about adding that to my formula?

I also need help with formulas that can grab data from a certian period of time and track it. For example, I am creating a template currently, and now I need to come up with the formula for my NPR ... which is the # of Normalization Units Shipped from period 0 - 12... (if calculating NPR for March 2013, it's units shipped from March 2012- Feb 2013)... There is a column for the # of units... but how to do make the formula so that it knows how to grab data from period 0-12?

I really appreciate the help Tom!

Thank you,

Kathryn

ANSWER: Kathryn,

If the months could match, but be a year apart, then this

=IF(OR(MONTH(E11)<>MONTH(J11),YEAR(E11)<>YEAR(J11)),NA(),IF(E11<J11,"0","1"))

If all dates will be in the same year then

=IF(MONTH(E11)<>MONTH(J11),NA(),IF(E11<J11,"0","1"))

If you are using excel 2007 or later, then they introduced the functions SUMIFS and COUNTIFS

note that the S on the end makes it a different function the SUMIF and COUNTIF. These functions support multiple criteria.

So if I had a date in say M1 and my date column was B:B, then I could count the rows for a period of 12 months with

=COUNTIFS(B:B,">="&DATE(YEAR($M$1),MONTH($M$1)-12,1),B:B,"<"&DATE(YEAR($M$1),MONTH($M$1),1))

if you wanted to sum the values in column C for the same rows

=SUMIFS(C:C,B:B,">="&DATE(YEAR($M$1),MONTH($M$1)-12,1),B:B,"<"&DATE(YEAR($M$1),MONTH($M$1),1))

So I believe that should show you how to do what you described.

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: The first one worked great! As far as the second I think I didn't explain it clear enough. Thats my bad!

If I wanted to grab a range of data over a 12 month period of time and sum it for me, how would I write that formula? This is an example of what I need to do-If I was reporting for March 2013 I would need to sum up all of the units delivered from March 2012- February 2013 (so total the units from 12 months prior to the reporting month). Some how I need excel to know to grab that data from only that time frame.

Thanks Tom!!

Kathryn,

If you put any date in March in M1, then the formulas I gave you would address the values from March 2012 - February 2013.

I assumed column B of the same sheet held the date of the data in each row.

If you want to count the rows, then

=COUNTIFS(B:B,">="&DATE(YEAR($M$1),MONTH($M$1)-12,1),B:B,"<"&DATE(YEAR($M$1),MONTH($M$1),1))

That would count the rows in that time period

then assuming values you want to sum are in column C the next formula would sum the values in column C for dates that fell with the specified range of dates (March 2012 - February 2013

If you change the date in M1, then the formula will address the preceding 12 months as you showed with your example dates.

So I believe I understood what you were asking and provided a tested solution to what I felt I understood. Hopefully you can adapt it to your actual data.

--

Regards,

Tom Ogilvy

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.