Forum Discussion
Solved
Need help with formula for counting the last 10 even rows that contain a value of a column
Hi, I need help with a formula. See the picture for my excel table. I want to count the average of the last 10 even rows that contain a value (so not the last 2 even rows on the picture) of for ex...
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
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