# Struggling with an IF Query

#### martnarts

##### New Member
I have the following data

 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!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Comfy

##### Well-known Member
Is this what you are after?

#### oldbrewer

##### Well-known Member
 header1 header2 header3 header4 header5 header6 6681.07 6695.21 6680.58 6695.13 RED 14.06 6678.15 6681.06 6677.22 6681.06 RED ####### 6674.4 6678.88 6673.57 6678.13 RED 3.73 6678.36 6679.54 6674.07 6674.31 GREEN 6677.78 6678.58 6677.58 6678.25 RED 0.47 ###### cell F3 =IF(F2="",IF(E3="RED",D3-A3,A3-D3),IF(F2>0,"",IF(E3="RED",D3-A3,A3-D3)))

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

#### oldbrewer

##### Well-known Member
comfy - amazing coincidence we both thought headers would help and both used header not heading - I put them in th sort out F2....

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,983
Messages
5,767,438
Members
425,414
Latest member
chwein

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back