For Bernard please

[John Holt]

Bernard, I am so appreciative of your help, however I think I have confused
you ,so I will list again what I am trying to achieve.

Col E is the buy date
Col F is the Sell date
Col G is the Buy Price
Col H is the Sell Price
Col I is the profit ONLY if the profit has occurred in 365 or less days
Col J is the profit ONLY if the profit has occurred in 366 or more days
Col K shows any loss that is made irrespective of what time period

I wish that all cells are blank until a value has been inputted.

John Holt
(e-mail address removed)

 

[J.E. McGimpsey]

Bernard, I am so appreciative of your help, however I think I have confused
you ,so I will list again what I am trying to achieve.

Col E is the buy date
Col F is the Sell date
Col G is the Buy Price
Col H is the Sell Price
Col I is the profit ONLY if the profit has occurred in 365 or less days
Col J is the profit ONLY if the profit has occurred in 366 or more days
Col K shows any loss that is made irrespective of what time period

I wish that all cells are blank until a value has been inputted.

One way:

I1: =IF((F1-E1<366)*(H1>G1),H1-G1,"")
J1: =IF((I1="")*(H1>G1),H1-G1,"")
K1: =IF(G1>H1,G1-H1,"")

Now, what should happen if the profit is zero?

 

[John Holt]

On 10/9/03 2:20 PM, in article
(e-mail address removed), "J.E. McGimpsey"

Thank you 2 of the formulea worked however the first one ie. I1 didn't, is
it possible for you to reconsider this one please.

 

[John Holt]

On 10/9/03 2:20 PM, in article
(e-mail address removed), "J.E. McGimpsey"

Thank you 2 of the formulea worked however the first one ie. I1 didn't, is
it possible for you to reconsider this one please.

Upon reflection only K works correctly it is hard to explain can I email
this very small excel file as an attachment to someone who could alter it to
make it work please.

Thank you

John Holt

 

[Bernard Rey]

John Holt wrote :

Upon reflection only K works correctly it is hard to explain can I email
this very small excel file as an attachment to someone who could alter it to
make it work please.

Try and modify a bit the formulas in order to fit your needs:

I1: =IF((SUM(E1:H1)>0)*(F1-E1<366)*(H1>G1),H1-G1,"")
J1: =IF((SUM(E1:H1)>0)*(F1-E1>=366)*(H1>G1),H1-G1,"")
K1: =IF((SUM(E1:H1)>0)*(G1>H1),G1-H1,"")

Now, as J.E. stated, what should happen if the profit is zero?

Can you explain in what the results differ from what you expect, and what
modifications you tried to bring, with what effect?

 

[John Holt]

Thank you Bernard and J E McGimpsey all is now fine
Column J is fine using =IF((SUM(E1:H1)>0)*(F1-E1>=366)*(H1>G1),H1-G1,"")
Column K is fine using =IF(G1>H1,G1-H1,"")
Column I is fine using using
=IF((SUM(E2:H2)>0)*(F2-E2<366)*(H2>G2),H2-G2,"")

When no profit is made both I and L columns are blank which is fine

Thanks again both of you.

John Holt