MIN formula leave blank if range of cell is empty

[jessamarie]

Hi everyone,

Hope anyone can help me with my problem.
I want my A1 cell to be blank if the range of A2:A6 has no value..
My existing formula is just this =MIN(A2:A6) it works when there is a date specified in the cell, but when it does not have a date specified therein it my A2 cell only reflect 00 Jan 1990.

Thank you all in advance.

 

[wideboydixon]

=if(min(a2:a6)=0,"",min(a2:a6))

wbd

 

[shaowu459]

You may also try the following one

=TEXT(MIN(A2:A6),"0;;")

 

[Aladin Akyurek]

=if(count(a2:a6),min(a2:a6),"")

 

[jessamarie]

Thank you! It works perfectly. I but I have another problem encountered after using the formula,
In my A1 cell (Actual Date Started), and B1 (Actual End ) and in C1 (# actual days),

Below is my formula;

A1 Formula: =if(min(a2:a6)=0,"",min(a2:a6))
B1 Formula: =if(max(b2:b6)=0,"",max(b2:b6))
C1 Formula: ==IF(OR(ISBLANK(a1),ISBLANK(b1)),"",b1-a1+1)

It works perfectly fine when the cells in A2:A6 and B2:B6 has dates, but when it does not have any dates specified therein, the cell in C1 just appears #VALUE !

Hope to hear from you soonest.

 

[jessamarie]

Thank you! It works perfectly. I but I have another problem encountered after using the formula,
In my A1 cell (Actual Date Started), and B1 (Actual End ) and in C1 (# actual days),

Below is my formula;

A1 Formula: =if(min(a2:a6)=0,"",min(a2:a6))
B1 Formula: =if(max(b2:b6)=0,"",max(b2:b6))
C1 Formula: ==IF(OR(ISBLANK(a1),ISBLANK(b1)),"",b1-a1+1)

It works perfectly fine when the cells in A2:A6 and B2:B6 has dates, but when it does not have any dates specified therein, the cell in C1 just appears #VALUE !

Hope to hear from you soonest.

 

[Aladin Akyurek]

A1:
=if(count(a2:a6),min(a2:a6),"")

b1:
=if(count(b2:b6),max(b2:b6),"")

c1:
=if(count(a1:b1)=2,b1-a1+1,"")

 

[jessamarie]

Thank you, If it's not too much to ask, please help me with one more problem.

I want to compute the percentage of the ff.
A1- Plan Start (date)
B1- Plan End (Date)
C1- Actual Start (Date)
D1- Actual End (Date)

I want to make a formula wherein I can compute the percent of Progress of my project

 

[Aladin Akyurek]

Thank you,

You are welcome.

If it's not too much to ask, please help me with one more problem.

I want to compute the percentage of the ff.
A1- Plan Start (date)
B1- Plan End (Date)
C1- Actual Start (Date)
D1- Actual End (Date)

I want to make a formula wherein I can compute the percent of Progress of my project

Care to provide an example how it is manually computed?

 

[jessamarie]

This is the formula i use, and I am not sure if its correct coz sometimes the percentage exceeds to 100%
=SUMPRODUCT(A10:B16)/SUMPRODUCT(C10:D16) . I wanted to email you my excel format as I cannot explain thoroughly, I dont know how to attached image on this thread.