Help with formulae

[John Holt]

Could someone help with a formulae I need to complete a worksheet.
Column H is the sell price, column G is the cost price, column E is the buy
date, column F is the sell date. In column I, I wish to see the profit if
the duration is 365 days or less, and in the J column I wish to see the
profit if the duration is 366 or more days. I wish both the I and J column
cells to be blank if no information has been entered in column EFG or H
Thanks in advance, as this is beyond my knowledge

 

[Bernard Rey]

John Holt wrote :

Could someone help with a formulae I need to complete a worksheet.
Column H is the sell price, column G is the cost price, column E is the buy
date, column F is the sell date. In column I, I wish to see the profit if
the duration is 365 days or less, and in the J column I wish to see the
profit if the duration is 366 or more days. I wish both the I and J column
cells to be blank if no information has been entered in column EFG or H
Thanks in advance, as this is beyond my knowledge

In Cell I2:
=IF(AND(SUM(E2:H2)>0,(F2-E2<366)),H2-G2,"")

In Cell J2:
=IF(AND(SUM(E2:H2)>0,(F2-E2>=366)),H2-G2,"")

 

[John Holt]

John Holt wrote :


In Cell I2:
=IF(AND(SUM(E2:H2)>0,(F2-E2<366)),H2-G2,"")

In Cell J2:
=IF(AND(SUM(E2:H2)>0,(F2-E2>=366)),H2-G2,"")

Thank you Bernard, however your formulea whilst it is correct it places a
negative value in column I if a loss is made , I would prefer column I to
remain blank and a negative value be shown in column K. in these
circumstances. Is this possible, and what would the formulea now be !!!
Thanks again

 

[Bernard Rey]

John Holt wrote :

Thank you Bernard, however your formulea whilst it is
correct it places a negative value in column I if a loss
is made, I would prefer column I to remain blank and a
negative value be shown in column K. in these
circumstances. Is this possible, and what would the
formulea now be !!!

Then you'll have to add another condition in cell I2, in
order to avoid negative values:

=IF(AND(SUM(E2:H2)>0,(F2-E2<366),H2>=G2),H2-G2,"")

In cell K2, you'll add a formula catching the values
rejected from cell I2 (and thus changing only this
last "H2>=G2" condition):

=IF(AND(SUM(E2:H2)>0,(F2-E2<366),H2<G2),H2-G2,"")

* Note that you could consider each condition as a yes/no
test and obtain the same result writing:
=IF((SUM(E2:H2)>0)*(F2-E2<366)*(H2<G2),H2-G2,"")

Now what if you have negative values in J2? ;-)

 

[John Holt]

John Holt wrote :


In Cell I2:
=IF(AND(SUM(E2:H2)>0,(F2-E2<366)),H2-G2,"")

In Cell J2:
=IF(AND(SUM(E2:H2)>0,(F2-E2>=366)),H2-G2,"")

Bernard thank you again, I am most appreciative these 2 formulea work OK
however if a loss occurs ie. Col G is greater than Col H, I wish for the
negative value to be seen in Col K and nothing in Cols I or J. Your formulea
puts a negative value in Col I. Could you please correct this.

Thank you very much

John Holt

 

[Bernard Rey]

John Holt wrote :

Bernard thank you again, I am most appreciative these 2 formulea work OK
however if a loss occurs ie. Col G is greater than Col H, I wish for the
negative value to be seen in Col K and nothing in Cols I or J. Your formulea
puts a negative value in Col I. Could you please correct this.

Did my previous answer (see: http://makeashorterlink.com/?P23F429D5 ) to
this question bring you the answer? If not what else did you expect?