Formula problems
 

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

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


wrote:

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

Hi

Do you mean something like
=IF(SUMPRODUCT((D17:D20=D15)+(D17:D20=""))=4,D15,M IN(D17:D20))

which will count nulls in D17:D20 and values in D17:D20 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 D17:D20

--
Regards

Roger Govier


wrote in message
oups.com...
=IF(COUNTIF(D17:D20,D15)=4,D15,MIN(D17:D20)) is the correct formula
and it works many thanks for that. But i would like to count blanks as
well. How is this achieved?


wrote:

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

Hi Roger,

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

cheers

Imran

Roger Govier wrote:
Hi

Do you mean something like
=IF(SUMPRODUCT((D17:D20=D15)+(D17:D20=""))=4,D15,M IN(D17:D20))

which will count nulls in D17:D20 and values in D17:D20 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 D17:D20

--
Regards

Roger Govier


wrote in message
oups.com...
=IF(COUNTIF(D17:D20,D15)=4,D15,MIN(D17:D20)) is the correct formula
and it works many thanks for that. But i would like to count blanks as
well. How is this achieved?


wrote:

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

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

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.

--
Regards,
Tom Ogilvy


" wrote:

Hi Roger,

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

cheers

Imran

Roger Govier wrote:
Hi

Do you mean something like
=IF(SUMPRODUCT((D17:D20=D15)+(D17:D20=""))=4,D15,M IN(D17:D20))

which will count nulls in D17:D20 and values in D17:D20 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 D17:D20

--
Regards

Roger Govier


wrote in message
oups.com...
=IF(COUNTIF(D17:D20,D15)=4,D15,MIN(D17:D20)) is the correct formula
and it works many thanks for that. But i would like to count blanks as
well. How is this achieved?


wrote:

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

Many thanks tom it working fine.

Tom Ogilvy wrote:
=if(or(D15="",CountBlank(D17:20)0),"",IF(COUNTIF( D17:D20,D15)=4,D15,MIN(D17:D20)))

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.

--
Regards,
Tom Ogilvy


" wrote:

Hi Roger,

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

cheers

Imran

Roger Govier wrote:
Hi

Do you mean something like
=IF(SUMPRODUCT((D17:D20=D15)+(D17:D20=""))=4,D15,M IN(D17:D20))

which will count nulls in D17:D20 and values in D17:D20 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 D17:D20

--
Regards

Roger Govier


wrote in message
oups.com...
=IF(COUNTIF(D17:D20,D15)=4,D15,MIN(D17:D20)) is the correct formula
and it works many thanks for that. But i would like to count blanks as
well. How is this achieved?


wrote:

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

Hi Tom

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

--
Regards

Roger Govier


"Tom Ogilvy" wrote in message
...

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

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.

--
Regards,
Tom Ogilvy


" wrote:

Hi Roger,

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

cheers

Imran

Roger Govier wrote:
Hi

Do you mean something like
=IF(SUMPRODUCT((D17:D20=D15)+(D17:D20=""))=4,D15,M IN(D17:D20))

which will count nulls in D17:D20 and values in D17:D20 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 D17:D20

--
Regards

Roger Govier


wrote in message
oups.com...
=IF(COUNTIF(D17:D20,D15)=4,D15,MIN(D17:D20)) is the correct
formula
and it works many thanks for that. But i would like to count
blanks as
well. How is this achieved?


wrote:

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display
result
D17:D20.

all these results would be display in cell D16.

many thanks

Formula problems
 

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

--
Regards,
Tom Ogilvy

"Roger Govier" wrote in message
...
Hi Tom

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

--
Regards

Roger Govier


"Tom Ogilvy" wrote in message
...

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

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.

--
Regards,
Tom Ogilvy


" wrote:

Hi Roger,

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

cheers

Imran

Roger Govier wrote:
Hi

Do you mean something like
=IF(SUMPRODUCT((D17:D20=D15)+(D17:D20=""))=4,D15,M IN(D17:D20))

which will count nulls in D17:D20 and values in D17:D20 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 D17:D20

--
Regards

Roger Govier


wrote in message
oups.com...
=IF(COUNTIF(D17:D20,D15)=4,D15,MIN(D17:D20)) is the correct formula
and it works many thanks for that. But i would like to count blanks
as
well. How is this achieved?


wrote:

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


WLMPilot wrote:

What is D17:D20 suppose to be?



" wrote:

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

FORMULA: -

IF D15 is equal to D17:D20 display result, IF not display
result
D17:D20.

all these results would be display in cell D16.

many thanks