FORMULA BASED ON DATE

[mcintoshmc]

I HAVE A FORMULA THAT WILL GIVE ME THE PERCENTAGE OF PEOPLE THAT ENROLL INTO SCHOOL. IT'S BASED ON THE NUMBER OF INTERVIEWS, AND OUT OF THOSE INTERVIEWS, HOW MANY OF THOSE ENROLL.

THE SPREADSHEET STARTS ON 7/18/07 BECAUSE THAT IS WHEN I STARTED IT. SO, RIGHT NOW IT WILL SHOW ME THE TOTAL PERCENTAGE FROM 7/18 TILL THE DATE OF MY LAST INTERVIEW, BEING THAT I INTERVIEW PEOPLE EVERYDAY, IT WILL SHOW 7/18 TO TODAY(). WHAT FORMULA CAN I USE THAT WILL SHOW ME THE PERCENTAGE FROM WHATEVER DATE I WANT? FOR EXAMPLE:

A2 WILL HAVE THE DESIRED DATE, AND A3 WILL ALSO SHOW ANOTHER DATE. A4 WILL GIVE THE PERCENTAGE.

A2- 8/1/07
A3- 8/24/07
A4- %

 

[Norie]

I have a caps lock button on my computer, and I normally turn it off when posting to forums.

Seriously though what percentage are you actually looking for?

If it's the percentage of pupils enrolled versus those who don't what determines whether or not somebody had enrolled/not enrolled?

 

[Scott Huish]

A2 & A3 are the start end date you want?

Since you didn't provide any sample other than these dates, I'm guessing you have a date, then a column with the number of interviews on this date, as well as the number of people who enrolled on these dates, is this correct?

 

[mcintoshmc]

ok, column F is named enrolled. Cell F1 will say either "yes" or "no" student themselves will determine whether they will attend the school I work for. Each day when I interview a student I will add them to the list. Eventually there will be hundreds of students on the list. For example, if I interview 150 students between 7/18/07 and 12/1/07, and only 75 of them enrolled into the school, it will show an enrollment rate of 50%. I want to be able to show what the percentage would be for the month of August for example. Right now I can see what the percentage is from 7/18/07 to 8/24/07. What if I wanted to see what it was from 8/1/07 to 8/24/07?

I want to have two cells that will show both dates, and have the third cell to show the enrollment percentage during that time period.

A1: 8/1/07
A2: 8/24/07
A3: %

 

[Scott Huish]

I used Column B for the dates, change as necessary, but note that you can't use full columns with SUMPRODUCT:

=SUMPRODUCT(--(B1:B10000>=A1),--(B1:B10000<=A2),--(F1:F10000="Yes"))/SUMPRODUCT(--(B1:B10000>=A1),--(B1:B10000<=A2))

Format as percent.

 

[mcintoshmc]

what goes in A1 and A2 if you used column B for the dates?

 

[mcintoshmc]

Oh nevermind, I got it..

 

[mcintoshmc]

It worked perfectly--thanks