Formula's

[lakeashtonstuff]

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.

 

[Von Pookie]

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]

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]

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]

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