Formula's

lakeashtonstuff

New Member
Joined
Mar 4, 2004
Messages
3
I have three contract types - I can only choose one of the three: CT, CS, or PL
CT is in cell F7
CS is in cell G7
PL is in cell H7

If I choose a CS or PL there are three calculations that will compute
If I choose a CT there is only one calculation that I want to compute

The formula is a forcasting formula for contracts and the date of purchase on the contract.- Which is E7

CS and PL have three calculations they are:
SUM(IF(G7&H7="x",E7+90
SUM(IF(G7&H7="x",E7+180
SUM(IF(G7&H7="x",E7+210

CS has one calculation:
SUM(IF(F7="x",E7+360)

If I choose CS or PL the formula's are producing the correct forcast dates, however, what appears in F7 is a date of 0/01/1900. How do I prevent any results from showing in F7 if I choose a CS or PL.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
I'm thinking you should be able to do this with an IF statement, but I can't quite figure out how your data is set up to provide you an example.

Could you maybe use the HTML Maker to post an example of how it's set up?
 

lakeashtonstuff

New Member
Joined
Mar 4, 2004
Messages
3
Not sure how to do the HTML
Brief overview of the spreasheet

The headers in the columns are across in this order
Date of Purchase =s DOP


Example


Contract Type
DOP CT CS PL 90 DYS 180 Dys 360 Dys

1/1/03 CS 3/1/03 7/1/03 0/1/1900
 

lakeashtonstuff

New Member
Joined
Mar 4, 2004
Messages
3
Not sure how to do the HTML
Brief overview of the spreasheet

The headers in the columns are across in this order
Date of Purchase =s DOP


Example


Contract Type
DOP CT CS PL 90 DYS 180 Dys 360 Dys
From DOP From DOP DOP

1/1/03 CS 3/1/03 7/1/03 0/1/1900


The contract information is one row- working from left to right across the columns. -- If I pick CS Contract Type, the Formula's Compute Fine. The problem is I don't want the date of 0/1/1900 to appear in the cell for 360 Days, because the formula for this type of Contract doesn't need to compute for 360 Days. Instead it is giving the funny date- And I don't want the funny date to appear- I want the cell to be blank.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
click right here to download utility. unzip, store in c:\windows\addins
inexcel, click Tools -> Addins -> Browse -> ..navigate to folder (above) -> click HTML Maker -> open -> ok
select data -> click html -> select used range -> ok -> ok -> ok -> copy to clipboard -> paste in reply window (looks like code)

HTH

ps - congrats on your 3000th post Kristy!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,777
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top