Forum Discussion
Need help with formula for counting the last 10 even rows that contain a value of a column
Try this formula:
=SUMPRODUCT(OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1)*ISEVEN(ROW(OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1))))/10
Try this formula:
=SUMPRODUCT(OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1)*ISEVEN(ROW(OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1))))/10- Hi Hans,
This formula worked! Thank you very much! However, I couldn't figure out how to do the same thing for the odd rows. So, the rows with the percentages. I also want to count the average percentage of the last 10 odd rows that contain a value (exactly the same as I did for the even rows with numbers). Could you help me figure this out?
Additionally, to complete my file, I want to calculate the chance of how many times of the last 10 dates there was a positive outcome (e.g., >0). I have a formula now that calculates the chance of all the dates that contain a value in the file (so the last two dates are excluded because they haven't got a value yet), but I want to change this to the last 10 dates that contain a value. Tried to adjust it with the formula you sent me, but couldn't get the right formula, unfortunately. Hope you can help me with this as well!
This is the formula I use to calculate how many times of all the dates a date was above 0:
=COUNTIF(B70:B115;">0")/COUNT(B70:B115)
I hope you can help me with these two formulas. Your last solution worked perfectly, once again thank you for that!
Greetings,
Daan Hi Hans,
This formula worked! Thank you very much! However, I couldn't figure out how to do the same thing for the odd rows. So, the rows with the percentages. I also want to count the average percentage of the last 10 odd rows that contain a value (exactly the same as I did for the even rows with numbers). Could you help me figure this out?
Additionally, to complete my file, I want to calculate the chance of how many times of the last 10 dates there was a positive outcome (e.g., >0). I have a formula now that calculates the chance of all the dates that contain a value in the file (so the last two dates are excluded because they haven't got a value yet), but I want to change this to the last 10 dates that contain a value. Tried to adjust it with the formula you sent me, but couldn't get the right formula, unfortunately. Hope you can help me with this as well!
This is the formula I use to calculate how many times of all the dates a date was above 0:
=COUNTIF(B70:B115;">0")/COUNT(B70:B115)
I hope you can help me with these two formulas. Your last solution worked perfectly, once again thank you for that!
Greetings,
Daan
The number of positive entries in the last 20 even-numbered rows is
=SUMPRODUCT((OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1)>0)*ISEVEN(ROW(OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1))))
Divide this by 10 to get the fraction of positive entries.
Hi Hans,
Sorry to ask you again. But I have one last formula I can't figure out on my own. Therefore, I ask you one more time for help. Hope you are willing to help again:)!
It is the same problem as for the other formulas. I have a formula that is based on all dates that contain a value and I want to change it to the last 10 dates that contain a value. Despite that I'm now familiar with the "offset" function, I can't figure out how to formulate the formula right. I want to calculate how many dates (of the last 10 dates that contain a value) contain a positive value (the green ones) in one of the columns. So, it doesn't matter if a date contains a positive value in more than one column. It does only matter if there is minimal one positive value in any of the columns B to G for a date. So, for example for date 21-09-2020, it doesn't contain a positive value in any of the columns. Therefore, this date must not be counted. The following dates (28-09-2020 till 19-10-2020), all have positive values in minimal one of the columns. Therefore, all these dates must be counted. I hope I made my idea clear. I have a formula that calculates it for all the dates, but now I need a formula that counts it for the last 10 dates that contain values. This is the formula I currently use for it:
=((SUM(--(MMULT(--(B84:G139>0);TRANSPOSE(COLUMN(B84:G139)))>0)))-COUNTBLANK(B84:B139))
I hope you can help me with this last formula I need for my sheet to complete! Already thanks in advance!!
Greetings,
Daan
For the last 10 odd rows:
=SUMPRODUCT(OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1)*ISODD(ROW(OFFSET($B$70,COUNT($B$70:$B$1000)-20,0,20,1))))/10
