Mr_Patrick
New Member
- Joined
- Feb 24, 2017
- Messages
- 8
Hi everyone,
I am working on a large dataset (+6000 entries) were each entry can be grouped pairwise, one recording some current data which will be later replaced by new data like so:
<tbody>
</tbody>
I wish to create a function that outputs as a percentage how many new values will be added if there is currently no new value. In the example above, assuming the data is only made up of these 4 rows, the function should output 50% (because out of the 6 blank cells in the 2 "Current" lines, 3 will be filled).
My intuition tells me to work with Countifs, but unfortunately since each criteria are assembled under an AND clause, thus working with something like :
does not work.
I also tried to make countifs only count only even cells (all the "Current" rows are on even cells ), but this is no good. (I try to avoid building macros as I have never done this).
Anyway, does someone can suggest me something?
Thank you in advance.
I am working on a large dataset (+6000 entries) were each entry can be grouped pairwise, one recording some current data which will be later replaced by new data like so:
| 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | |
| Current | 32 | 12 | 54 | 432 | 2 | ||
| New | 32 | 12 | 54 | 1 | 432 | 2 | |
| Current | 53 | 65 | 4 | ||||
| New | 4 | 65 | 8 | ||||
| etc | etc | etc | etc | etc | etc | etc | etc |
<tbody>
</tbody>
I wish to create a function that outputs as a percentage how many new values will be added if there is currently no new value. In the example above, assuming the data is only made up of these 4 rows, the function should output 50% (because out of the 6 blank cells in the 2 "Current" lines, 3 will be filled).
My intuition tells me to work with Countifs, but unfortunately since each criteria are assembled under an AND clause, thus working with something like :
Code:
[COUNTIFS(Data!$G$2:$G$6697;"=Current";Data!K$2:K$6697;"=";Data!$G$2:$G$6697;"=New";Data!K$2:K$6697;"<>")I also tried to make countifs only count only even cells (all the "Current" rows are on even cells ), but this is no good. (I try to avoid building macros as I have never done this).
Anyway, does someone can suggest me something?
Thank you in advance.
