Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Help needed for excel formulas!!!

Hi, need help on below:-

i'm trying to have a formula that show if the Close date has been changed
for data as of May 2008 and June 2008 and if this change is a Push-out,
Pull-In or No change.
I will also like the formula to show if the close date as of June 2008 is
within the quarter or outside of the quarter.

Below is the formula i've tried to use but return no value.
=IF(AND(F2F3, F339538, F3<39630), "In-In", "In-Out"), IF(AND(F2<F3,
F339538, F3<39630), "Out-In", "Out-Out"), IF(AND(F2=F3, F339538, F3<39630),
"NC-In", "NC-Out")

F2 - close date as of May 2008
F3 - close date as of June 2008
If close date is between 1 April to 30 Jun 2008, then it's within the quarter
If close date is not within this period, then it should show as Outside of
quarter

Please help . thanks
--
nikko
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Help needed for excel formulas!!!

Hi Don
Here's a sample of the data..

For every opp, there are 2 rows of data. 1st row is the data as of May and
2nd, as of June.

I need a formula to tell if the Close date has been updated/revised between
these 2 period. if the date has been updated, is this a push out or pull in
and if the new date as of June is within the qtr or outside of qtr.

Country Opportunity Owner Opportunity ID Opportunity Name As of
Date Historical Close Date Historical Amount (converted)
China Cindy Opp1 ABC-1111 5/1/2008 5/31/2008 204,926
China Cindy Opp1 ABC-1111 6/1/2008 9/30/2008 90,463
China Andy Opp2 ABC-2222 5/1/2008 8/19/2008 111,694
China Andy Opp2 ABC-2222 6/1/2008 8/19/2008 111,694
China Ben Opp3 ABC-3333 5/1/2008 6/20/2008 912,011
China Ben Opp3 ABC-3333 6/1/2008 9/30/2008 912,011
China Cindy Opp4 ABC-4444 5/1/2008 5/23/2008 33,785
China Cindy Opp4 ABC-4444 6/1/2008 6/27/2008 33,785
Australia Jason Opp5 ABC-5555 5/1/2008 6/20/2008 162,231
Australia Jason Opp5 ABC-5555 6/1/2008 6/20/2008 162,231

Thanks!
nikko


"Don Guillett" wrote:

I'm confused.
Post your actual layout or send a workbook to my personal address below with
exactly what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"nikko" wrote in message
...
Hi, need help on below:-

i'm trying to have a formula that show if the Close date has been changed
for data as of May 2008 and June 2008 and if this change is a Push-out,
Pull-In or No change.
I will also like the formula to show if the close date as of June 2008 is
within the quarter or outside of the quarter.

Below is the formula i've tried to use but return no value.
=IF(AND(F2F3, F339538, F3<39630), "In-In", "In-Out"), IF(AND(F2<F3,
F339538, F3<39630), "Out-In", "Out-Out"), IF(AND(F2=F3, F339538,
F3<39630),
"NC-In", "NC-Out")

F2 - close date as of May 2008
F3 - close date as of June 2008
If close date is between 1 April to 30 Jun 2008, then it's within the
quarter
If close date is not within this period, then it should show as Outside of
quarter

Please help . thanks
--
nikko



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Help needed for excel formulas!!!


To start with
The first part of your formula asks if f2f3. How can the as of date be
later than the close?
Then you answer the if in the first part without giving the second and third
a chance.

=if(and(cond1,cond2),ans and stop, second if
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"nikko" wrote in message
...
Hi Don
Here's a sample of the data..

For every opp, there are 2 rows of data. 1st row is the data as of May and
2nd, as of June.

I need a formula to tell if the Close date has been updated/revised
between
these 2 period. if the date has been updated, is this a push out or pull
in
and if the new date as of June is within the qtr or outside of qtr.

Country Opportunity Owner Opportunity ID Opportunity Name As of
Date Historical Close Date Historical Amount (converted)
China Cindy Opp1 ABC-1111 5/1/2008 5/31/2008 204,926
China Cindy Opp1 ABC-1111 6/1/2008 9/30/2008 90,463
China Andy Opp2 ABC-2222 5/1/2008 8/19/2008 111,694
China Andy Opp2 ABC-2222 6/1/2008 8/19/2008 111,694
China Ben Opp3 ABC-3333 5/1/2008 6/20/2008 912,011
China Ben Opp3 ABC-3333 6/1/2008 9/30/2008 912,011
China Cindy Opp4 ABC-4444 5/1/2008 5/23/2008 33,785
China Cindy Opp4 ABC-4444 6/1/2008 6/27/2008 33,785
Australia Jason Opp5 ABC-5555 5/1/2008 6/20/2008 162,231
Australia Jason Opp5 ABC-5555 6/1/2008 6/20/2008 162,231

Thanks!
nikko


"Don Guillett" wrote:

I'm confused.
Post your actual layout or send a workbook to my personal address below
with
exactly what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"nikko" wrote in message
...
Hi, need help on below:-

i'm trying to have a formula that show if the Close date has been
changed
for data as of May 2008 and June 2008 and if this change is a Push-out,
Pull-In or No change.
I will also like the formula to show if the close date as of June 2008
is
within the quarter or outside of the quarter.

Below is the formula i've tried to use but return no value.
=IF(AND(F2F3, F339538, F3<39630), "In-In", "In-Out"), IF(AND(F2<F3,
F339538, F3<39630), "Out-In", "Out-Out"), IF(AND(F2=F3, F339538,
F3<39630),
"NC-In", "NC-Out")

F2 - close date as of May 2008
F3 - close date as of June 2008
If close date is between 1 April to 30 Jun 2008, then it's within the
quarter
If close date is not within this period, then it should show as Outside
of
quarter

Please help . thanks
--
nikko




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help Needed With Copying Formulas [email protected] Excel Worksheet Functions 6 April 11th 08 05:59 AM
IF FORMULAS NEEDED Kerri Olsen Excel Worksheet Functions 4 July 16th 07 09:21 PM
Help With Formulas Needed MAB Excel Worksheet Functions 1 January 12th 06 01:03 AM
Help needed on formulas busterbrown885 New Users to Excel 2 August 6th 05 06:26 AM
Date formulas needed David Excel Discussion (Misc queries) 9 July 24th 05 02:01 AM


All times are GMT +1. The time now is 04:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"