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

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" <cincode5@discussions.microsoft.com> wrote in message
news:73706842-81FC-4CB8-BC10-B4B70BAD0E71@microsoft.com...
> 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...

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" <cincode5@discussions.microsoft.com> wrote in message
> news:73706842-81FC-4CB8-BC10-B4B70BAD0E71@microsoft.com...
> > 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...
>
>