I'm looking for the cell directly below the most recent data input to show the difference in values for the two cells above.
Column A has dates, columns B & C have Gain/loss in USD and % respectively, D: market value, E: total cost, as depicted below (value are obviously simply for representation)
So potential formula for B4 would include the following arguments
- If A3 has a value and A4 is blank then calculate formula '=B3-B2'
- If both A3 & A4 have values then calculate formula '=D4-E4'
- If both A3 & A4 are blank leave cell value at '-'
A B C D E 1 Date Gain/Loss ($) Gain/Loss (%) Value Cost 2 9/08 $10 3% $20 $10 3 9/09 $12 3.5% $22 $10 4 Blank $2.0 0.5% - - 5 Blank - - - -
Currently I am using '=IF(OR(A#=""),"-",(D#-E#))' to calculate USD and % gain/loss if there's a date value present in column A and to leave as '-' if blank.
Bookmarks