# Excel indirect formula does not work with condition

#### Ria

##### Member
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.

#### Attachments

• Sample excel data file.xlsm
75 KB · Views: 5
Code:
``INDIRECT("G"&A13):INDIRECT("G"&B13)``
you can use:
Code:
``INDIRECT("G"&A13 & ":G"&B13)``
or:
Code:
``INDEX(G:G,A13):INDEX(G:G,B13)``
the last one being non-volatile.

Try:
Code:
``=MIN(IF((INDEX(G:G,A13):INDEX(G:G,B13)<>-9999)*(INDEX(G:G,A13):INDEX(G:G,B13)<>0),INDEX(G:G,A13):INDEX(G:G,B13)))``
for the min and average you can substitute max and average.
In Excel 2007 you have AVERAGEIFS, so for the average you can use:
Code:
``=AVERAGEIFS(INDEX(G:G,A13):INDEX(G:G,B13),INDEX(G:G,A13):INDEX(G:G,B13),"<>-9999",INDEX(G:G,A13):INDEX(G:G,B13),"<>0")``

Code:
``INDIRECT("G"&A13):INDIRECT("G"&B13)``
you can use:
Code:
``INDIRECT("G"&A13 & ":G"&B13)``
or:
Code:
``INDEX(G:G,A13):INDEX(G:G,B13)``
the last one being non-volatile.

Try:
Code:
``=MIN(IF((INDEX(G:G,A13):INDEX(G:G,B13)<>-9999)*(INDEX(G:G,A13):INDEX(G:G,B13)<>0),INDEX(G:G,A13):INDEX(G:G,B13)))``
for the min and average you can substitute max and average.
In Excel 2007 you have AVERAGEIFS, so for the average you can use:
Code:
``=AVERAGEIFS(INDEX(G:G,A13):INDEX(G:G,B13),INDEX(G:G,A13):INDEX(G:G,B13),"<>-9999",INDEX(G:G,A13):INDEX(G:G,B13),"<>0")``

Hey Thanks a lot for reply and giving other options.
AverageIFS YOU PROVIDED WORKED FINE. HOWEVER Min & Max do not work and I get error: #VALUE!

I'm not sure; try entering the formulae which show the error using Ctrl+Shift+Enter rather than plain Enter. Hold down the Ctrl and Shift keys, then then press Enter, then let go.

I'm not sure; try entering the formulae which show the error using Ctrl+Shift+Enter rather than plain Enter. Hold down the Ctrl and Shift keys, then then press Enter, then let go.
Thanks a lot. Ctrl+Shift+Enter WORKED.
One more thing, for MIN and AVERAGE, can we set ONLY ONE condition e.g. ( find MIN and AVERAGE only values those are greater than zero then I do not need to worry what garbage values are coming below zero). If we can please help me how. I am struggling and my knowledge is only user level.

Depending on your version the MINIFS function is available

I am using Excel 2007.
MINIFS appeared in Excel 2019.

find MIN and AVERAGE only values those are greater than zero
Try:
Code:
``=MIN(IF(INDEX(G:G,A13):INDEX(G:G,B13)>0,INDEX(G:G,A13):INDEX(G:G,B13)))``
replace MIN with AVERAGE or use:
Code:
``=AVERAGEIFS(INDEX(G:G,A13):INDEX(G:G,B13),INDEX(G:G,A13):INDEX(G:G,B13),">0")``

• Ria
MINIFS appeared in Excel 2019.

Try:
Code:
``=MIN(IF(INDEX(G:G,A13):INDEX(G:G,B13)>0,INDEX(G:G,A13):INDEX(G:G,B13)))``
replace MIN with AVERAGE or use:
Code:
``=AVERAGEIFS(INDEX(G:G,A13):INDEX(G:G,B13),INDEX(G:G,A13):INDEX(G:G,B13),">0")``
Big thanks. It worked. Solute you.