# How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then REPEAT

1. ## How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then REPEAT

I need some assistance in writing a formula.

Input Condition:
• Cells B3 downwards contains 2015.07.17 (date weekdays only, Monday-Friday, when there is a holiday on weekdays, it will not be included)
• Cells C3 downwards contains 0:00:00 (time with a space 5 minutes until the end of the day, but not always to 23:55:00, could be less, then back again to 0:00:00 to the next day)
• Cells D3 and E3 downwards contains random value (any positive value)

Output (SOLVED):
• Cells G3 downwards contains E3-\$D\$3 until the end of the day. If the next day is in B6 and 0:00:00 is in C6, then the formula will be E6-\$D\$6, and so on. Lets say the last input data is row 21, therefore the formula for cells G3 downwards:

``Please Login or Register  to view this content.``
This formula has been completed and in accordance with what I want.

Output Problems:
1. In cells H3 downwards, I want to make this formula:
If C3<=3;55;00 and G3<=-0.00025, then "YES". Other than that, it will be "NO".
The formula for this cells downwards will be:

``Please Login or Register  to view this content.``
The problem is, I want it to be more specific. Lets say the results for H3 is "YES" and H4 is "YES", both on the same day. I want to make H4 result become "NO" because H3 result already "YES".
In other words: If first "YES" appears, then the next "YES" will be "NO" until the end of the day and repeat it every day.
What formula should I add into this? or should I just add a new column?

2. In cells I3 downwards, I want to make this formula:
For cell I3, If H3 is "YES", then find the maximum value from G3 downwards until the end of the day. Other than that is "NO". Lets say the end of the day is in cell B5 or C5, so formula for this cells downwards in 1 day will be:

``Please Login or Register  to view this content.``
The problem is, If I use this formula, I must change the MAX formula for every new day because there is a fixed sign (\$) for the MAX value. I want the formula for 1 day only and repeat it every day.
What formula should I add into this? or should I just add a new column?

This is a sample of my workbook:
Attachment 456597

I need the formula so I can copy down the formula without change the formula for every new day (there is more than 300.000 data).

Thank you.

-Jaka-

2. ## Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

3. ## Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

Hi, thank you for your reply and for the information. Sorry for the inconvenience.

I just uploaded my workbook sample. I hope my explanation is understandable and there is someone who can help me.

Originally Posted by FDibbins
Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

4. ## Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

Bump no response

5. ## Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

Hi guys, I just uploaded my NEW workbook sample and add more explanation in this post, I hope someone can help me with this problems. Thank you.

6. ## Re: How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then RE

Bump no response

7. ## Re: How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then RE

Bump no response

There are currently 1 users browsing this thread. (0 members and 1 guests)