I need to generate a 6 month rolling PPM formula and I have no idea where to
start. It needs to automatically select the last six months of data and
calculate it into PPM accurately. any help would be greatly appreciated.

Sure, I can help you with that!

First, let's start with the formula for calculating PPM:

PPM = (defective units / total units) x 1,000,000

To generate a 6 month rolling PPM formula, you'll need to follow these steps:

1. Organize your data: Make sure your data is organized in a table with columns for the date, total units produced, and defective units.
   
2. Calculate the total units produced: Use the SUM function to add up the total units produced for the last six months. You can use the SUMIFS function to specify a date range. For example:
   
   
   Formula:

   =SUMIFS(B:B, A:A, "="&TODAY()-180, A:A, "<="&TODAY()) 

   This formula will add up all the values in column B (total units produced) where the date in column A is within the last 180 days (6 months).
   
3. Calculate the defective units: Use the SUM function again to add up the defective units for the last six months. For example:
   
   
   Formula:

   =SUMIFS(C:C, A:A, "="&TODAY()-180, A:A, "<="&TODAY()) 

   This formula will add up all the values in column C (defective units) where the date in column A is within the last 180 days (6 months).
   
4. Calculate the PPM: Use the PPM formula above to calculate the PPM for the last six months. For example:
   
   
   Formula:

   =(C2/B2)*1000000 

   This formula will calculate the PPM for the current row, assuming that the total units produced are in column B and the defective units are in column C.
   
5. Repeat for each row: Copy and paste the formulas from steps 2-4 for each row in your table.
   
6. Set up a rolling range: To make the formula "rolling," you'll need to adjust the date range in steps 2 and 3 to always include the last six months of data. One way to do this is to use a dynamic named range. Here's how:
   
   • Select the date column in your table (column A)
   • Go to the Formulas tab and click on "Define Name"
   • In the "New Name" dialog box, enter a name for your dynamic range (e.g. "LastSixMonths")
   • In the "Refers to" field, enter this formula:
     
     
     Formula:

     =OFFSET(Sheet1!$A$2, COUNTA(Sheet1!$A:$A)-1, 0, -180, 1) 

     This formula will create a range that starts at the last non-blank cell in column A and extends up 180 rows (6 months).
7. Update the SUMIFS formulas: In steps 2 and 3, replace the date range criteria with the named range you just created. For example:
   
   
   Formula:

   =SUMIFS(B:B, A:A, "="&MIN(LastSixMonths), A:A, "<="&MAX(LastSixMonths)) 

   This formula will add up all the values in column B where the date in column A is within the LastSixMonths range.

That's it! Your PPM formula should now be calculating the last six months of data and updating automatically as new data is added. Let me know if you have any questions or need further clarification.

you'll need to send more data

or send me a copy of the file direct
--
Greetings from New Zealand

"Rachel" wrote in message
...
I need to generate a 6 month rolling PPM formula and I have no idea where
to
start. It needs to automatically select the last six months of data and
calculate it into PPM accurately. any help would be greatly appreciated.

The formula is generally the amount rejected/the amount shipped*1000000, with
an if then statement preceding that says IF(amount shipped=0,0. My data is
organized in columns with headers of the months april to december with the
amount shipped and rejected for each company under each month. So in total
theres nine months, but i need to build the formula to function even if more
months are added. I know its hard to explain, but im not very familiar with
this forum and i dont know how to post an attchment of the file for you to
take a look at.

"Bill Kuunders" wrote:

you'll need to send more data

or send me a copy of the file direct
--
Greetings from New Zealand

"Rachel" wrote in message
...
I need to generate a 6 month rolling PPM formula and I have no idea where
to
start. It needs to automatically select the last six months of data and
calculate it into PPM accurately. any help would be greatly appreciated.

Rachel
By "direct" I meant.... send me a copy of the file to my email address.

By the way.... the end result
is it going to be an average of the ppm's per month
or is it a recalculated ppm of "the total send" divided by "the total
rejected"
for the 6 months

Bill K

"Rachel" wrote in message
...
The formula is generally the amount rejected/the amount shipped*1000000,
with
an if then statement preceding that says IF(amount shipped=0,0. My data
is
organized in columns with headers of the months april to december with the
amount shipped and rejected for each company under each month. So in
total
theres nine months, but i need to build the formula to function even if
more
months are added. I know its hard to explain, but im not very familiar
with
this forum and i dont know how to post an attchment of the file for you to
take a look at.

"Bill Kuunders" wrote:

you'll need to send more data

or send me a copy of the file direct
--
Greetings from New Zealand

"Rachel" wrote in message
...
I need to generate a 6 month rolling PPM formula and I have no idea
where
to
start. It needs to automatically select the last six months of data
and
calculate it into PPM accurately. any help would be greatly
appreciated.