Formula problems

[ims121uk]

My problem is I am trying to create a formula that would calculate some
figures.

FORMULA: -

IF D15 is equal to D1720 display result, IF not display result
D1720.

all these results would be display in cell D16.

many thanks

 

[WLMPilot]

What is D1720 suppose to be?

 

[ims121uk]

D1720 is the range of cells used in the formula. What it is i would
like to calculate D15 equal D1720 then display in D16 or if not pick
the lowest value from D1720.

 

[ims121uk]

=IF(COUNTIF(D1720,D15)=4,D15,MIN(D1720)) is the correct formula
and it works many thanks for that. But i would like to count blanks as
well. How is this achieved?

 

[Roger Govier]

Hi

Do you mean something like
=IF(SUMPRODUCT((D1720=D15)+(D1720=""))=4,D15,MIN(D1720))

which will count nulls in D1720 and values in D1720 which are the
same as D15.
If any value in the range equals D15, or if it is null, it will be
included in the count.
If the above results in 4, then it will give the value that is in D15,
or the lowest non-empty cell value between D1720

 

[ims121uk]

Hi Roger,

It doesn't really work, because cell D16 doesn't become blank, where
there is no data for D15 and D1720. I did used a countblank function
which works. But it wasn't with a countif function as well. Can you
help

cheers

Imran

 

[Tom Ogilvy]

=if(or(D15="",CountBlank(D17:20)>0),"",IF(COUNTIF(D1720,D15)=4,D15,MIN(D1720)))

is a possibility. It depends on how you want blanks treated. In this case,
if there is a blank anywhere in D15 or D17:20, then D16 appears blank as
well.

 

[ims121uk]

Many thanks tom it working fine.

=if(or(D15="",CountBlank(D17:20)>0),"",IF(COUNTIF(D1720,D15)=4,D15,MIN(D1720)))

is a possibility. It depends on how you want blanks treated. In this case,
if there is a blank anywhere in D15 or D17:20, then D16 appears blank as
well.

 

[Roger Govier]

Hi Tom

That seems to do it, when you correct the small typo
CountBlank(D17:20)
to
CountBlank(D1720)

 

[Tom Ogilvy]

Thaks for pointing that out. Looks like the OP already pegged it. <g>