• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum question in condition

@Ratan Bhushan

The LOOKUP function is simply replacing all the blank cells in column A with the value in merged cells, than after that it's just equating it to equal to condition and forming array for sumproduct.

If you still have doubt in understanding any part of it, write back.

Regards,
 
Somendra Misra said:
@Ratan Bhushan

The LOOKUP function is simply replacing all the blank cells in column A with the value in merged cells, than after that it's just equating it to equal to condition and forming array for sumproduct.

If you still have doubt in understanding any part of it, write back.

Regards,
Click to expand...
LOOKUP(ROW($A$2:$A$17),ROW($A$2:$A$17)/($A$2:$A$17<>""),$A$2:$A$17)

Sorry sir, but this fundamental formula I am unable to understand, please to make me understand this function of merge cells, as I have seen many times you have done the same way to search the value. I am requesting you sir to make me understand the same
 
Sure @Ratan Bhushan .

First you need to understand LOOKUP function. So here what Excel Helps tells us:

Excel Help said:
The LOOKUP function returns a value either from a one-row or one-column range.
Click to expand...

Excel Help said:
LOOKUP syntax:
LOOKUP(lookup_value, lookup_vector, [result_vector])

Some important remark:
The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

  • If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
Click to expand...
Apart from this LOOKUP function ignore errors.

Now coming to our formula:
=LOOKUP(ROW($A$2:$A$17),ROW($A$2:$A$17)/($A$2:$A$17<>""),$A$2:$A$17)

Now as you can see the lookup value (RED colour) will be an array of row number {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}. This will be looked in the lookup vector.

Now if you merge cells say A2:A5, A2 holds the value and rest A3:A5 are blanks cells.

Now lookup vector will be row number as lookup vector divided by TRUE or FALSE. So TRUE will be for those cells which are not blanks, so in our example A2 will be TRUE and A3:A5 will be FALSE. So, we have our lookup vector as
{2/TRUE;3/FALSE;4/FALSE;5/FALSE}. Now when you do mathematical operations on TRUE & FALSE, they get converted to 0 & 1 respectively. So finally we will get the array as {1;#Div/0!;#Div/0!;#Div/0!}.

Now result vector is A2:A5. Now this will be like {"Jan";0;0;0}.

Now LOOKUP function will see first lookup vector i.e. 2, it will not find it, so it will look for a number lower than it which 1 so the result will be JAN. Now it will look 3, again it will not find so again return 1, so result is JAN and so On.

So finally we will have an array {"JAN";"JAN";"JAN";"JAN"}.

This Can be compared with Jan.

That's it.

I hope this will clear your doubt, if not than write back.

Regards,
 
You must log in or register to reply here.
Back
Top