I have the following data
<tbody>
</tbody>
I want cell F2 and the rest of the column to calculate the following formula:
=IF(E1="RED",D2-A2,A2-D2)
However I also want the cell to calculate if the preceding cell above it is greater than ZERO then do not calculate the =IF(E1="RED",D2-A2,A2-D2) formula and just leave a blank cell. I have come up with this but it does not work
=IF(F1>0,"",E1="RED",D2-A2,A2-D2)
When I copy this down the moment a cell is greater than 0 the following cells underneath are blank when I only want to do it for the next cell.
Hope this is making sense in short if the above cell calculation creates a figure >0 then do not do the calculation for the next cell, start again with the cell below so it would look something like this...
<tbody>
</tbody>
I'm happy to create new columns if need be.
Thanks for the help in advance!
A | B | C | D | E | F | |
1 | 6681.07 | 6695.21 | 6680.58 | 6695.13 | RED | |
2 | 6678.15 | 6681.06 | 6677.22 | 6681.06 | RED | |
3 | 6674.4 | 6678.88 | 6673.57 | 6678.13 | RED | |
4 | 6678.36 | 6679.54 | 6674.07 | 6674.31 | GREEN | |
5 | 6677.78 | 6678.58 | 6677.58 | 6678.25 | RED |
<tbody>
</tbody>
I want cell F2 and the rest of the column to calculate the following formula:
=IF(E1="RED",D2-A2,A2-D2)
However I also want the cell to calculate if the preceding cell above it is greater than ZERO then do not calculate the =IF(E1="RED",D2-A2,A2-D2) formula and just leave a blank cell. I have come up with this but it does not work
=IF(F1>0,"",E1="RED",D2-A2,A2-D2)
When I copy this down the moment a cell is greater than 0 the following cells underneath are blank when I only want to do it for the next cell.
Hope this is making sense in short if the above cell calculation creates a figure >0 then do not do the calculation for the next cell, start again with the cell below so it would look something like this...
A | B | C | D | E | F | |
1 | 6681.07 | 6695.21 | 6680.58 | 6695.13 | RED | |
2 | 6678.15 | 6681.06 | 6677.22 | 6681.06 | RED | 2.91 |
3 | 6674.4 | 6678.88 | 6673.57 | 6678.13 | RED | |
4 | 6678.36 | 6679.54 | 6674.07 | 6674.31 | GREEN | -0.05 |
5 | 6677.78 | 6678.58 | 6677.58 | 6678.25 | RED | -0.47 |
<tbody>
</tbody>
I'm happy to create new columns if need be.
Thanks for the help in advance!