FORMULA BASED ON DATE

mcintoshmc

Active Member
Joined
Aug 10, 2007
Messages
265
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- %
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Aug 10, 2007
Messages
265
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

Format as percent.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,882
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top