RobynLouise
New Member
- Joined
- Jan 6, 2017
- Messages
- 20
Hi
I have a data set that I need to calculate quintiles on. Essentially, my data set shows:
- the number of transactions taken place in a range of shops, broken down into hourly periods
I want to rank these transactions and calculate a quintile based not just on the time of transaction but also the county in which it takes place. The data looks as follows
I want to be able to show which stores are in the bottom quintile for transactions at a certain time, in their county. I had been using the following formula:
=SUMPRODUCT(($G$3:$G$9196=G3)*(H3>=$H$3:$H$9196))/COUNTIF($G$3:$G$9196,G3) (G relates to county, H relates to transactions per hour)
However I am finding that the result (which is a percentile) is wrong because it is taking blank cells into account (these need to be excluded as the store was closed)
So can anyone help me in excluding blank cells in this formula?
Thank you
I have a data set that I need to calculate quintiles on. Essentially, my data set shows:
- the number of transactions taken place in a range of shops, broken down into hourly periods
I want to rank these transactions and calculate a quintile based not just on the time of transaction but also the county in which it takes place. The data looks as follows
Store code (F) | County (G) | 9 - 10am (H) | 10am - 11am (I) | 11am - 12pm (J) |
xxxx | Devon | 3 | 9 | 8 |
xxxx | Cornwall | 7 | 8 | 11 |
I want to be able to show which stores are in the bottom quintile for transactions at a certain time, in their county. I had been using the following formula:
=SUMPRODUCT(($G$3:$G$9196=G3)*(H3>=$H$3:$H$9196))/COUNTIF($G$3:$G$9196,G3) (G relates to county, H relates to transactions per hour)
However I am finding that the result (which is a percentile) is wrong because it is taking blank cells into account (these need to be excluded as the store was closed)
So can anyone help me in excluding blank cells in this formula?
Thank you