MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Min function


Posted by Rich on October 02, 2001 3:08 PM


(Excel 2000 Question)
I am developing a questionnaire where some but not all of the questions may be filled out.

In my example, the following equation;

=MIN('Questionnaire One'!E17,'Questionnaire Two'!E17,'Questionnaire Three'!E17,'Questionnaire Four'!E17,'Questionnaire Five'!E17,'Questionnaire Six'!E17,'Questionnaire Seven'!E17,'Questionnaire Eight'!E17,'Questionnaire Nine'!E17,'Questionnaire Ten'!E17)


Will return data such as 1,3,5,4,3,1,0,0,0,0.

I want my Min function to return 1 as a min (in the above example data, therefore ignoring the 0's. As you can see, the data is coming from different sheets.

Thanks for the help.

Rich


Posted by Aladin Akyurek on October 02, 2001 3:36 PM

Collect them first one by one into a column in an additional worksheet.

Apply to that range the following array formula:

=MIN(IF(A2:A11,A2:A11))

Hold down CONTROL and SHIFT keys before hitting ENTER to enter the above formula.

============= I am developing a questionnaire where some but not all of the questions may be filled out.