• 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.

Return last occurrence of a non blank column with certain conditions [SOLVED]

A

a_dani20

New Member
hii guys..


I have got one file on which I update growth and outstanding on weekly basis. What i need is that on the basis of week, respective monthly and quarterly numbers should get automatically updated.

There are two major issues i am facing in doing so:-

1) How to identify last non blank cell and return value of the same

2) I need to report growth on cumulative basis and outstanding on as on basis. That means if i have updated values for first three weeks of April then growth number which will appear in April Month Column will be sum of growth of all weeks, however outstanding number will be of latest week.

File is attached at below link for further reference:-

https://docs.google.com/file/d/0B_ERYRYCgSUXRTUwQzhJWWl3LTg/edit?usp=sharing


Thanks in advance..
 
Hi a_dani20,


I just downloaded the file, can you explain the problem with an example referring to your sheets plus the resulting figures?


Regards,
 
Hi Faseeh,


Thanks for prompt reply..

For example

Growth :- In cell H6 i need sum of all weeks of April which will be 150, similarly for Cell I6 (May) sum will be 196 and for Cell J6 (June)it will be 96. Since July Now total of April, May and june i.e 442 should come in cell D6 (Q1).

Outstanding:- In cell H7 i need value of last week of April which will be 22587, similarly for Cell I7 (May) value will be 22783 and for Cell J6 (June)it will be 22879. Afterwords, as soon as i update value For the period 15 Jun to 21 Jun, cell J6 should reflect outstanding as on that week. For Q1 (cell d7) value should be 22879.


Hope this clarifies..
 
Hi,


In D6 write:
Code: 
=SUMIF($M$2:$AC$2,D4,$M$6:$AC$6)

In D7 write: [code]=OFFSET($A7,0,MAX((ISBLANK($M$7:$X$7)=FALSE)*COLUMN($M$7:$X$7))-1) Press Ctrl Shift Enter.

In H6 write: =SUMIF($M$3:$AC$3,H4,$M$6:$AC$6)
...drag to right

in H7 write: =IFERROR(OFFSET($A7,0,MAX(($M$3:$X$3=H4)*(ISBLANK($M$7:$X$7)=FALSE)*COLUMN($M$7:$X$7))-1),"-")[/code] Press Ctrl+Shift+Enter and drag to right.


Regards,
 
Hi ,


In D7 , I think it should be :


=IFERROR(INDEX($A$7:$AC$7,IF(MAX(($M$3:$AC$3=H$4)*($M$7:$AC$7<>"")*(COLUMN($M$3:$AC$3)))=0,999,MAX(($M$3:$AC$3=H$4)*($M$7:$AC$7<>"")*(COLUMN($M$3:$AC$3))))),0)


entered as an array formula , using CTRL SHIFT ENTER. Copy this across.


Narayan
 
Hi Narayan!


I also thought.. according to Sheet.. D7 should be latest for Q1 & April..


But OP already stated

For Q1 (cell d7) value should be 22879.
Click to expand...

May be this CSE will work..

Code: 
=INDEX($M$7:$AC$7,,MATCH(TRUE,ISBLANK($M$7:$AC$7),0)-1)


Regards,

Deb
 
Hi Deb ,


No , my mistake was in writing D7 , when I actually meant H7 ; the formula I gave was for H7 , I7 , J7 and so on.


Q1 clearly refers to April , May and June , for which 22879 is the correct answer.


Narayan
 
Hi ,


For D7 , the formula would be :


=IF(MAX(($M$2:$BY$2=D4)*($M$7:$BY$7<>"")*(COLUMN($M$7:$BY$7)))=0,0,INDEX($A$7:$BY$7,MAX(($M$2:$BY$2=D4)*($M$7:$BY$7<>"")*(COLUMN($M$7:$BY$7)))))


entered as an array formula , using CTRL SHIFT ENTER. Copy this across.


I have entered BY as the end column arbitrarily ; this can be extended to what ever is the final column.


Narayan
 
Hello Dani,


Try these with JUST ENTER.


D6, then copy till G6;


=SUMIF($M2:$AC2,D4,$M6:$AC6)


H6, then copy till L6;


=SUMIF($M3:$AC3,H4,$M6:$AC6)


D7, then copy till G7;


