nostradamus
Board Regular
- Joined
- Aug 9, 2010
- Messages
- 143
- Office Version
- 365
- Platform
- Windows
Hello all,
Have four formula requests based on certain criteria from the following dataset (using Office 365).
Formulas for Cells E2 - E5, for Low category ("1):
Cell E 2: Total count of "1" in range (B2:B14), if not, return 0
Cell E 3: Max value from Col A associated with "1" from Col B in range (A1:A14)
Cell E 4: Max continuous count of "1" in range (B2:B14), if not, return 0
Cell E 5: Max value from Col A associated with "1" from Col B in range (A1:A14) - only from the continuous count
Similar formulas in Cells E7 - E10, but with the Medium category ("2").
Hope formulas in E3 and E5 make sense, I could have worded it better ...
Thanks!!!
Have four formula requests based on certain criteria from the following dataset (using Office 365).
Formulas for Cells E2 - E5, for Low category ("1):
Cell E 2: Total count of "1" in range (B2:B14), if not, return 0
Cell E 3: Max value from Col A associated with "1" from Col B in range (A1:A14)
Cell E 4: Max continuous count of "1" in range (B2:B14), if not, return 0
Cell E 5: Max value from Col A associated with "1" from Col B in range (A1:A14) - only from the continuous count
Similar formulas in Cells E7 - E10, but with the Medium category ("2").
Hope formulas in E3 and E5 make sense, I could have worded it better ...
Thanks!!!
Copy of Colorado at Bastrop 1985-2021.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Quantity | Category | Variable | Formula Values | |||
2 | 625 | 1 | Low-Count | 7 | |||
3 | 616 | 1 | Low-maxQtyForLow | 1030 | |||
4 | 578 | 1 | Low-continuous count | 5 | |||
5 | 571 | 1 | Low-maxQtyForContinuousCount | 625 | |||
6 | 571 | 1 | |||||
7 | 6030 | 2 | Medium-Count | 6 | |||
8 | 2430 | 2 | Medium-maxQtyForMedium | 6030 | |||
9 | 1030 | 1 | Medium-continuous count | 4 | |||
10 | 920 | 1 | Medium-maxQtyForContinuousCount | 2920 | |||
11 | 2920 | 2 | |||||
12 | 2750 | 2 | |||||
13 | 2690 | 2 | |||||
14 | 2540 | 2 | |||||
15 | |||||||
16 | |||||||
Sheet2 |