How does this work?

  • Thread starter Thread starter aresar
  • Start date Start date
A

aresar

I received the following formula from this forum, and I want to us
something similar to it for another function.

MIN(IF(('PIPE IN DATA'!$A$3:$A$500=C20)*('PIPE I
DATA'!$D$3:$D$500),'PIPE IN DATA'!$D$3:$D$500,""))

The question:

In the logical test part, how does excel see the * symbol. I though
is was the multiplier symbol, but it is not serving that purpose here?
(The 'PIPE IN DATA' is another sheet being referenced)

Any input would be appreciated
 
Hi

It is being used as a condition tester. If 'PIPE IN DATA'!$A$3:$A$500=C20,
then it returns a 1; if it doesn't it returns a 0. This is then used to
multiply the relative figure in column D

Andy.
 
Hi
in this case '*' multiplies two boolean values (the results of the
comparisons). Excel converts boolean values to numbers in mathematical
operations (TRUE = 1; FALSE = 0). Son in your example '*' acts as an
AND combination. e.g.
TRUE * TRUE = 1 * 1 = 1 = TRUE
TRUE * FALSE = 1 * 0 = 0 = FALSE
FALSE * TRUE = 0 * 1 = 0 = FALSE
FALSE * FALSE = 0 * = = FALSE
 
If I was to take out the MIN in the function, and made it just an I
function.

MIN(IF(('PIPE IN DATA'!$A$3:$A$500=C20)*('PIPE I
DATA'!$D$3:$D$500),'PIPE IN DATA'!$D$3:$D$500,""))

What would happen if there multiple values in A3:A500 which matche
C20?

Example: C20=2 and A5=2, A25=2

How would the function handle this situation
 
Hi

The function will return the smallest (MIN) value from the relative row in
D3:D500 of the matching values.

Andy.
 
If I take out the MIN it leaves the cell blank like it is false if ther
are duplicate values in the column.

Any ideas as to why it would do this?

IF(('PIPE IN DATA'!$A$3:$A$500=C20)*('PIPE IN DATA'!$D$3:$D$500),'PIP
IN DATA'!$D$3:$D$500,"")

This is what I used
 
Hi

I'm guessing that it cannot return an array (of all of the values that
apply) into a single cell. If you copy the formula for each line, none
array, it's fine.

Andy.
 
it will either bring back what is in C20, or say FALSE.

HTH

*edit*: btw, that's not as an array formula (which won't work in thi
case)
 
Back
Top