• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.
or any better solution. Any one has solutions for it, please help.
 

Attachments

  • Sample excel data file.xlsm
    75 KB · Views: 5
Instead of
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")
 
Instead of
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!
Any other thought please
 
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.
 
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.
 
Back
Top