My problem is I got this formula, which achieve the results I want
apart from one solution. The equation is: -


=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(D*17:D20)))



The solution that I am looking for if any number is selected out of
D17:D20 it should display the min value in D15, but the equation you
must select
all 4 cells from D17:D20 for the min value to display in D15.


Anyone there that can help me?


many thanks


ims

You have a circular reference. you can not put the reference to d15 when the
formula is in d15. thry this in D15

=IF(OR(COUNTBLANK(D17:D20)0),"",MIN(D17:D20))

It returns blank unless there are numbers in each of the cells D17:D20 and
the minimum of the range if there are 4 numbers.

Regards
Peter


" wrote:

My problem is I got this formula, which achieve the results I want
apart from one solution. The equation is: -


=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(DÂ*17:D20)))



The solution that I am looking for if any number is selected out of
D17:D20 it should display the min value in D15, but the equation you
must select
all 4 cells from D17:D20 for the min value to display in D15.


Anyone there that can help me?


many thanks


ims

Thanks Peter for that response, but i am looking for the value to
return to D15 if there were 1 number in cell D17:D20.

thanks

Imran


PeterAtherton wrote:
You have a circular reference. you can not put the reference to d15 when the
formula is in d15. thry this in D15

=IF(OR(COUNTBLANK(D17:D20)0),"",MIN(D17:D20))

It returns blank unless there are numbers in each of the cells D17:D20 and
the minimum of the range if there are 4 numbers.

Regards
Peter


" wrote:

My problem is I got this formula, which achieve the results I want
apart from one solution. The equation is: -


=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(D*17:D20)))



The solution that I am looking for if any number is selected out of
D17:D20 it should display the min value in D15, but the equation you
must select
all 4 cells from D17:D20 for the min value to display in D15.


Anyone there that can help me?


many thanks


ims

Imran

Try D15 =IF(COUNT(D17:D20)=0,"",MIN(D17:D20))
- counting the enties rather than the blanks

regards
Peter

" wrote:

Thanks Peter for that response, but i am looking for the value to
return to D15 if there were 1 number in cell D17:D20.

thanks

Imran


PeterAtherton wrote:
You have a circular reference. you can not put the reference to d15 when the
formula is in d15. thry this in D15

=IF(OR(COUNTBLANK(D17:D20)0),"",MIN(D17:D20))

It returns blank unless there are numbers in each of the cells D17:D20 and
the minimum of the range if there are 4 numbers.

Regards
Peter


" wrote:

My problem is I got this formula, which achieve the results I want
apart from one solution. The equation is: -


=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(DÂ*17:D20)))



The solution that I am looking for if any number is selected out of
D17:D20 it should display the min value in D15, but the equation you
must select
all 4 cells from D17:D20 for the min value to display in D15.


Anyone there that can help me?


many thanks


ims

thanks peter it's work apart from if D15 has 1 D16 display 0 how would
i remove this 0

many thanks

Imran

PeterAtherton wrote:
Imran

Try D15 =IF(COUNT(D17:D20)=0,"",MIN(D17:D20))
- counting the enties rather than the blanks

regards
Peter

" wrote:

Thanks Peter for that response, but i am looking for the value to
return to D15 if there were 1 number in cell D17:D20.

thanks

Imran


PeterAtherton wrote:
You have a circular reference. you can not put the reference to d15 when the
formula is in d15. thry this in D15

=IF(OR(COUNTBLANK(D17:D20)0),"",MIN(D17:D20))

It returns blank unless there are numbers in each of the cells D17:D20 and
the minimum of the range if there are 4 numbers.

Regards
Peter


" wrote:

My problem is I got this formula, which achieve the results I want
apart from one solution. The equation is: -


=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(D*17:D20)))



The solution that I am looking for if any number is selected out of
D17:D20 it should display the min value in D15, but the equation you
must select
all 4 cells from D17:D20 for the min value to display in D15.


Anyone there that can help me?


many thanks


ims

Imran

You can choose Tools, Options, View tab and uncheck the the show zeros
checkbox.
This means that although not showing a zero fills that cell and this could
affect an average calc including that cell.

I'm not sure what you have in D16 that causes it to display zero but if you
want the cell to have nothing use something like D16 =if(d15=0,"",something
else here)

For instance, if you wanted D15 to display a zero instead of a blank the
formula would be D15 =IF(COUNT(D17:D20)=0,0,MIN(D17:D20)) but I think you new
that already.

Regards
Peter

" wrote:

thanks peter it's work apart from if D15 has 1 D16 display 0 how would
i remove this 0

many thanks

Imran

PeterAtherton wrote:
Imran

Try D15 =IF(COUNT(D17:D20)=0,"",MIN(D17:D20))
- counting the enties rather than the blanks

regards
Peter

" wrote:

Thanks Peter for that response, but i am looking for the value to
return to D15 if there were 1 number in cell D17:D20.

thanks

Imran


PeterAtherton wrote:
You have a circular reference. you can not put the reference to d15 when the
formula is in d15. thry this in D15

=IF(OR(COUNTBLANK(D17:D20)0),"",MIN(D17:D20))

It returns blank unless there are numbers in each of the cells D17:D20 and
the minimum of the range if there are 4 numbers.

Regards
Peter


" wrote:

My problem is I got this formula, which achieve the results I want
apart from one solution. The equation is: -


=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(DÂ*17:D20)))



The solution that I am looking for if any number is selected out of
D17:D20 it should display the min value in D15, but the equation you
must select
all 4 cells from D17:D20 for the min value to display in D15.


Anyone there that can help me?


many thanks


ims

thanks Peter, I wouldn't be using D15 within the formula because it's
far too complicated.
PeterAtherton wrote:
Imran

You can choose Tools, Options, View tab and uncheck the the show zeros
checkbox.
This means that although not showing a zero fills that cell and this could
affect an average calc including that cell.

I'm not sure what you have in D16 that causes it to display zero but if you
want the cell to have nothing use something like D16 =if(d15=0,"",something
else here)

For instance, if you wanted D15 to display a zero instead of a blank the
formula would be D15 =IF(COUNT(D17:D20)=0,0,MIN(D17:D20)) but I think you new
that already.

Regards
Peter

" wrote:

thanks peter it's work apart from if D15 has 1 D16 display 0 how would
i remove this 0

many thanks

Imran

PeterAtherton wrote:
Imran

Try D15 =IF(COUNT(D17:D20)=0,"",MIN(D17:D20))
- counting the enties rather than the blanks

regards
Peter

" wrote:

Thanks Peter for that response, but i am looking for the value to
return to D15 if there were 1 number in cell D17:D20.

thanks

Imran


PeterAtherton wrote:
You have a circular reference. you can not put the reference to d15 when the
formula is in d15. thry this in D15

=IF(OR(COUNTBLANK(D17:D20)0),"",MIN(D17:D20))

It returns blank unless there are numbers in each of the cells D17:D20 and
the minimum of the range if there are 4 numbers.

Regards
Peter


" wrote:

My problem is I got this formula, which achieve the results I want
apart from one solution. The equation is: -


=IF(OR(D15="",COUNTBLANK(D17:D20)0),"",IF(COUNTIF (D17:D20,D15)=4,D15,MIN(D*17:D20)))



The solution that I am looking for if any number is selected out of
D17:D20 it should display the min value in D15, but the equation you
must select
all 4 cells from D17:D20 for the min value to display in D15.


Anyone there that can help me?


many thanks


ims