Struggling with an IF Query

martnarts

New Member
Joined
Aug 8, 2016
Messages
1
I have the following data

ABCDEF
16681.076695.216680.586695.13RED
26678.156681.066677.226681.06RED
36674.46678.886673.576678.13RED
46678.366679.546674.076674.31GREEN
56677.786678.586677.586678.25RED

<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...

ABCDEF
16681.076695.216680.586695.13RED
26678.156681.066677.226681.06RED2.91
36674.46678.886673.576678.13RED
46678.366679.546674.076674.31GREEN-0.05
56677.786678.586677.586678.25RED-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
Joined
Dec 21, 2009
Messages
3,386
Is this what you are after?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Header1</td><td style=";">Header2</td><td style=";">Header3</td><td style=";">Header4</td><td style=";">Header5</td><td style=";">Header6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6681.07</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6695.21</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6680.58</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6695.13</td><td style="color: #333333;background-color: #FAFAFA;;">RED</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">14.06</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6678.15</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6681.06</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6677.22</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6681.06</td><td style="color: #333333;background-color: #FAFAFA;;">RED</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6674.4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6678.88</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6673.57</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6678.13</td><td style="color: #333333;background-color: #FAFAFA;;">RED</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">3.73</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6678.36</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6679.54</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6674.07</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6674.31</td><td style="color: #333333;background-color: #FAFAFA;;">GREEN</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6677.78</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6678.58</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6677.58</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6678.25</td><td style="color: #333333;background-color: #FAFAFA;;">RED</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0.47</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">F1>0,NOT(<font color="Green">ISTEXT(<font color="Purple">F1</font>)</font>)</font>),0,IF(<font color="Red">E2="RED",D2-A2,A2-D2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
header1header2header3header4header5header6
6681.076695.216680.586695.13RED14.06
6678.156681.066677.226681.06RED#######
6674.46678.886673.576678.13RED3.73
6678.366679.546674.076674.31GREEN
6677.786678.586677.586678.25RED0.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
Joined
Apr 11, 2010
Messages
11,005
comfy - amazing coincidence we both thought headers would help and both used header not heading - I put them in th sort out F2....
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top