If, Min, Max Formulas Help

[silentlenny07]

Excel Product.png

I'm trying to auto populate another sheet with the earliest start time and latest stop time in 24-hour time for each round based on the cells in the attachment. I was considering using the min, max, and if formulas to produce this, but honestly have no idea where to start. For the purposes of this:

Sheet: ABC is where the times are listed in table format:
Round is column D
Start Time is G
Stop Time is H
*These cells are populated by a schedule that is copied and pasted into the table, so that's why the gap is there

Sheet: DEF Cell: C5:C17 is where I need the minimum or earliest start time posted
Sheet: DEF Cell: D5:D17 is where I need the maximum or latest stop time posted

Any assistance with this would greatly be appreciated, thank you.

[FDibbins]

Hi, welcome to the forum

You could try this ARRAY formula...
=max(if($D$1:$D$10=1,$G$1:$G$10))
=min(if(($D$1:$D$10=1)*($G$1:$G$10>0),$G$1:$G$10))

[silentlenny07]

Ford,

Thanks for the assistance. Do the times need to be in any certain format? All I got was 0 for both formulas. I did a custom format 00\:00 for 24-hour time after I got 0s and it updated. It gives me the min or max for the whole selection instead of based on the round.

[FDibbins]

The times need to be real times, not test. Do a quick test with =isnumber(cell-ref), FALSE indicates text and we will need to convert them

I am out of time right now (3:02 am and bed is calling, if no other member can help, I will take a look again tomorrow)

[FDibbins]

hmm looking at your pic, yes they are text, not real numbers, let me see what I can throw together quickly

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[silentlenny07]

Thanks for your help Ford, I really appreciate it.

[FDibbins]

OK try this.

1st, make 2 helper columns to convert the Start and End times to real times (this will be simpler than a fancy formula to do it all in 1)
Then put this in the 1st helper and copy down...
=--(LEFT(g1,2)&":"&RIGHT(g1,2))
repeat that for H

Then use my 2 formulas but reference the helper columns instead

I noticed my MAX formula was wrong, it should have been referencing H, not G
=max(if($D$1:$D$10=1,$H$1:$H$10))

[silentlenny07]

Excel Product 2.png

I2 tested H2 number is real. It's in a custom format 00\:00

The formula that Ford suggested, pulled the whole earliest time and latest time for all rounds, not just each separate round.

[AliGW]

Attach the workbook, NOT a picture of it.

[silentlenny07]

I think I have attached the document. It's called Sample

[JohnTopley]

in N2

=MIN(IF(($D$2:$D$13=LEFT(M2,1)+0)*($G$2:$G$13>0),$G$2:$G$13))

in O2

=MAX(IF($D$2:$D$13=LEFT(M2,1)+0,$H$2:$H$13))

Both .....

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[silentlenny07]

The formula worked amazingly. To adjust for each round, I change M2,1 to M3,2 and M4,3 correct?

[JohnTopley]

You can just copy/drag the formula down the columns.

The LEFT(M2,1)+0 simple takes the first letter of "1st Round" etc to get the Round numeric value ie. 1, 2 or 3 so as you copy down you will get 2 and 3.

[FDibbins]

Happy to help and thanks for the feedback

[silentlenny07]

I got the formula to work and it does well, now my issue is when I copy and paste the schedule, the array formula doesn't update. If I manually type in the times it updates. Is there a way to get the formula to update without having to hand-jam the cells individually? Can I set up a Macro to do that? Thanks in advance for the help

[silentlenny07]

The formula works great, but it doesn't update when I copy the schedule to the appropriate cells. If I hand-jam the cells it updates. Is there any way to keep from hand-jamming the cells? Maybe a macro? Thanks in advance for the help.

**Sorry for the duplicate posts, I'm still learning how forums work**

[JohnTopley]

...but it doesn't update when I copy the schedule to the appropriate cells. If I hand-jam the cells it updates

....meaning?

[Myki]

Hi,

Can someone assist with a formula/function to allocate revenue across yearly columns. e.g;

Start Date: 28/12/2017
End Date: 05/09/2022
Expected Revenue: 150,000
Revenues to be allocated across years in Col. - 31/12/2018 - 31/12/2019 - 31/12/2020 - 31/12/2021 & 31/12/20122