=LOOKUP(2,1/($M2:$AC2=D4)/ISNUMBER($M7:$AC7),$M7:$AC7)


H7, then copy till L7;


=LOOKUP(2,1/($M3:$AC3=H4)*ISNUMBER($M7:$AC7),$M7:$AC7)


Use error handler to avoid #N/A in D7:L7. If you are on Excel 2007 or later use IFERROR, or this version will work in ALL versions.


D7:H7;

=LOOKUP(9.9E+300,CHOOSE({1,2},0,LOOKUP(2,1/($M2:$AC2=D$4)/ISNUMBER($M7:$AC7),$M7:$AC7)))


H7:L7;

=LOOKUP(9.9E+300,CHOOSE({1,2},0,LOOKUP(2,1/($M3:$AC3=H4)*ISNUMBER($M7:$AC7),$M7:$AC7)))


Hope this helps;

Haseeb
 
Hii guys...


Thank you all for your fascinating solutions...


Narayan's formula worked like a charm (though i didnt understand how it works), would really appreciate if Narayan can throw some light on basic functioning of this formula...


Regards..

Amit
 
Hi Amit ,


I think the formula can be understood if it is broken down into its components :


First the formula in H7 , which is easier to formulate : we want to return the value from the last column which has Apr in row 3 ; you have made it easier by putting these values of Apr , May , Jun ,... in this row ; we can also put formulae in this row based on the contents of the cells in row 4.


All we have to do is :


1. Check which of the cells in the row 3 has Apr in them - this is done by the part :


($M$3:$AC$3=H$4)


2. We also have to ensure that blank cells are not included , since otherwise the last cell for Jun will return 0 as the outstanding value ; this is done by the part :


($M$7:$AC$7<>"")


3. Both of the above will return an array of TRUE / FALSE values ; we now multiply this by the column numbers of the range to arrive at the resultant column number :


COLUMN($M$3:$AC$3)


4. We now take the MAX of this to get which of the columns contains the value we are looking for ; using the INDEX function on the relevant row ( row 7 ) returns the actual outstanding value.


5. The check for 0 is because if no cell satisfies the conditions , the array will contain only 0 values , MAX will also return 0 , and an INDEX function used with 0 returns an entire row. Using 999 instead generates an error value , which can then be trapped with the IFERROR function.


6. Another point to take care of is that since we are returning the column number , this will be 13 or more ( column M is column number 13 ) ; we need to use the INDEX with a reference starting from column number 1 i.e. column A.


I am sure that this can be improved upon , but I always prefer logical and straightforward formulae which can be easily explained , unless there is a compelling reason why logical and straightforward formulae / approaches cannot be used.


For the formula in D7 , the same technique has been used ; again you have made it easier by putting in Q1 , Q2 , Q3 and Q4 in row 2 ; in the absence of these values , it would have been more difficult to arrive at which columns were part of each quarter.


In fact the formula in H7 can use the same technique as the formula in D7 to eliminate the blank values , and be rewritten as :


=IF(MAX(($M$3:$AC$3=H$4)*($M$7:$AC$7<>"")*(COLUMN($M$3:$AC$3)))=0,0,INDEX($A$7:$AC$7,MAX(($M$3:$AC$3=H$4)*($M$7:$AC$7<>"")*(COLUMN($M$3:$AC$3)))))


entered as an array formula , using CTRL SHIFT ENTER.


Narayan
 
Hi Narayan,

I would like to suggest a slight modification to your formula, by replacing MAX with LARGE, so that the main segment does not have to be repeated:


=IFERROR(INDEX($A$7:$AC$7, LARGE(IF(($M$3:$AC$3=H$4)*($M$7:$AC$7<>""), COLUMN($M$3:$AC$3)), 1)),0)


entered with Ctrl + Shift + Enter


Cheers,

Sajan.
 
brilliantly explained narayan...now i got a hang of it...thanks a ton..

P.S: Was just wondering can we somehow use sumproduct instead of using array to produce the same result..
 
Hi ,


I think using SUMPRODUCT would not work , since the conditions may return multiple values , out of which we wish to choose the largest value ; since the conditions will return arrays of 0s and 1s , the SUMPRODUCT function would operate on a final array such as {0;0;0;0;0;0;19;20;21;0;0;0;0} ; the result which is supposed to be 21 , would instead be 60.


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