Advanced Excel Question

[mikevic]

Hey People,

I am trying to write a formula that will look at a table that contains 12 monthly columns (monthly data). As the year goes by the current month will fill in that months data...so except for December there will be columns blank (without data). This formula needs to be able to look at the columns on the left and determine which month the data starts. There are multiple rows, sometimes no data is available for a certain row, so this formula has to be able to determine (based upon all rows being greater than zero) where to begin its computation. Ultimately, I will need to show what percentage this row is to the total of all rows for the given month.

Anybody that can help....it will be greatly appreciated.

 

[mikevic]

Quick question...
What does the 2,1 indicate below? Also, why will this formula not work when the cell contains a zero and I insert a 0 in between the ""?

Thanks


=LOOKUP(2,1/(B2:M2<>""),B2:M2)

 

[Robert Mika]

Quick question...
What does the 2,1 indicate below? Also, why will this formula not work when the cell contains a zero and I insert a 0 in between the ""?

Thanks


=LOOKUP(2,1/(B2:M2<>""),B2:M2)

This part
B2:M2<>""
is expecting nothing so when you have 0 this will not work
Change to
=LOOKUP(2,1/(B2:M2<>0),B2:M2)


Explanation how this formula works:
http://www.ozgrid.com/forum/showthread.php?t=69525&page=1

 

[Aladin Akyurek]

This part
B2:M2<>""
is expecting nothing so when you have 0 this will not work
Change to
=LOOKUP(2,1/(B2:M2<>0),B2:M2)


Explanation how this formula works:
http://www.ozgrid.com/forum/showthread.php?t=69525&page=1

Closer home:

http://www.mrexcel.com/forum/showthread.php?p=492425

 

[mikevic]

Thanks to Robert, Aladin, and all for the help....this formula works great. Great Job Guys!