Forum Discussion
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 example column B. I'm constantly adding new data in the new rows. Therefore, I need a formula that constantly only calculates the last 10 even rows with a value of column B.
Now I have a formula that calculates the mean of all the even rows. So, this has to be adjusted that it only calculates the last 10 even rows with a value. This is the formula I have now:
=SUM(IF(ISEVEN(ROW(B70:B115));B70:B115))/(COUNT(B70:B115)/2)
Or I also can calculate it with this formula:
=SUM(IF(MOD(ROW(B70:B115);2)=0;B70:B115;0))/(COUNT(B70:B115)/2)
So these formulas just count all the even values, but I need it to calculate only the last 10 even values.
I have searched online but I wasn't able to find the solution. Hopefully, someone can help me!!
Thanks in advance!
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
12 Replies
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.
