You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Finding the number of times a max value is seen per location
- Thread starter hieronomo
- Start date
Good afternoon,
Please see the attached file. Is this what you are looking for? It looks like all of the max values occur only once, but I did change the values to test the formula and it appears to work correctly (when I duplicate a max value within a location it shows up as a '2' in the count.
Hope this helps,
Please see the attached file. Is this what you are looking for? It looks like all of the max values occur only once, but I did change the values to test the formula and it appears to work correctly (when I duplicate a max value within a location it shows up as a '2' in the count.
Hope this helps,
Attachments
Good afternoon,
Please see the attached file. Is this what you are looking for? It looks like all of the max values occur only once, but I did change the values to test the formula and it appears to work correctly (when I duplicate a max value within a location it shows up as a '2' in the count.
Hope this helps,
Thank you for your response but I was actually looking for the count of max per ssid. So there should be 4 rows (Ant, Threat, Snail and Variable) each with a count of the times thay had a max value per location.
Simi
New member
- Joined
- Feb 10, 2012
- Messages
- 190
- Reaction score
- 0
- Points
- 0
- Location
- Utah, USA
- Excel Version(s)
- Version 2002 Build 12527.20194
This is slightly hard to look at but I'll try to break it down.
On "Sheet1" cell B2 put the following:
=SUM(IF(speed!B$1:speed!B$24508=A2,IF(speed!C$1:speed!C$24508=MAX(IF(speed!B$1:speed!B$24508=A2,speed!C$1:speed!C$24508,0)),1,0),0))
you will need to enter this as an array formula by pressing ctrl+shift+enter
I'm going to color code this so its easier to explain how it works.
=SUM(IF(speed!B$1:speed!B$24508=A2,IF(speed!C$1:speed!C$24508=MAX(IF(speed!B$1:speed!B$24508=A2,speed!C$1:speed!C$24508,0)),1,0),0))
The Red portion is there for us to only look at the data that pertains to the ssid in A2.
The Blue portion, then checks to see if the value in column C is equal to the max for the ssid, if it is it adds 1 to the count
The Green portion helps us find the max speed for our given value in this case cell A2 = Ant.
On "Sheet1" cell B2 put the following:
=SUM(IF(speed!B$1:speed!B$24508=A2,IF(speed!C$1:speed!C$24508=MAX(IF(speed!B$1:speed!B$24508=A2,speed!C$1:speed!C$24508,0)),1,0),0))
you will need to enter this as an array formula by pressing ctrl+shift+enter
I'm going to color code this so its easier to explain how it works.
=SUM(IF(speed!B$1:speed!B$24508=A2,IF(speed!C$1:speed!C$24508=MAX(IF(speed!B$1:speed!B$24508=A2,speed!C$1:speed!C$24508,0)),1,0),0))
The Red portion is there for us to only look at the data that pertains to the ssid in A2.
The Blue portion, then checks to see if the value in column C is equal to the max for the ssid, if it is it adds 1 to the count
The Green portion helps us find the max speed for our given value in this case cell A2 = Ant.
This is slightly hard to look at but I'll try to break it down.
On "Sheet1" cell B2 put the following:
=SUM(IF(speed!B$1:speed!B$24508=A2,IF(speed!C$1:speed!C$24508=MAX(IF(speed!B$1:speed!B$24508=A2,speed!C$1:speed!C$24508,0)),1,0),0))
you will need to enter this as an array formula by pressing ctrl+shift+enter
I'm going to color code this so its easier to explain how it works.
=SUM(IF(speed!B$1:speed!B$24508=A2,IF(speed!C$1:speed!C$24508=MAX(IF(speed!B$1:speed!B$24508=A2,speed!C$1:speed!C$24508,0)),1,0),0))
The Red portion is there for us to only look at the data that pertains to the ssid in A2.
The Blue portion, then checks to see if the value in column C is equal to the max for the ssid, if it is it adds 1 to the count
The Green portion helps us find the max speed for our given value in this case cell A2 = Ant.
Thanks but forgot the ua on speed. This is more of a tally per location. You are trying to find how many times SSID of Ant has the fastest speeds across all of the ua locations. This is a competitive list.