Calculating a percentage based on rows above and below

Originally Posted by AliGW

OK - I sort of follow this, but have no clue how you get that percentage. Exactly WHICH rows are you counting for I14 and how is C21 relevant? I'm not quite getting it.

Talk me through the EXACT calculation step-by-step.

So in that example, R is on I14, 8 rows above so until I6 and I22 (not counting with I14 itself).

You last comment it works and it's giving the right answer, but could it be a coincidence? The Column to check and count is C, counting all the same that there is "B5", "B4", "B3", "B2" or "B1" withing the range (I14 and I22)

I'm almost coming up with an answer myself using:

=IF(I2="R", COUNTIFS(OFFSET(C2,-8,0,17,1), "B5")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B4")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B3")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B2")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B1")-1, "")/16

Don't know if it's more efficient or less than yours. Only downside is that I get #VALUE! on the empty rows (would rather your version which I could make it blank)

Originally Posted by AliGW

This is the bit I'm not getting.

I have asked you to talk me through the EXACT mechanics of that calculation. You still haven't done this and I'm afraid I do not fully undertstand what that calculation should be. If you'd just talk me through it, I could give it another go. Or you could highlight the cells in the workbook that need to be counted.

Sorry, would try do it but I just noticed where perhaps the problem is. "B5", "B4", "B3", "B2" or "B1" is a grading system of my own and this values and not regarding any value on cells B5 B4 B3 etc

If the value of I="R" then I take into consideration 8 rows above and 8 rows below the row where I="R", in these rows I check Column C and count how many of them have "B5", "B4", "B3", "B2" or "B1" and finally I divide this last number for the total of rows checked (which is 16)

Example in the spreadsheet:

I14="R" so I went and counted in Column C how many times do I have the values "B5", "B4", "B3", "B2" or "B1" through C6 and C22 (ignoring C14, as that is the row where I="R"). In this case it only happens once through C6 to C22 (value of C21 is "B5"). Now I divide 1, which was the total number of times that I had "B5", "B4", "B3", "B2" or "B1" and divide it by 16, the total number of rows checked giving me 0.0625 (6.25% chance of having a value "B5", "B4", "B3", "B2" or "B1")

Let's do the other example. I21="R". Now I'm going to check Column C 8 rows above and below row 21, so from C13 to C29 and this time the count of "B5", "B4", "B3", "B2" or "B1" on Column C was 2 (value of C29 is "B3" and value of C14 is "B5"). 2/16 gives gives 12.5%

I do hope I was clearer this time, and thank you for your patience AliGW. I'm going to work on Column K as I just noticed a mistake there, and will need to provide a slightly bigger example with more rows so that I can do it manually and show it.

Column K.xlsx

Column K spreadsheet with example and explanation there.

perhaps in K30
=IF(I30="R",(SUM(COUNTIF($C$2:C21,{"B1","B2","B3","b4","B5"}))+SUM(COUNTIF(C39:$C$58,{"B1","B2","B3","b4","B5"})))/40,"")

or
=IF(I30="R",(COUNTIF($C$2:C21,"<>B0")+COUNTIF(C39:$C$58,"<>B0"))/40,"")

Originally Posted by davsth

perhaps in K30
=IF(I30="R",(SUM(COUNTIF($C$2:C21,{"B1","B2","B3","b4","B5"}))+SUM(COUNTIF(C39:$C$58,{"B1","B2","B3","b4","B5"})))/40,"")

or
=IF(I30="R",(COUNTIF($C$2:C21,"<>B0")+COUNTIF(C39:$C$58,"<>B0"))/40,"")

That did it for that example! How could I apply to a whole spreadsheet, as in apply the formula to K2 and then apply it to the whole column? As the original spreadsheet have thousands of entries

Originally Posted by AliGW

This is the bit I'm not getting.

I have asked you to talk me through the EXACT mechanics of that calculation. You still haven't done this and I'm afraid I do not fully undertstand what that calculation should be. If you'd just talk me through it, I could give it another go. Or you could highlight the cells in the workbook that need to be counted.

I'm trying to adapt your formula to:

=IF(I2="R",LET(r,ROW()-1,f,FILTER(C:C,(ROW(C:C)>r-8)*(ROW(C:C)<r+8)),SUMPRODUCT((f="B5")+(f="B4")+(f="B3")+(f="B2")+(f="B1"))/16),"")

But in certain situations doesn't quite work, perhaps is it still counting the row where I="R" or perhaps it's not counting the 8th cell? Still working on it and see if I can adapt it myself

Originally Posted by AliGW

I don't remember seeing anywhere that the row the formula is on shouldn't be counted until now.

Change my formula to this to see which cells it's counting (put it in a cell somewhere where it can spill down):

=LET(r,ROW()-1,f,FILTER(F2:F32,(ROW(F2:F32)>r-8)*(ROW(F2:F32)<r+8)),s,SUMPRODUCT((f<>0)*(f="B5"))/16,f)

I'm going offline now, but if you still want help, I'll be back tomorrow morning.

Thanks for all your help AliGW! I ended up fixing this formula which does what I need and now leaving in blank when there's nothing to report:

