minifs | linux distribution maker to create

I have 2 columns of numbers, the idea is to find the first & last value in column L that respects a criterion and return the value from the same row in column K. As the criterion is "higher than 99% of the max value in column L", I tried the MINIFS formula, but I cannot use this as a criterion.

I guess the solution will include the MATCH, INDEX formula but I cannot find the right combination

In this specific example, we want to return the value of the first column that has in column L a number higher than 0,99*max(L3:L62)(=3.0879...) so it should return 19 This will be verified for a couple rows until the value goes below the 99% again. This last row is 58.

Link to sheet : https://docs.google.com/spreadsheets/d/1MUkYDPoR1NxB8qWcYr_2Fp91FgUbnUOpfGd7EUuWCOg/edit?usp=sharing

...

Edit: Here is a spreadsheet with dummy data.

I'm working with accounting data and trying to come up with a table like this to figure out what I have to pay in the upcoming payment:

Price Quantity Total Value 0.50 600 300.00 0.10 1000 100.00

My data is a table of prices and quantities and arbitrary dates of payment. Cell addresses in parentheses

Price (N2) Quantity (O2) (P2) 1/1/2021 (Q2) 2/9/2021 (R2) 5/2/2021 (S2) 10/5/2021 (T2) 11/4/2021 0.25 100 25.00 0.70 2 1.40 0.50 500 250.00 0.10 1000 100.00 0.50 100 50.00

The values in the date columns always equal the price multiplied by quantity, and each row only has an entry in a single date column. I want to proactively calculate how much needs to be paid in the soonest date of payment that is also in the future. So for example, since today is September 10th, it would be the amounts for October 5th, 2021.

What I've tried so far:

To do the final calculation use a query like this:

...

Background

Pretty new to Python and dataframes. I'm on a Mac (Sierra) running Jupyter Notebook in Firefox (87.0). I've got a dataframe like this:

...

The base table

...

I am trying to design an Excel formula that, based on an adjacent column's value compared to the previous row's value returns the minimum of the column. Here is a specific example:

Given this data, the value 800 should be returned, because only 1800, 9000, and 800 should be considered, because those are the first cells where the Track column changes.

I have tried a variety of combinations with MIN() MINIFS(), MIN()/IF(), INDRIECT()/ADDRESS()/ROW()... but could not come up with a working example.

In pseudocode:

...

I'm using this MINIFS formula on google sheet without problem

...

So I have a data set converted from a stock chart in 1 minute time increments and I want to extract key data points from the data set.

screenshot

The problem I am running into is when I attempt to use the INDEX function to match the MAXIF and MINIF results, the time criteria does not follow through: The first function to extract the Low of Day from this data set:

=MINIFS(E:E, B:B, ">09:30", B:B, "<16:00")

The second function I'm attempting to pull the time of when the Low of Day data point is pulled from:

=INDEX(B:B,MATCH(MINIFS(E:E, B:B, ">09:30", B:B, "<16:00"),E:E,0))

The result I get is 8:52 AM, which is outside of the time criteria I have set. It appears that the function seems to pull the very first instance that matches the MINIF function result, disregarding the time criteria altogether.

So also I want to keep in mind that I want to use a function that does not rely on hunting down individual cells as I'm hoping the end goal is to automate a data extraction process to export all the significant data points into a new excel sheet, and doing this over the course of several hundred to thousands of data sets.

Ideally I'd like to have a function that can reference the exact data point that was first extracted to pull other significant data from the same row and avoid possibly referencing the wrong data point just because it's a duplication elsewhere.

...

I have the following sheet:

Where for each Constructor there are two cars, so in Column C I use:

...

I am new to VBA macro and need some experts help on meeting the below requirement.

I got a workbook containing 2 sheets called 'Data' and 'Stats'.

'Data' contains the values as below

'Stats' contains the values as below

On click on the button, I would like to do the below

1. Get the values in column A in 'Stats' sheet
2. Find all the matching rows in 'Data' Sheet
3. Find the smallest start time and put that in 'Stats' sheet against the stage value
4. Find the biggest end time and that in 'Stats' sheet against the stage value

Final output would be like below

Note: I do not have the MINIFS or MAXIFS in my installation.

...