Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nightmare formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nightmare formula
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",CO UNT (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") *(ReturnsDATE(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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nightmare formula
Hi,
Show us some sample data and the results you would expect with that sample. Your question is far from clear. -- If this helps, please click the Yes button. Cheers, Shane Devenshire " wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nightmare formula
On 15 Mar, 06:21, Harlan Grove wrote:
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",CO UNT (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") *(ReturnsDATE(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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nightmare formula
On 15 Mar, 06:27, Shane Devenshire
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Resorting nightmare | Excel Discussion (Misc queries) | |||
sumif nightmare! | Excel Worksheet Functions | |||
Formula Nightmare | Excel Worksheet Functions | |||
PivotTable Nightmare | Excel Discussion (Misc queries) | |||
Help - array formula nightmare!!!! | Excel Worksheet Functions |