nightmare formula

[jc.shapiera]

Got a curly one for the top brass...
On one sheet I have a client listing with dates in one colum for their
"presentation". in the next colum are the dates of those who returned.
I need to find a conversion rate formula based upon 1st of the month
to last day of month.
So far I have: {=SUM(((Presentations)>=VALUE("1 Jan 2009"))*
((Presentations)<=VALUE("31 Jan 2009")))} . This formula interegates
values between the given dates and gives a numerical return of X. I
need to add to this to find "who returned" in the next colum taking
into mind that I cannot apply the formula to the whole of the next
colum as the clients date of return does vary. This needs to be
tracked on a month by month basis and then apply other vales to find a
% of the returns. I have tried vlookup but cannot find anyway to step
into the cell next door based upon the formula above. Any help is most
appreciated - I am really stuck on this one.

regards

 

[Harlan Grove]

(e-mail address removed) wrote...
....

So far I have: {=SUM(((Presentations)>=VALUE("1 Jan 2009"))*
((Presentations)<=VALUE("31 Jan 2009")))} . . . I
need to add to this to find "who returned" in the next colum taking
into mind that I cannot apply the formula to the whole of the next
colum as the clients date of return does vary. . . .

If Presentations refers to a cell in one column and Returns refers to
the cell one column to the right of the Presentations cell, try

=IF(AND(TEXT(Presentations,"yyyymmm")="2009Jan",COUNT
(Returns)),Returns,"")

On the other hand, it looks like your original formula is an array
formula, so Presentations would seem to be a multiple cell range. If
so, it's unclear what exactly you want. If you want the first date of
return after Jan 2009 for those with presentations in Jan 2009, try
the array formula

=MIN(IF((TEXT(Presentations,"yyyymmm")="2009Jan")
*(Returns>DATE(2009,1,31)),Returns))

If you want something else, please describe in greater detail ALL the
data fields involved, what their rough size/shape is (single or
multiple column, single or multiple row), and how the result should be
determined from the data.

 

[Shane Devenshire]

Hi,

Show us some sample data and the results you would expect with that sample.
Your question is far from clear.

 

[jc.shapiera]

(e-mail address removed) wrote...

...


If Presentations refers to a cell in one column and Returns refers to
the cell one column to the right of the Presentations cell, try

=IF(AND(TEXT(Presentations,"yyyymmm")="2009Jan",COUNT
(Returns)),Returns,"")

On the other hand, it looks like your original formula is an array
formula, so Presentations would seem to be a multiple cell range. If
so, it's unclear what exactly you want. If you want the first date of
return after Jan 2009 for those with presentations in Jan 2009, try
the array formula

=MIN(IF((TEXT(Presentations,"yyyymmm")="2009Jan")
*(Returns>DATE(2009,1,31)),Returns))

If you want something else, please describe in greater detail ALL the
data fields involved, what their rough size/shape is (single or
multiple column, single or multiple row), and how the result should be
determined from the data.

Yes - sorry guys it was a little unclear --
the colums are set accordingly:

A B
C D E
01 | Client name |Presentations| Sign Up 1 | Sign Up 2
| Program
02 | Speers Carl | 03 Jan 09 | |
| To track the sales conversation rate on a
monthly basis we can see that there were
03 | Whitee Adam | 03 Jan 09 | 10 Feb 09 | 17 Feb 09 | 25
Mar 09 6 presentations in Jan 09 but only 2 of them
converted to the next stage. Giving a
04 | Lloydal Nicholas | 04 Jan 09 |
| | 33% converstion rate for stage 1 to 2 for
Jan 09. Tracking the same Jan clients
05 | Oslandy Simone | 14 Jan 09 | 19 Jan 09 | 06 Feb 09
| we had a 100% converstion rate from stage
2 to 3 and then 50% from Sign Up 2
06 | Smithers Tracy | 15 Jan 09 |
| | to program.
07 | Free Andrew | 16 Jan 09 | | |
08 | Colesie Kevin | 06 Feb 09 | 14 Feb 09 | 08 Mar 09
| February figures are 5 presentations; 3
converstions to Sign Up1 and 100% to
09 | Youngster Peter | 07 Feb 09 | |
| Sign up 2
10 | Deremp Mark | 07 Feb 09 | 16 Feb 09 | 22 Feb 09 |
11 | Doylesman Rob | 11 Feb 09 | | |
12 | Yorkel Walter | 12 Feb 09 | 22 Feb 09 | 10 Mar 09 | 12
Mar 09
13 | Styrus Wendy | 04 Mar 09 | |
| March 09 - so far - 4 Presentations; 3
converstions to Sign Up 1 100% to Sign up
14 | Goodman Susan | 05 Mar 09 | 17 Mar 09 | 27 Nov 07
| 2 with 1 so far doing the Program
15 | Taylor Marie | 06 Mar 09 | 18 Mar 09 | 28 Sep 07 |
16 | Whitbrown David | 06 Mar 09 | 07 Mar 09 | 10 Mar 09 |
11 Mar 09

