Rajesh_123
New Member
I have attached excel file for the same, thanks
I want to get percentage of last 20 records of "A" where rating is "C" and score greater than 2.5. I
- Thread starter Rajesh_123
- Start date
Somendra Misra
Excel Ninja
Hi Rajesh,
Can you explain percentage of what? Can you give the manual solution for the case described.
Regards,
Can you explain percentage of what? Can you give the manual solution for the case described.
Regards,
NARAYANK991
Excel Ninja
Hi Rajesh ,
Can you see if the result is correct ? I have used 2 helper columns.
Narayan
Can you see if the result is correct ? I have used 2 helper columns.
Narayan
Rajesh_123
New Member
Hi Narayan,
Many thanks for the reply. If all the twenty rows met the criteria, it should display 100%, but it is showing 105%, Any how we can change the formula . Can we get this using offset and index function?. Since I am going to use this on a large set of data, helper column might be complicated. What do yo think
Many thanks for the reply. If all the twenty rows met the criteria, it should display 100%, but it is showing 105%, Any how we can change the formula . Can we get this using offset and index function?. Since I am going to use this on a large set of data, helper column might be complicated. What do yo think
Last edited:
Rajesh_123
New Member
Hi Somendra,
Thanks for looking into this, please see the attached file.
Thanks for looking into this, please see the attached file.
NARAYANK991
Excel Ninja
Hi Rajesh ,
According to my arithmetic you have colored 21 rows , not 20.
Narayan
According to my arithmetic you have colored 21 rows , not 20.
Narayan
Rajesh_123
New Member
Hi Narayan,
hmmm..sorry...Is there any ways to show directly without any support column
hmmm..sorry...Is there any ways to show directly without any support column
Somendra Misra
Excel Ninja
@Rajesh_123
Check below array formula on your real data:
=SUMPRODUCT((T(INDIRECT("B"&LARGE(IF($A$3:$A$37="A",ROW($A$3:$A$37)),ROW(INDIRECT("1:20")))))="C")*(N(INDIRECT("C"&LARGE(IF($A$3:$A$37="A",ROW($A$3:$A$37)),ROW(INDIRECT("1:20")))))>2.5))/20*100
Confirm with ctrl+Shift+Enter.
Regards,
Check below array formula on your real data:
=SUMPRODUCT((T(INDIRECT("B"&LARGE(IF($A$3:$A$37="A",ROW($A$3:$A$37)),ROW(INDIRECT("1:20")))))="C")*(N(INDIRECT("C"&LARGE(IF($A$3:$A$37="A",ROW($A$3:$A$37)),ROW(INDIRECT("1:20")))))>2.5))/20*100
Confirm with ctrl+Shift+Enter.
Regards,
Rajesh_123
New Member
Thanks for your email, when I added a new column it became zero, I have also attached the file highlighed in blue...I want to find records between a data range, than has got score >2.5 and C. I have also entered a manual calculation of 75
Rajesh_123
New Member
Hi Nebu,
Could you please look into the excel file. now it is considering above the given condition.
Could you please look into the excel file. now it is considering above the given condition.
Somendra Misra
Excel Ninja
@Rajesh_123
Although you did not respond to my comment number 9, but below array formula seems to work just like you mention in the file you uploaded above.
=SUMPRODUCT((T(INDIRECT("D"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))="C")*(N(INDIRECT("E"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))>2.5)*(N(INDIRECT("B"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))>=J3)*(N(INDIRECT("B"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))<=J4))/25*100
Confirm with Ctrl+Shift+Enter.
Regards,
Although you did not respond to my comment number 9, but below array formula seems to work just like you mention in the file you uploaded above.
=SUMPRODUCT((T(INDIRECT("D"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))="C")*(N(INDIRECT("E"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))>2.5)*(N(INDIRECT("B"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))>=J3)*(N(INDIRECT("B"&LARGE(IF($C$3:$C$36="A",ROW($C$3:$C$36)),ROW(INDIRECT("1:25")))))<=J4))/25*100
Confirm with Ctrl+Shift+Enter.
Regards,
Rajesh_123
New Member
Hey I got your formula Misra, thanks a lot...Every thing is working fine, but It should not consider <2.5 in score column. It should ignore <2.5 in last 25 records while calcuation. please help
Misra, it should take only last 25 jobs between the dates, that has got >2.5hrs of estimation, column1 as A and column B C as said before
Misra, it should take only last 25 jobs between the dates, that has got >2.5hrs of estimation, column1 as A and column B C as said before
Somendra Misra
Excel Ninja
Rajesh_123
New Member
Hi Mirsa,
Pls see the attached xls fil..Now it is showing 92%, but it should be 100 since the 25 & 24 is less than 2.5. ie..it should take only last 25 whose score is greater than 2.5 and name A
Pls see the attached xls fil..Now it is showing 92%, but it should be 100 since the 25 & 24 is less than 2.5. ie..it should take only last 25 whose score is greater than 2.5 and name A
Somendra Misra
Excel Ninja
Rajesh_123
New Member
Thanks Misra, we are looking last 25 records for A where scores >2.5, and rating C between those dates, It should not consider the row that has scores less than 2.5. In my xcel two 1 and 1M has scores <2.5. ie, in this case it should be ignored. Sorry if i am not clear earlier.
Rajesh_123
New Member
Could yo please check this excel file and different tabs, this should be clear
Somendra Misra
Excel Ninja
@Rajesh_123
I am still unclear, in your data last 25 record has 3 data on row 11,12 & 13 which does not meet the condition, so percentage is 23/25 that is 88%?
Where is this wrong?
Regards,
I am still unclear, in your data last 25 record has 3 data on row 11,12 & 13 which does not meet the condition, so percentage is 23/25 that is 88%?
Where is this wrong?
Regards,
Rajesh_123
New Member
Hi Misra
Many thanks, for row 11 and 12 the rating is 1, so it should not consider that row for condition, the condition should check last 25 records of A and and score >2.5 and Rating C. ie, 11, 12, and 13 row score is <2.5, so the condition should take till row higlighted in green. xample: last 25 record means, last 25 where score >2.5
Many thanks, for row 11 and 12 the rating is 1, so it should not consider that row for condition, the condition should check last 25 records of A and and score >2.5 and Rating C. ie, 11, 12, and 13 row score is <2.5, so the condition should take till row higlighted in green. xample: last 25 record means, last 25 where score >2.5
Rajesh_123
New Member
Please see the attached one more example:
Row 17 to 21, does not belongs to "A", so now last 25 records of A means it is till excel row number 5 (row highligted in purple), becoz row number 6 score is <2.5
Row 17 to 21, does not belongs to "A", so now last 25 records of A means it is till excel row number 5 (row highligted in purple), becoz row number 6 score is <2.5
Rajesh_123
New Member
Records can be random, the formula should check for scores >2.5 for last 25 records of A (last 25 records means excluding scores <2.5) and rating should be C