Sales Forecast Question - Need Help!
Hello Excel Community,
I have a sales forecast worksheet that contains business oppurtunities entered as single deals (by row). These can routinely carry-over from month to month poviding the deal is still alive. I simply re-forecast a prediction from one month to the next as the deal progresses. Forecast potential is shown as a percentage against the total deal size, in a range of 12 cells (K:V, representing Jan - Dec). I need to know which cell (or month) in that range represents the last cell to conatin a forecasted month. For ex: if Jan, Feb, and Mar were forecasted on a deal, and Mar was the month the deal closed, then 03 is the information I need to see as a number on my worksheet. I dont always know when a deal closes so this has to be a formula that looks at the last entry in a range. (Hope this makes sense). I appreciate any feedback. Thanks everyone... -- Regards... |
Sales Forecast Question - Need Help!
Last entry in range A2:A500 would be
=INDEX(A2:A500,COUNTA(A2:A500)) this assume there are no blanks in-between, otherwise =LOOKUP(2,1/(A2:A500<""),A2:A500) if numeric entries only =LOOKUP(9.99999999999999E+307,A:A) if text entries only =INDEX(A:A,MATCH(REPT("z",255),A:A)) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "cincode5" wrote in message ... Hello Excel Community, I have a sales forecast worksheet that contains business oppurtunities entered as single deals (by row). These can routinely carry-over from month to month poviding the deal is still alive. I simply re-forecast a prediction from one month to the next as the deal progresses. Forecast potential is shown as a percentage against the total deal size, in a range of 12 cells (K:V, representing Jan - Dec). I need to know which cell (or month) in that range represents the last cell to conatin a forecasted month. For ex: if Jan, Feb, and Mar were forecasted on a deal, and Mar was the month the deal closed, then 03 is the information I need to see as a number on my worksheet. I dont always know when a deal closes so this has to be a formula that looks at the last entry in a range. (Hope this makes sense). I appreciate any feedback. Thanks everyone... -- Regards... |
Sales Forecast Question - Need Help!
Peo,
This works perfectly... Thank you very much! -- Regards... "Peo Sjoblom" wrote: Last entry in range A2:A500 would be =INDEX(A2:A500,COUNTA(A2:A500)) this assume there are no blanks in-between, otherwise =LOOKUP(2,1/(A2:A500<""),A2:A500) if numeric entries only =LOOKUP(9.99999999999999E+307,A:A) if text entries only =INDEX(A:A,MATCH(REPT("z",255),A:A)) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "cincode5" wrote in message ... Hello Excel Community, I have a sales forecast worksheet that contains business oppurtunities entered as single deals (by row). These can routinely carry-over from month to month poviding the deal is still alive. I simply re-forecast a prediction from one month to the next as the deal progresses. Forecast potential is shown as a percentage against the total deal size, in a range of 12 cells (K:V, representing Jan - Dec). I need to know which cell (or month) in that range represents the last cell to conatin a forecasted month. For ex: if Jan, Feb, and Mar were forecasted on a deal, and Mar was the month the deal closed, then 03 is the information I need to see as a number on my worksheet. I dont always know when a deal closes so this has to be a formula that looks at the last entry in a range. (Hope this makes sense). I appreciate any feedback. Thanks everyone... -- Regards... |
All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com