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:
This formula has been completed and in accordance with what I want.![]()
Please Login or Register to view this content.
Output Problems:
- 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:
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".![]()
Please Login or Register to view this content.
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?
- 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:
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.![]()
Please Login or Register to view this content.
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-
Bookmarks