I am trying to find a formula that will both rank only visible values but as the same time as ignoring zeros. I have an array formula for ranking only visible and I have a formula for for ignoring zeros but can not work out a formula to include both. Please help
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Rank only visible values and ignore zeros
- Thread starter Amba1006
- Start date
You can combine the two formulas to rank only the visible values while ignoring zeros by using the SUBTOTAL function in Excel. The SUBTOTAL function can perform calculations like SUM, AVERAGE, COUNT, etc. on visible cells only.
Here's an example formula to rank the values in column A while ignoring zeros:
=IF(A2=0,"",IFERROR(RANK.AVG(SUBTOTAL(9,OFFSET(A$1,ROW(A2:A10)-ROW(A2),)),SUBTOTAL(9,OFFSET(A$1,ROW(A2:A10)-ROW(A2),))),"-"))
In this formula, SUBTOTAL(9,OFFSET(A$1,ROW(A2:A10)-ROW(A2),)) is used to get the visible cells only in the range A2:A10. The RANK.AVG function is then used to rank the values in the visible cells.
The IF statement at the beginning checks if the value in A2 is zero. If it is, the cell is left blank. If not, the ranking is calculated as described above.
You can adjust the formula to fit your specific data range and requirements.
Here's an example formula to rank the values in column A while ignoring zeros:
=IF(A2=0,"",IFERROR(RANK.AVG(SUBTOTAL(9,OFFSET(A$1,ROW(A2:A10)-ROW(A2),)),SUBTOTAL(9,OFFSET(A$1,ROW(A2:A10)-ROW(A2),))),"-"))
In this formula, SUBTOTAL(9,OFFSET(A$1,ROW(A2:A10)-ROW(A2),)) is used to get the visible cells only in the range A2:A10. The RANK.AVG function is then used to rank the values in the visible cells.
The IF statement at the beginning checks if the value in A2 is zero. If it is, the cell is left blank. If not, the ranking is calculated as described above.
You can adjust the formula to fit your specific data range and requirements.
Upvote
0
Thanks so much however, I have entered the formula exactly as you have written it however it returns the below. I have walked through your formula and I can't see where that would apply. Any advice?
Upvote
0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 90,612
- Office Version
- 365
- Platform
- Windows
Can you post some sample data, including your existing formulae.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Upvote
0
Thanks, I am super new here. I will post example shortly
Upvote
0
| Rank Formula Example.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | COLOUR BAND | YTD REVENUE | Rank Ignoring ZERO | FILTERED RANK | ||
| 2 | BLUE | £ 1,401 | 24 | 24 | ||
| 3 | GREEN | £ 873,789 | 6 | 6 | ||
| 4 | RED | £ 9,846,469 | 1 | 1 | ||
| 5 | BLUE | £ 199,337 | 9 | 9 | ||
| 6 | GREEN | £ 1 | 30 | 30 | ||
| 7 | RED | £ 4,655,195 | 3 | 3 | ||
| 8 | BLUE | £ 2,879,577 | 4 | 4 | ||
| 9 | GREEN | £ 2,704,335 | 5 | 5 | ||
| 10 | RED | £ 140,403 | 10 | 10 | ||
| 11 | BLUE | £ 7,138,346 | 2 | 2 | ||
| 12 | GREEN | £ 602 | 28 | 28 | ||
| 13 | RED | £ - | 31 | |||
| 14 | BLUE | £ - | 31 | |||
| 15 | GREEN | £ - | 31 | |||
| 16 | RED | £ 624 | 27 | 27 | ||
| 17 | BLUE | £ - | 31 | |||
| 18 | GREEN | £ - | 31 | |||
| 19 | RED | £ 12,636 | 13 | 13 | ||
| 20 | BLUE | £ - | 31 | |||
| 21 | GREEN | £ - | 31 | |||
| 22 | RED | £ 13,014 | 12 | 12 | ||
| 23 | BLUE | £ - | 31 | |||
| 24 | GREEN | £ 452 | 29 | 29 | ||
| 25 | RED | £ 5,631 | 18 | 18 | ||
| 26 | BLUE | £ 492,349 | 7 | 7 | ||
| 27 | GREEN | £ 9,433 | 17 | 17 | ||
| 28 | RED | £ 2,673 | 20 | 20 | ||
| 29 | BLUE | £ 2,650 | 21 | 21 | ||
| 30 | GREEN | £ 978 | 25 | 25 | ||
| 31 | RED | £ 11,072 | 15 | 15 | ||
| 32 | BLUE | £ 11,753 | 14 | 14 | ||
| 33 | GREEN | £ 319,005 | 8 | 8 | ||
| 34 | RED | £ 4,364 | 19 | 19 | ||
| 35 | BLUE | £ 1,655 | 23 | 23 | ||
| 36 | GREEN | £ 2,494 | 22 | 22 | ||
| 37 | RED | £ 747 | 26 | 26 | ||
| 38 | BLUE | £ 10,345 | 16 | 16 | ||
| 39 | GREEN | £ 112,417 | 11 | 11 | ||
RANK FORMULA | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C39 | C2 | =IF([@[YTD REVENUE]]=0,"",SUM(IF(SUBTOTAL(103,OFFSET($B$2:$B$39,ROW($B$2:$B$39)-ROW($B$2),0,1))>0,IF(B2<$B$2:$B$39,1)))+1) |
| D2:D39 | D2 | =SUM(IF(SUBTOTAL(103,OFFSET($B$2:$B$39,ROW($B$2:$B$39)-ROW($B$2),0,1))>0,IF(B2<$B$2:$B$39,1)))+1 |
Upvote
0
So I have just added column A to show that the formula in column D works on filters but basically I am trying to combine the two formulas (or a formula that does the job of both)
Upvote
0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 90,612
- Office Version
- 365
- Platform
- Windows
Must admit I'm not sure what your asking for as both formulae return the same result, except for the zeros.
But if you are happy with a helper column, you can get rid of the volatile functions
But if you are happy with a helper column, you can get rid of the volatile functions
| Fluff.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | COLOUR BAND | YTD REVENUE | Rank Ignoring ZERO | FILTERED RANK | IsVisible | Column2 | ||
| 2 | BLUE | 1401.05 | 24 | 24 | 1 | 24 | ||
| 3 | GREEN | 873789.337 | 6 | 6 | 1 | 6 | ||
| 4 | RED | 9846469.34 | 1 | 1 | 1 | 1 | ||
| 5 | BLUE | 199336.727 | 9 | 9 | 1 | 9 | ||
| 6 | GREEN | 0.65 | 30 | 30 | 1 | 30 | ||
| 7 | RED | 4655195.021 | 3 | 3 | 1 | 3 | ||
| 8 | BLUE | 2879577.446 | 4 | 4 | 1 | 4 | ||
| 9 | GREEN | 2704335.209 | 5 | 5 | 1 | 5 | ||
| 10 | RED | 140402.62 | 10 | 10 | 1 | 10 | ||
| 11 | BLUE | 7138346.156 | 2 | 2 | 1 | 2 | ||
| 12 | GREEN | 602.1 | 28 | 28 | 1 | 28 | ||
| 13 | RED | 0 | 31 | 1 | ||||
| 14 | BLUE | 0 | 31 | 1 | ||||
| 15 | GREEN | 0 | 31 | 1 | ||||
| 16 | RED | 623.71 | 27 | 27 | 1 | 27 | ||
| 17 | BLUE | 0 | 31 | 1 | ||||
| 18 | GREEN | 0 | 31 | 1 | ||||
| 19 | RED | 12636.162 | 13 | 13 | 1 | 13 | ||
| 20 | BLUE | 0 | 31 | 1 | ||||
| 21 | GREEN | 0 | 31 | 1 | ||||
| 22 | RED | 13014.3 | 12 | 12 | 1 | 12 | ||
| 23 | BLUE | 0 | 31 | 1 | ||||
| 24 | GREEN | 451.62 | 29 | 29 | 1 | 29 | ||
| 25 | RED | 5631.22 | 18 | 18 | 1 | 18 | ||
| 26 | BLUE | 492349.351 | 7 | 7 | 1 | 7 | ||
| 27 | GREEN | 9433.166 | 17 | 17 | 1 | 17 | ||
| 28 | RED | 2672.91 | 20 | 20 | 1 | 20 | ||
| 29 | BLUE | 2649.68 | 21 | 21 | 1 | 21 | ||
| 30 | GREEN | 977.8 | 25 | 25 | 1 | 25 | ||
| 31 | RED | 11071.75 | 15 | 15 | 1 | 15 | ||
| 32 | BLUE | 11752.59 | 14 | 14 | 1 | 14 | ||
| 33 | GREEN | 319004.74 | 8 | 8 | 1 | 8 | ||
| 34 | RED | 4364.13 | 19 | 19 | 1 | 19 | ||
| 35 | BLUE | 1654.5 | 23 | 23 | 1 | 23 | ||
| 36 | GREEN | 2494.35 | 22 | 22 | 1 | 22 | ||
| 37 | RED | 746.91 | 26 | 26 | 1 | 26 | ||
| 38 | BLUE | 10345.11 | 16 | 16 | 1 | 16 | ||
| 39 | GREEN | 112417.09 | 11 | 11 | 1 | 11 | ||
Sheet4 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C39 | C2 | =IF([@[YTD REVENUE]]=0,"",SUM(IF(SUBTOTAL(103,OFFSET($B$2:$B$39,ROW($B$2:$B$39)-ROW($B$2),0,1))>0,IF(B2<$B$2:$B$39,1)))+1) |
| D2:D39 | D2 | =SUM(IF(SUBTOTAL(103,OFFSET($B$2:$B$39,ROW($B$2:$B$39)-ROW($B$2),0,1))>0,IF(B2<$B$2:$B$39,1)))+1 |
| E2:E39 | E2 | =SUBTOTAL(103,[@[COLOUR BAND]]) |
| F2:F39 | F2 | =IF([@[YTD REVENUE]]=0,"",COUNTIFS([YTD REVENUE],">"&[@[YTD REVENUE]],[IsVisible],1)+1) |
| Fluff.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | COLOUR BAND | YTD REVENUE | Rank Ignoring ZERO | FILTERED RANK | IsVisible | Column2 | ||
| 3 | GREEN | 873789.337 | 2 | 2 | 1 | 2 | ||
| 6 | GREEN | 0.65 | 10 | 10 | 1 | 10 | ||
| 9 | GREEN | 2704335.209 | 1 | 1 | 1 | 1 | ||
| 12 | GREEN | 602.1 | 8 | 8 | 1 | 8 | ||
| 15 | GREEN | 0 | 11 | 1 | ||||
| 18 | GREEN | 0 | 11 | 1 | ||||
| 21 | GREEN | 0 | 11 | 1 | ||||
| 24 | GREEN | 451.62 | 9 | 9 | 1 | 9 | ||
| 27 | GREEN | 9433.166 | 5 | 5 | 1 | 5 | ||
| 30 | GREEN | 977.8 | 7 | 7 | 1 | 7 | ||
| 33 | GREEN | 319004.74 | 3 | 3 | 1 | 3 | ||
| 36 | GREEN | 2494.35 | 6 | 6 | 1 | 6 | ||
| 39 | GREEN | 112417.09 | 4 | 4 | 1 | 4 | ||
Sheet4 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3,C6,C9,C12,C15,C18,C21,C24,C27,C30,C33,C36,C39 | C3 | =IF([@[YTD REVENUE]]=0,"",SUM(IF(SUBTOTAL(103,OFFSET($B$2:$B$39,ROW($B$2:$B$39)-ROW($B$2),0,1))>0,IF(B3<$B$2:$B$39,1)))+1) |
| D3,D6,D9,D12,D15,D18,D21,D24,D27,D30,D33,D36,D39 | D3 | =SUM(IF(SUBTOTAL(103,OFFSET($B$2:$B$39,ROW($B$2:$B$39)-ROW($B$2),0,1))>0,IF(B3<$B$2:$B$39,1)))+1 |
| E3,E6,E9,E12,E15,E18,E21,E24,E27,E30,E33,E36,E39 | E3 | =SUBTOTAL(103,[@[COLOUR BAND]]) |
| F3,F6,F9,F12,F15,F18,F21,F24,F27,F30,F33,F36,F39 | F3 | =IF([@[YTD REVENUE]]=0,"",COUNTIFS([YTD REVENUE],">"&[@[YTD REVENUE]],[IsVisible],1)+1) |
Upvote
0
Solution
Similar threads
