Give date when dynamic range exceeds value for only the first time

sarao18592

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 2019
  2. 2016
Platform
  1. MacOS
This is my first post here so i apologize if I have not provided the appropriate information! I would love to be able to get help with my current problem!

Column A contains a varied range of values which are linked to another sheet. Hence when that sheet changes, the values in Column A change.
In column B, I would like to know the date when the Values in Column A exceed 30. The values in column A will change every 12 hours and will only irregularly increase over time.

Furthermore, in column B, I would like only the value when it exceeds for the first time. i.e. if A2 > 30 for the first time on 4th March then when the data refreshes on 5th march, B2 will still show 4th March. and I would like to repeat the same for the rest of the column.

Note: Each A value has a different start point and will increase irregularly, hence the date in the B column will be different.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
This is my first post here so i apologize if I have not provided the appropriate information! I would love to be able to get help with my current problem!

Column A contains a varied range of values which are linked to another sheet. Hence when that sheet changes, the values in Column A change.
In column B, I would like to know the date when the Values in Column A exceed 30. The values in column A will change every 12 hours and will only irregularly increase over time.

Furthermore, in column B, I would like only the value when it exceeds for the first time. i.e. if A2 > 30 for the first time on 4th March then when the data refreshes on 5th march, B2 will still show 4th March. and I would like to repeat the same for the rest of the column.

Note: Each A value has a different start point and will increase irregularly, hence the date in the B column will be different.

It would easier if you share a data set sample via XL2BB

If I understood your requirement, there are 3 ways of doing so:

1- if your office version is Office 365, use Filter formula
2- if it is not. then either the below proposed long formula (drag down as per the count number)
3- alternativly, create a pivot table, apply a filter and refresh the table whenever new data are entered/replaced in Column A

Book1
ABCDEFGH
11601-Feb-21above30
22402-Feb-21
34503-Feb-21Method 1CountMethod 2
41204-Feb-2103-Feb-21205-Feb-21
53705-Feb-2105-Feb-2103-Feb-21
61606-Feb-21
72107-Feb-21
82808-Feb-21
Sheet1
Cell Formulas
RangeFormula
E4:E5E4=FILTER(B:B,(A:A>E1))
G4G4=COUNTIF(A:A,">"&E1)
H4:H5H4=IFERROR(INDEX($B:$B,AGGREGATE(14,4,IFERROR(ROW(A:A)/(A:A>$E$1),0),COUNTA($A$1:A1))),"")
Dynamic array formulas.


Regards
 

sarao18592

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 2019
  2. 2016
Platform
  1. MacOS
Hi, Thank you for the response!
I will try to share the excel sheet in a few hours. Column B currently does not contain any dates and therefore this might not work well. Instead Column B is a blank.
Column A is dependent on another sheet, that sheet changes every 12 hours and the corresponding values in Column A change every 12 hours as well.

In column B, I need the date when its respective row in Column A first crosses 30, and then this value in column B should not change thereafter.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,725
Members
417,108
Latest member
Thein Than

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