Attached is an image of a partial sheet I use. As you can see times are entered and a result is given.
The MAX time works (formula =MAX(u7:u76)
The MIN time does not (formula =MIN(u7:u76)
Can anyone shed any light as to why?? or even a replacement formula that returns the correct data.
Last edited by ukphoenix; 10-22-2013 at 03:06 PM.
Cannot evaluate your spreadsheet from a picture.
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Agree with Alan, its pretty much impossible to see what you are working with there.
However, keep in mind that if you cells with a formul athat returns a zero, that will be the lowest value in that range
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
as requested here is a sample page of the workbook. As you can see i have filled data in rows 73 & 74 giving a max and min time.
In cells U78 Max works fine but in cell U79 returns 00 as an answer.
Is it picking the blank cells in column U up so therefore as suggested by FDibbins the time remains 00.
If this is the case then how do I get the MIN to ignore the blank cells
hi ukphoenix. try:
=SMALL(U7:U76,COUNTIF(U7:U76,0)+1)
by the way, it's quite useful for us if you put in your MS Off Ver in your profile. it helps us decide if you can use a newer & better formula. go to Settings -> Edit Profile
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Ok updated profile
ok formula works and returns a minimum when data is entered. But to tidy the sheet up completely if all are blank in column U it returns the data as #NUM!, can something be added to remove this and leave answer as 00:00:00 until it has a figure to display ??
Hi ukphoenix,
Try
=IFERROR(SMALL(U7:U76,COUNTIF(U7:U76,0)+1),0)
Ok well that seems to work, thanx all for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks