Help with conditional formula

[Guest]

Hi All

I am using the following formula

=IF(K49=0,MIN(K4:K38),LOOKUP(MAX(J4:J38),K4:K38))

with this formula if K49 is not equal to 0 and there is no value in J4:J48
and K4:K48
It returns a #NA .
For the most part this is not a functional problem but more of an asthetic
issue.
What can I add to this formula to get it to return a Blank cell if there is
no value in
J4:J48 and K4:K48.
This will tidy up my sheet and make it look much nicer

Thanks

Dan N

crunchnin numbers

 

[Dave Peterson]

Maybe:

=IF(K49=0,MIN(K4:K38),if(count(j4:k38)=0,"",LOOKUP(MAX(J4:J38),K4:K38)))

or maybe...

=IF(K49=0,MIN(K4:K38),
if(or(count(j4:j38)=0,count(k4:k38)=0),"",LOOKUP(MAX(J4:J38),K4:K38)))

 

[Guest]

Check out the ISNA() function, which will return a boolean result. You can
wrap this with something like: If(ISNA(...),"",...)

 

[JoAnn Paules]

Jon pointed me to a link for some conditional formatting that might work for
you. If the cell says #N/A, it changes the color of the text to match the
cell background. He sent me that link and I thought, "Oh, that's nice but I
don't need it." About two hours later, I found myself using it. I needed
something in those cells because a blank cell was giving me a weird effect
on the charts I am working on.

(I've had to learn more about formulas in the last two weeks than I thought
I'd ever need to know so I've been paying more and more attention to funky
little things like this. They work and that's the name of the game.)

--

JoAnn Paules
Microsoft MVP - Publisher

How to ask a question
http://support.microsoft.com/kb/555375