The only formula I have so far is =SUMPRODUCT(((Presentations)>=VALUE
("1 Jan 2009"))*((Presentations)<=VALUE("31 Jan 2009"))) which gives
me 6 presentations done in Jan 09 (I changed to SUMPRODUCT to get same
result as SUM without the ctrl-shift-enter). I need to now track these
6 and see a resultant %rate for converstions per month. So - an
another sheet converstions for Jan 09 should show =(1 Jan 09 - 31 Jan
09=6 presentations) / (signup1 "of those clients only" =2) =33%
converstion rate for Jan 09. Any help appreciated.
regards
Jonathan

 

[jc.shapiera]

Hi,

Show us some sample data and the results you would expect with that sample.  
Your question is far from clear.

Yes - sorry guys it was a little unclear --
the colums are set accordingly:

A B C
D E F
01 | Client name |Presentations| Sign Up 1 | Sign Up 2 |
Program
02 | Speers Carl | 03 Jan 09 |
| | To track the sales conversation
rate on a monthly basis we can see that there were
03 | Whitee Adam | 03 Jan 09 | 10 Feb 09 | 17 Feb 09 |
25 Mar 09 6 presentations in Jan 09 but only 2 of them converted to
the next stage. Giving a
04 | Lloydal Nicholas | 04 Jan 09 |
| | 33% converstion rate for
stage 1 to 2 for Jan 09. Tracking the same Jan clients
05 | Oslandy Simone | 14 Jan 09 | 19 Jan 09 | 06 Feb
09 we had a 100% converstion rate from stage 2
to 3 and then 50% from Sign Up 2
06 | Smithers Tracy | 15 Jan 09 |
| | to program.
07 | Free Andrew | 16 Jan 09 |
| |
08 | Colesie Kevin | 06 Feb 09 | 14 Feb 09 | 08 Mar
09 February figures are 5 presentations; 3
converstions to Sign Up1 and 100% to
09 | Youngster Peter | 07 Feb 09 | |
| Sign up 2
10 | Deremp Mark | 07 Feb 09 | 16 Feb 09 | 22 Feb 09
|
11 | Doylesman Rob | 11 Feb 09 |
| |
12 | Yorkel Walter | 12 Feb 09 | 22 Feb 09 | 10 Mar 09
| 12 Mar 09
13 | Styrus Wendy | 04 Mar 09 | |
| March 09 - so far - 4 Presentations;
3 converstions to Sign Up 1 100% to Sign up
14 | Goodman Susan | 05 Mar 09 | 17 Mar 09 | 27 Nov
07 2 with 1 so far doing the Program
15 | Taylor Marie | 06 Mar 09 | 18 Mar 09 | 28 Sep 07
|
16 | Whitbrown David | 06 Mar 09 | 07 Mar 09 | 10 Mar 09
| 11 Mar 09


The only formula I have so far is =SUMPRODUCT(((Presentations)>=VALUE
("1 Jan 2009"))*((Presentations)<=VALUE("31 Jan 2009"))) which gives
me 6 presentations done in Jan 09 (I changed to SUMPRODUCT to get same
result as SUM without the ctrl-shift-enter). I need to now track these
6 and see a resultant %rate for converstions per month. So - an
another sheet converstions for Jan 09 should show =(1 Jan 09 - 31 Jan
09=6 presentations) / (signup1 "of those clients only" =2) =33%
converstion rate for Jan 09. Any help appreciated.
regards
Jonathan