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
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
Can you explain as clearly as you can what you want to compute? I have no idea what your formula does. Thanks in advance.
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
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