Formula equation

[ims121uk]

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


=IF(OR(D15="",COUNTBLANK(D1720)>0),"",IF(COUNTIF(D1720,D15)=4,D15,MIN(D­1720)))



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


Anyone there that can help me?


many thanks


ims

 

[PeterAtherton]

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(D1720)>0),"",MIN(D1720))

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

Regards
Peter

 

[ims121uk]

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

thanks

Imran

 

[PeterAtherton]

Imran

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

regards
Peter

 

[ims121uk]

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

many thanks

Imran

 

[PeterAtherton]

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(D1720)=0,0,MIN(D1720)) but I think you new
that already.

Regards
Peter

 

[ims121uk]

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