| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MAX value but with two comparison filtering criteria
Hi again chaps,
I'm using the following array formula to determine the MAX value in column I3:I1002: {=IF(I1019="","",MAX(IF($G$3:$G$1002$H$3:$H$1002, I3:I1002)))} I get the MAX value from column I3:I1002 but only where corresponding values in G3:G1002 are larger than the values in H3:H1002. This appears to work fine but I now need the MAX value not only with G3:G1002H3:H1002, but also where E3:E1002F3:F1002. If someone could work the additional criteria into my formula it's be a great help. Many thanks for looking. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX value but with two comparison filtering criteria
One way is to just nest another IF test.
Array entered: =IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002)))) This has the meaning of: IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis. -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi again chaps, I'm using the following array formula to determine the MAX value in column I3:I1002: {=IF(I1019="","",MAX(IF($G$3:$G$1002$H$3:$H$1002, I3:I1002)))} I get the MAX value from column I3:I1002 but only where corresponding values in G3:G1002 are larger than the values in H3:H1002. This appears to work fine but I now need the MAX value not only with G3:G1002H3:H1002, but also where E3:E1002F3:F1002. If someone could work the additional criteria into my formula it's be a great help. Many thanks for looking. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX value but with two comparison filtering criteria
Hi Biff,
Thanks for that, works a treat. Just hit the buffers again though where I need to find the MAX value in I3:I1002 but using an OR function rather than AND. If value in column G3:G1002 is 7 OR value in H3:H1002 is 7 (or even if both columns have a value 7 in them), how could I find the MAX corresponding value in column I3:I1002 where one or both criteria are met? "T. Valko" wrote: One way is to just nest another IF test. Array entered: =IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002)))) This has the meaning of: IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis. -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi again chaps, I'm using the following array formula to determine the MAX value in column I3:I1002: {=IF(I1019="","",MAX(IF($G$3:$G$1002$H$3:$H$1002, I3:I1002)))} I get the MAX value from column I3:I1002 but only where corresponding values in G3:G1002 are larger than the values in H3:H1002. This appears to work fine but I now need the MAX value not only with G3:G1002H3:H1002, but also where E3:E1002F3:F1002. If someone could work the additional criteria into my formula it's be a great help. Many thanks for looking. . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX value but with two comparison filtering criteria
Array entered:
=MAX(IF((G3:G10027)+(H3:H10027),I3:I1002)) -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi Biff, Thanks for that, works a treat. Just hit the buffers again though where I need to find the MAX value in I3:I1002 but using an OR function rather than AND. If value in column G3:G1002 is 7 OR value in H3:H1002 is 7 (or even if both columns have a value 7 in them), how could I find the MAX corresponding value in column I3:I1002 where one or both criteria are met? "T. Valko" wrote: One way is to just nest another IF test. Array entered: =IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002)))) This has the meaning of: IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis. -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi again chaps, I'm using the following array formula to determine the MAX value in column I3:I1002: {=IF(I1019="","",MAX(IF($G$3:$G$1002$H$3:$H$1002, I3:I1002)))} I get the MAX value from column I3:I1002 but only where corresponding values in G3:G1002 are larger than the values in H3:H1002. This appears to work fine but I now need the MAX value not only with G3:G1002H3:H1002, but also where E3:E1002F3:F1002. If someone could work the additional criteria into my formula it's be a great help. Many thanks for looking. . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX value but with two comparison filtering criteria
Cheers Biff
"T. Valko" wrote: Array entered: =MAX(IF((G3:G10027)+(H3:H10027),I3:I1002)) -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi Biff, Thanks for that, works a treat. Just hit the buffers again though where I need to find the MAX value in I3:I1002 but using an OR function rather than AND. If value in column G3:G1002 is 7 OR value in H3:H1002 is 7 (or even if both columns have a value 7 in them), how could I find the MAX corresponding value in column I3:I1002 where one or both criteria are met? "T. Valko" wrote: One way is to just nest another IF test. Array entered: =IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002)))) This has the meaning of: IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis. -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi again chaps, I'm using the following array formula to determine the MAX value in column I3:I1002: {=IF(I1019="","",MAX(IF($G$3:$G$1002$H$3:$H$1002, I3:I1002)))} I get the MAX value from column I3:I1002 but only where corresponding values in G3:G1002 are larger than the values in H3:H1002. This appears to work fine but I now need the MAX value not only with G3:G1002H3:H1002, but also where E3:E1002F3:F1002. If someone could work the additional criteria into my formula it's be a great help. Many thanks for looking. . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX value but with two comparison filtering criteria
You're welcome!
-- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Cheers Biff "T. Valko" wrote: Array entered: =MAX(IF((G3:G10027)+(H3:H10027),I3:I1002)) -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi Biff, Thanks for that, works a treat. Just hit the buffers again though where I need to find the MAX value in I3:I1002 but using an OR function rather than AND. If value in column G3:G1002 is 7 OR value in H3:H1002 is 7 (or even if both columns have a value 7 in them), how could I find the MAX corresponding value in column I3:I1002 where one or both criteria are met? "T. Valko" wrote: One way is to just nest another IF test. Array entered: =IF(I1019="","",MAX(IF(G3:G1002H3:H1002,IF(E3:E10 02F3:F1002,I3:I1002)))) This has the meaning of: IF G3:G1002H3:H1002 *and* IF E3:E1002F3:F1002 on a cell by cell basis. -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi again chaps, I'm using the following array formula to determine the MAX value in column I3:I1002: {=IF(I1019="","",MAX(IF($G$3:$G$1002$H$3:$H$1002, I3:I1002)))} I get the MAX value from column I3:I1002 but only where corresponding values in G3:G1002 are larger than the values in H3:H1002. This appears to work fine but I now need the MAX value not only with G3:G1002H3:H1002, but also where E3:E1002F3:F1002. If someone could work the additional criteria into my formula it's be a great help. Many thanks for looking. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filtering with multiple criteria | Excel Discussion (Misc queries) | |||
| Different comparison criteria in sumproduct | Excel Worksheet Functions | |||
| Filtering by criteria within PV in Excel. | Excel Discussion (Misc queries) | |||
| Two cell Comparison with three different Criteria | Excel Worksheet Functions | |||
| Filtering by criteria in two columns | Excel Worksheet Functions | |||
![Struggling in Sheffield[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