sample excel sheet most welcome.

[AliGW]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Start your own thread - do not post again to this thread.

[silentlenny07]

....meaning?

When I copy the schedule into the affected cells where the array formula references, the cells remain with 00:00. If I type in the same information that is already in the cell (i.e. 10:30) then the array formula cell updates and shows 10:30. I have formatted the cells to the same custom type for all the reference cells and array formula cell. {=MAX(IF($E$4:$E$25=1,$I$4:$I$25))}

I have attached Sample 2, but I can't show the full schedule. The cells on the right are what I copy and paste.

[JohnTopley]

Data needs to TIME format: yours is currently treated as TEXT.

[silentlenny07]

So I tried formatting the cells to time and regardless of which time I select, it converts the cell from "10:30" to "10/26/1902 12:00:00 AM". I have selected "more number formats" and selected the 10:30 format and it still converts it to full year and time format. It still does not update for me. I'm using MS Excel 2013 from the full office suite. I even tried pasting the copied schedule into unused cells to convert those to time and then copy again and paste into the cells with no different results.

[silentlenny07]

For clarification, I am copying the schedule from a PDF document that I recognize text in.

[JohnTopley]

As the data is text you can convert to time using the following

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

[silentlenny07]

As the data is text you can convert to time using the following

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

How do you make this reference another cell on a different sheet?

[JohnTopley]

assuming (PDF) data is on Sheet2

=TIME(LEFT(Sheet2!A1,2),RIGHT(Sheet2!A1,2),0)

[silentlenny07]

So I attached my product to show you what I'm trying to do.

In tab Schedule, I copy the schedule from a PDF file that I have to recognize text since it is a scanned image. I paste the schedule into the cells respectively and then use the array formula to pull the earliest and latest round from each schedule to the cells on the right. Forgot to label them, but you can see by the formula max/min which is earliest and latest. Once it calculates in those cells, I pull the info to tab Events Log in the respective location. The times don't show as time and it doesn't calculate in the array formula unless I manually type the entry and hit enter. If I could get the array formulas to work from the events log tab and pull the info from the schedule tab, I wouldn't need those cells to the right of the schedule.

This is the last thing to finalize before my product gets tested and put into service. Thanks for the help in advance.

[JohnTopley]

... and where would the times to be analysed appear in the Events log?

Also the format of the PDF data is inconsistent (1035 vs 10:35)

I created a Word Document which I converted to PDF. I then copied the data form the PDF file to Excel and it was formatted as time. ????

[silentlenny07]

... and where would the times to be analysed appear in the Events log?

Also the format of the PDF data is inconsistent (1035 vs 10:35)

I created a Word Document which I converted to PDF. I then copied the data form the PDF file to Excel and it was formatted as time. ????

I'm not sure I understand the question. The Schedule tab only applies to the blue columns in the Events Log. There are other outfits that use the other colors, I'm just trying to keep the product simple, I should be able to reproduce the solution for the other schedules. I can try converting the document to Word and then import the information.

Results - Still trying a few adjustments, but first shot didn't work.

[JeteMc]

If I understand correctly then perhaps this will be of some help.
Three helper columns, which may be moved and/or hidden for aesthetic purposes, have been added to the the schedule sheet beginning in row 2:
For column Q the formula, which is based on John's, reads:

Please Login or Register  to view this content.

For column R the formula, again based on John's, reads:

Please Login or Register  to view this content.

For column S the formula reads:

Please Login or Register  to view this content.

The array entered formulas* that populate the cells in column O are similar to:

Please Login or Register  to view this content.

The cells on the Events Log sheet then reference the appropriate cells on the Schedule sheet.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[silentlenny07]

=AGGREGATE(15,6,(0+$H$116:$H$140)/($E$116:$E$140=1),1)
=AGGREGATE(14,6,(0+$I$116:$I$140)/($E$116:$E$140=1),1)

Thank you to all who helped me along the way, the formulas above solved my problem. I was away for the holidays.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AliGW]

For future reference:

Rule 08: Cross-posting Without Links

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).