Hello Excel masters:
I have few data columns in excel and want to perform some calculations on it using INDIRECT formula with condition.
In my sample excel sheet: (excel sheet attached). I am using Excel 2007.
Col: E has depths
Col: F, G, H have data related to depths and these columns have some invalid data that I want to ignore when perform calculations of data. invalid data is: 0 and -9999 in some cells.
In cell A15 & B15, I enter the depth intervals ( depths from col E)
Cell: B17, B18, B19 I enter the
.1 When I use: MIN(INDIRECT("G"&A13):INDIRECT("G"&B13)), it works fine but include invalid data. I do not want this formula.
2. When I use it with condition then it returns ZERO or invalid data or wrong results.
MIN(IF(G:G>0,INDIRECT("G"&A13):INDIRECT("G"&B13))) ==> I want to use this formula.
If my data columns has values e.g. 0 and or -9999 then excel should ignore these values and give me correct MIN, MAX and AVERAGE based on formula 2 above.
or any better solution. Any one has solutions for it, please help.
I have few data columns in excel and want to perform some calculations on it using INDIRECT formula with condition.
In my sample excel sheet: (excel sheet attached). I am using Excel 2007.
Col: E has depths
Col: F, G, H have data related to depths and these columns have some invalid data that I want to ignore when perform calculations of data. invalid data is: 0 and -9999 in some cells.
In cell A15 & B15, I enter the depth intervals ( depths from col E)
Cell: B17, B18, B19 I enter the
.1 When I use: MIN(INDIRECT("G"&A13):INDIRECT("G"&B13)), it works fine but include invalid data. I do not want this formula.
2. When I use it with condition then it returns ZERO or invalid data or wrong results.
MIN(IF(G:G>0,INDIRECT("G"&A13):INDIRECT("G"&B13))) ==> I want to use this formula.
If my data columns has values e.g. 0 and or -9999 then excel should ignore these values and give me correct MIN, MAX and AVERAGE based on formula 2 above.
or any better solution. Any one has solutions for it, please help.