SOLVED

Need help with formula for counting the last 10 even rows that contain a value of a column

Copper Contributor

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!

Schermafbeelding 2020-10-26 om 21.28.51.png

12 Replies
best response confirmed by Daan21 (Copper Contributor)
Solution

@Daan21 

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

@Hans Vogelaar 

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

@Daan21 

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

@Daan21 

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.

@Hans Vogelaar 

Hi Hans,

Thank you for the quick responses! The formulas both work perfectly. I tried the one for the odd rows the same way but made a small mistake that's why I wasn't able to formulate the formula. So, Thank you very much for your perfect help!

 

Greetings,

Daan

@Hans Vogelaar 

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

@Daan21 

Can you explain as clearly as you can what you want to compute? I have no idea what your formula does. Thanks in advance.

@Hans Vogelaar 

 

Hi Hans, 

Thank you for your response! I'll try to explain it the best I can.

For the explanation, you can look at the picture below. As you can see, in column A there are all dates. Al these dates have 2 rows, one row with numbers, and one row with percentages. I want to calculate the number of dates that have at least one column (in Column B to G) with a positive value. Sidenote: the number and percentages are always both either positive or negative. So, as you can see, if a date has a positive number it always also has a positive percentage. There is no difference between the rows. The only difference is that the one is a number and one a percentage. So, you don't have to take this into account. I want to know if a date contains at least one positive value (green cells) for column B to G. IF this is the case I want it to be counted.

Here a couple of examples:

Date 01-06-2020: it has a positive value in column B (under 09.10), so this date has to be counted.

Date 08-06-2020: doesn't have a positive value in any of the columns from B to G (they hare all negative values), so this date doesn't have to be counted.

Date 29-06-2020: this date contains positive values at column B and C (so it has at least one positive value in any of the columns), so this date has to be counted.

 

But the real problem I am facing is that I want a formula that calculates for how many dates it is case that they have at least one positive value in any of the columns based on the last 10 dates that contain values in the columns. So, at this moment this is from date 17-08-2020 till 19-10-2020 and the outcome has to be 8. Because only the dates 07-09-2020 and 21-09-2020 don't have a positive value in any of the columns. However, when I fill in a new date (26-10-2020), then the last 10 dates with values are 24-08-2020 till 26-10-2020. So, this can be done (I think) with the function "offset", as you advised for the other formulas. 

 

I hope I made it clear this way! 

I'm looking forward to your reaction.

Greetings,

Daan

Schermafbeelding 2020-10-26 om 21.28.51.png

@Daan21 

Hi Daan,

 

Thanks, I understand now (I think). Try this formula, confirmed with Ctrl+Shift+Enter if you don't have Microsoft 365 (it's an array formula):

 

=SUM(--(MMULT(--(OFFSET($B$84;COUNT($B$84:$B$1000)-20;0;20;6)>0);TRANSPOSE(COLUMN(OFFSET($B$84;COUNT($B$84:$B$1000)-20;0;20;6))))>0))/2

@Hans Vogelaar 

 

Hi Hans,

Thank you for the quick responses! This formula worked! Thank you very much for your help. This was the last formula I needed in my current sheet to finalize, so I think I won't need your help any time soon anymore;) If I need help one day, I'll contact you if you are open to that!

 

For the formula, I did the same thing with "offset". However, I forgot to change the width to 6 instead of 1. So, that was the mistake I made and you cleared that up for me! 

Once again, thank you and have a nice evening!

 

Greetings,

Daan

@Daan21 

I'm happy to hear that your problem has been solved. Feel free to come back if you have more questions.

1 best response

Accepted Solutions
best response confirmed by Daan21 (Copper Contributor)
Solution

@Daan21 

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

View solution in original post