LinkBack URL
About LinkBacks
I have 10 items which would be 100%
I have a date range 6-1 to 6-5
Based on the date range, I need a formula that will calculate the percentage.
I included simple example with expected results based on date range if someone would assist.
Thank you,
I have 10 items which would be 100%
I have a date range 6-1 to 6-5
Based on the date range, I need a formula that will calculate the percentage.
I included simple example with expected results based on date range if someone would assist.
Thank you,
Does it come from same example?
https://www.excelforum.com/excel-for...ml#post5357415
Quang PT
In your example, why is 6-3 to 6-5 20%? I only see one entry that has a date between 6-3 to 6-5 for Tier B, and that is row 11. Wouldn't that also be 10%?
That is correct, I meant to put 6-5 as well so that there are two of that entries within that range.Originally Posted by Gregb11
I entered the date range in cells N4 and N5. So the formula you could enter into N7 is this:
=COUNTIFS(Table13[TimeStamp],">="&N4,Table13[TimeStamp],"<="&N5,Table13[Tier],L7)/COUNTIFS(Table13[Tier],L7)
IT WORKED!!
How ever when I tried it using my real scenario it yelled at me with error. The formula entered is referencing another sheet:
=COUNTIFS(Westside!,West[TIME STAMP],Report!">="&B1,WESTSIDE!,West[TIME STAMP],Report!"<="&B2,REPORT!,A18/COUNTIFS(REPORT!,A18))
Answer should be in the "Report" sheet of B17 Tried different version of the formula but the above is what I thought it should look like.
A18 of the "Report" sheet is a helper cell that I substituted with your original formula of "Tier" B
Last edited by rogrand; 06-30-2020 at 02:15 AM.
It's hard to know what you're trying to do now based on your data. There's NO data under the Time Stamp column nor under the "Trained and device..." column. Plus, since you're referencing a table, you shouldn't need to reference the sheet name. Also, it looks like you have commas in your formula where you shouldn't.
Good Morning Gregb11,
Sorry if I am making it difficult. Initially I created a simple spreadsheet to get some direction so I can replicate it using same thought proccess . Needing the same result under the posted spreadsheet which I though I could handle the different conditions.
B17 under "Report" sheet is the intended cell.
As with your example, I was targeting the "B" Tier column, however, since in my original spreadsheet I have A, B, and C formula, it would not work as per the statements that refer to COUNTIFS(Table13[Tier],L7) where L7 represents only "B". Hence, I created a helper cell "Yes" so it can find any item under the the column J that has a "Yes". Please advise where the formula needs to be revised if it has a logical order using your formula as my template.
Thank youm
This is confusing because there IS NO value of "YES" in column J.I created a helper cell "Yes" so it can find any item under the the column J that has a "Yes"
There's also nothing under the Time Stamp column.
Maybe you could put in some data and show the results you expect based on the sample data - that might make it more clear for me.
Lastly, you have a circular reference under column K AND column L. These formulas are referring to themselves, which it shouldn't.
That all being said, I tried putting something together that I THINK might work or at least can be a start. I mocked up some data myself and attached the file so you can see the data I entered on sheet "Westside". On the "Report" sheet, in B17 I entered this formula:
=COUNTIFS(Table14[Tier],"A",Table14[Trained and device setup complete?
Yes],Report!$A$18,Table14[Time Stamp],"<="&B$2,Table14[Time Stamp],">="&B$1)
Take a look and let me know of any questions/issues.
Last edited by Gregb11; 06-30-2020 at 06:39 PM. Reason: removed my first question
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks