if statement with index match understanding

[andycreber]

Hi

I have been given this formula but I don't understand some parts in the false section, please could someone explain the parts of the formula to me in detail, especially what the (1- means

many thanks in advance

{=IF($U2="Fuel Card","",IF(ISNUMBER(MATCH($P2,$P1:$P1,0)),INDEX($CV$1:$CV1,MATCH(P2,$P1:P1,0)),MIN(IF($P$2:$P$12500=$P2,IF(1-($U$2:$U$12500="Fuel card"),$AM$2:$AM$12500)))))}

 

[kbrummert]

Do you have a copy of the spreadsheet so we have a frame of reference?

 

[andycreber]

Do you have a copy of the spreadsheet so we have a frame of reference?

no but I can tell you what the references relate to the type of data

column u = text (vehicle id number)
column p = number and text ( reg number)
column cv = date format (this formula)
column am = date format

does that help

 

[thisoldman]

That's an unusual formula. I assume the formula is in a cell in row 2 of the spreadsheet. Is the formula to be copied downward, or copied leftward or rightward?

Starting from the end, IF(1-($U$2:$U$12500="Fuel card"),$AM$2:$AM$12500).

Excel will convert the value TRUE to the integer 1, and FALSE to the integer 0, whenever a conditional expression is used in an arithmetic operation. Here are some examples you can try in a blank cell:
=1+TRUE evaluates to 2, and =1-TRUE evaluates to 0.
=1+FALSE evaluates to 1, and =1-FALSE evaluates to 1.
=1*TRUE evaluates to 1, and =1*FALSE evaluates to 0.
This is sometimes called Boolean arithmetic.

Excel will also coerce the number zero into FALSE, but any other number becomes TRUE. Try this:
=IF(0,"It's true!","It's false!") returns "It's false!"
Then substitute any other number for the zero (including decimal numbers) and the formula will return "It's true!"

So, 1-($U$2:$U$12500="Fuel card") is going to evaluate to an array of ones and zeros. Since it's the test expression in an IF, Excel is going to convert the ones and zeros to an array of TRUE and FALSE values.

It would have been clearer to have written:
IF($U$2:$U$12500<>"Fuel card", $AM$2:$AM$12500)
This is going to return an array where the values will be either FALSE or the values of the cells in row $AM.

<sidebar> You could get rid of the IF using Boolean arithmetic and rewrite this part as
($U$2:$U$12500<>"Fuel card")* $AM$2:$AM$12500 </sidebar>

More to follow. . . .

 

[andycreber]

many thanks

 

[thisoldman]

Still working back from the end of the formula.

I wrote my previous post before knowing content types of the cells. I now know ''IF(1- ..." is going to return either a date values or FALSE values. Don't get rid of the last IF in the formula, as I suggested above. The MIN function doesn't include Booleans when it determines the minimum value.

MIN(IF($P$2:$P$12500=$P2,IF(1-($U$2:$U$12500="Fuel card"),$AM$2:$AM$12500)))
This returns the earliest date in column AM for the registration number in P2. It excludes dates from consideration where the corresponding cell in column U contains the value "Fuel card".

IF(ISNUMBER(MATCH($P2,$P1:$P1,0)),INDEX($CV$1:$CV1,MATCH(P2,$P1:P1,0)),MIN(IF...
This part has me scratching my head. The conditional expression, if it is written correctly, is only checking the one cell immediately above P2 for a duplicate value. I suspect the author meant to write:

IF(ISNUMBER(MATCH($P2,$P$1:$P1,0)),INDEX($CV$1:$CV1,MATCH(P2,$P$1:P1,0)),MIN(IF...

That correction would check for duplicates in all the cells in column P above the current row when the formula is copied downward.

As currently written, it checks P1 (only) to see if it equals P2. If P2 equals P1, it returns the date from CV1.
When P2 does not equal P1, the MIN is evaluated.

When copied downward into CV3, the formula as written will check if P3 equals P2. If they are equal, it returns the date from CV2.

To summarize the whole formula:
If U2 = "Fuel card", return the empty string, "".
Else, return the earliest date in AM that matches the registration number in P2.