=IF(I2="R", (COUNTIFS(OFFSET(C2,-8,0,17,1), "B5")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B4")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B3")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B2")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B1")-1)/16, "")

All I need now is to find a way to apply your formula davsth to the whole column and doing exactly what it does to every time that I="R", I've been trying but so far no luck

Originally Posted by davsth

perhaps in K30
=IF(I30="R",(SUM(COUNTIF($C$2:C21,{"B1","B2","B3","b4","B5"}))+SUM(COUNTIF(C39:$C$58,{"B1","B2","B3","b4","B5"})))/40,"")

or
=IF(I30="R",(COUNTIF($C$2:C21,"<>B0")+COUNTIF(C39:$C$58,"<>B0"))/40,"")

Any ideas on how to turn one of these formulas do work exactly as it does but for every time I="R"? Still stuck at it

Thanks

Originally Posted by AliGW

Thet's what the IF clause does.

Please attach a fresh workbook with the formula in place and notes about where it is not currently working.

Here you go AliGW, with only K column related information, manual example and how I got it plus davsth formula which works, but would just need something to actually apply the same logic for every time that I="R" as in the original spreadsheet this happens a few times.

Column K.xlsx

Originally Posted by AliGW

In K2 copied down:

=IF(I2="R",LET(d,$C$2:$C$118,r,ROW(I2),f,VSTACK(FILTER(d,((ROW(d)<r-8)*(ROW(d)>=r-28))),FILTER(d,((ROW(d)>r+8)*(ROW(d)<=r+28)))),SUMPRODUCT((f<>"")*(f<>"B0"))/40),"")

Sorry had to be away from the computer, just tested and seems to work perfectly!! Thank you AliGW

Hello and sorry to reply on a solved thread as I just noticed a small problem with the formulas

This formula from below from you AliGW it works great but if the value of "IR" is too close to the beginning or the end of the sheet values I get a #CALC! error as it probably doesn't have the amount of values to calculate. Is there a way around it?

And if so, in this cases the last bit of the formula "/40" would have to be adapted to the actually amount of cells analysed, otherwise it would report an incorrect value (for example, if it looks up to 40 rows, 5 of those rows match so 5/40. But in these cases if it only looks up to say 29 rows for example (due to being in the beginning/end of the sheet), let's say 5 of those rows match but should be 5/29 in these cases.

=IF(W7="IR",LET(d,C:C,r,ROW(W7),f,VSTACK(FILTER(d,((ROW(d)<r-8)*(ROW(d)>=r-28))),FILTER(d,((ROW(d)>r+8)*(ROW(d)<=r+28)))),SUMPRODUCT((f<>"")*(f<>"B0"))/40),"")


And then If I could ask for assistance in this formula below that I'm using, which in a similar way as the formula above checks rows above and below (without counting the row itself where it applies, in this case W7="IR"). It's giving a #REF! error which I assume is due to the same situation, doesn't have enough values to check as it's too close to the beginning/end of the sheet

Sorry to come back at this post especially on a Sunday! Happy to provide an updated test sheet if necessary.

Thanks

=IF(W7="IR", (COUNTIFS(OFFSET(C7,-8,0,17,1), "B5")+COUNTIFS(OFFSET(C7,-8,0,17,1), "B4")+COUNTIFS(OFFSET(C7,-8,0,17,1), "B3")+COUNTIFS(OFFSET(C7,-8,0,17,1), "B2")+COUNTIFS(OFFSET(C7,-8,0,17,1), "B1")-1)/16, "")

Thank you AliGW, it fixes the error however the maths are incorrect, I believe it's because in that situation it's still doing "/40" at the end when in reality it should be as many cells as it managed to count ("/40" maximum if enough values are provided)

I've edited your file and added a new example in Column L7 and new notes there

Column K(2) AliGW.xlsx

No worries you've helped so much already!

I've done an updated sheet example with notes for my other problem in this thread with the formula

=IF(I2="R", (COUNTIFS(OFFSET(C2,-8,0,17,1), "B5")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B4")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B3")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B2")+COUNTIFS(OFFSET(C2,-8,0,17,1), "B1")-1)/16, "")

Test1.xlsx

Your brain is far from tiny and you've helped so much already AliGW, really gratefull Maybe it's not possible or too hard to create a formula with these requirements

Anyone else wants to have a go at this two problems? do let me know if I can provided any more information or explain something better. I've been trying but so far no luck

Thanks

Absolutely, don't have much free time right now but I will post that still today.

Do you want it for both problems sheets?

Here you go, Column J is all made manually:

Column K(2) AliGW.xlsx

That work Bo_Ry and bebo021999! Thank you so much, had almost given up on this one and just reject the first and last batch of results.

I've also adapted your formula Bo_Ry to fix my other problem in this thread to:

=MAP(X2:X10000,LAMBDA(x,IF(x="R",LET(c,C2:C10000,r,ROW(c),s,ROW(x),y,(r>=s-8)*(r<s-0)+(r>s+0)*(r<=s+8),SUM(y*(c>"B0"))/SUM(y)),"")))

Will mark as thread solved, reputation given. Thank you all!!