Macro/Formula for which value occurs first?

mlarson · Oct 3, 2011

Hi there, I would like to figure out which value occurs first (either +0.01 or -0.05) in column E for each given date (column B). If +0.01 occurs first, I’d like “1” placed in column F in the first row listed for a given date. If -0.05 occurs first, I’d like “5” placed in column F. If neither occurs during the given date, I’d like the value of column E of the final row of the given date to be placed in column F of the first row of the given date.

A sample of my data is below (actual spreadsheet contains many dates and times). Column F shows the desired output. Thank you for any help you can provide!

<table border="0" cellpadding="0" cellspacing="0" width="399"><col style="width: 48pt;" width="64" span="4"> <col style="width: 59pt;" width="79"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl63" style="width: 48pt;" width="64">B</td> <td class="xl63" style="width: 48pt;" width="64">C</td> <td class="xl63" style="width: 48pt;" width="64">D</td> <td class="xl63" style="width: 59pt;" width="79">E</td> <td class="xl63" style="width: 48pt;" width="64">F</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2</td> <td class="xl64">9/5/2011</td> <td class="xl65">10:01</td> <td class="xl63">500</td> <td class="xl63">
</td> <td class="xl63">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">3</td> <td class="xl64">9/5/2011</td> <td class="xl65">10:02</td> <td class="xl63">501</td> <td class="xl66">0.002</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">4</td> <td class="xl64">9/5/2011</td> <td class="xl65">10:03</td> <td class="xl63">500</td> <td class="xl66">0.000</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">5</td> <td class="xl64">9/5/2011</td> <td class="xl65">10:04</td> <td class="xl63">501</td> <td class="xl66">0.002</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">6</td> <td class="xl64">9/5/2011</td> <td class="xl65">10:05</td> <td class="xl63">506</td> <td class="xl66">0.012</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">7</td> <td class="xl64">9/5/2011</td> <td class="xl65">10:06</td> <td class="xl63">502</td> <td class="xl66">0.004</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">8</td> <td class="xl64">9/6/2011</td> <td class="xl65">10:01</td> <td class="xl63">465</td> <td class="xl66">
</td> <td class="xl63">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">9</td> <td class="xl64">9/6/2011</td> <td class="xl65">10:02</td> <td class="xl63">460</td> <td class="xl66">-0.011</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">10</td> <td class="xl64">9/6/2011</td> <td class="xl65">10:03</td> <td class="xl63">450</td> <td class="xl66">-0.032</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">11</td> <td class="xl64">9/6/2011</td> <td class="xl65">10:04</td> <td class="xl63">440</td> <td class="xl66">-0.054</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">12</td> <td class="xl64">9/6/2011</td> <td class="xl65">10:05</td> <td class="xl63">460</td> <td class="xl66">-0.011</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">13</td> <td class="xl64">9/6/2011</td> <td class="xl65">10:06</td> <td class="xl63">470</td> <td class="xl66">0.011</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">14</td> <td class="xl64">9/7/2011</td> <td class="xl65">10:01</td> <td class="xl63">472</td> <td class="xl66">
</td> <td class="xl66">0.006</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">15</td> <td class="xl64">9/7/2011</td> <td class="xl65">10:02</td> <td class="xl63">468</td> <td class="xl66">-0.008</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">16</td> <td class="xl64">9/7/2011</td> <td class="xl65">10:03</td> <td class="xl63">469</td> <td class="xl66">-0.006</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">17</td> <td class="xl64">9/7/2011</td> <td class="xl65">10:04</td> <td class="xl63">471</td> <td class="xl66">-0.002</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">18</td> <td class="xl64">9/7/2011</td> <td class="xl65">10:05</td> <td class="xl63">474</td> <td class="xl66">0.004</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">19</td> <td class="xl64">9/7/2011</td> <td class="xl65">10:06</td> <td class="xl63">475</td> <td class="xl66">0.006</td> <td class="xl63">
</td> </tr> </tbody></table>

Gerald Higgins · Oct 4, 2011

Hi

In your sample . . .
For 9/5/2011, the value +0.01 does not appear at all in column E, yet you have put a 1 in col F.
Similarly for 9/6/2011, the value -0.05 does not appear at all in col E, yet you have a 5 in col F.
Can you clarify this please ?

Maybe you mean, which of these values is passed first ?

Also, is ALL of your data sorted by date, then time ?

mlarson · Oct 4, 2011

Gerald, yes, I meant which value is passed first (not necessarily hitting the exact value). Yes, ALL data is sorted by date and time.

Gerald Higgins · Oct 5, 2011

Sorry to be a pain, but is it POSSIBLE that BOTH values could be passed on the same day, and you need to identify which one is passed first, or will there only ever be one value that is passed ? I'm not sure if it makes a difference, but it might.

mlarson · Oct 5, 2011

Gerald, you're not being a pain at all... as a matter of fact I appreciate your detailed questions. Yes, it is possible (and likely) that both values will be passed on the same day. Thanks in advance for your help.

Gerald Higgins · Oct 6, 2011

Well I think I've got a solution to this.
It's not very elegant, but it does produce the results you say you want.
There are probably better ways of doing this, but I just can't think of them at the moment

First, the cell references. I'm assuming that the first instance of 09/05/2011 is in cell B2, with all the rest of the data being in adjacent columns / rows, as in the OP.

I've inserted the constants 0.01 and -0.05 in cells K2 and L2.

I've used two helper columns.
In G2

Code:

=IF(G1=0,IF(AND(E2>$K$2,E1<$K$2),1,IF(AND(E2<$L$2,E1>$L$2),5,0)),0)

In H2

Code:

=IF(B2=B3,+H3,+E2)

Copy G2 and H2 down as far as required. You can then hide these columns if you don't want to see them.

Then, in F2

Code:

=IF(B2=B1,"",IF(SUMPRODUCT(--(B$2:B$19=B2),G$2:G$19)=1,1,IF(SUMPRODUCT(--(B$2:B$19=B2),G$2:G$19)>1,5,H2)))

Like I say, it's a pretty clunky, sorry, but does give the 3 expected answers.

mlarson · Oct 6, 2011

Gerald, thank you! You call it clunky but I call it beautiful as it works, thanks again!

Macro/Formula for which value occurs first?

mlarson

Well-known Member

Excel Facts

Gerald Higgins

Well-known Member

mlarson

Well-known Member

Gerald Higgins

Well-known Member

mlarson

Well-known Member

Gerald Higgins

Well-known Member

mlarson

Well-known Member

Similar threads

Forum statistics

Share this page

Macro/Formula for which value occurs first?

mlarson

Well-known Member

Excel Facts

Gerald Higgins

Well-known Member

mlarson

Well-known Member

Gerald Higgins

Well-known Member

mlarson

Well-known Member

Gerald Higgins

Well-known Member

mlarson

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock