Looking for a simpler formula instead of using may nested "IF"s

**Ogbuehi** · 12-18-2009, 02:09 PM

I'm new to the forum but have tried to do alot of reading and testing on my own and I've gotten as far as I think I can go. Basically I receive a bunch of daily reports and I have a master sheet that I use to gather pertinent data. So on my master sheet I use the "MAX" command to find the highest temp from those sheets. I then use a bunch of nested "IF" commands to display the day that the max temp was recorded on. Is there a simpler formula or command to do this?

I have no prior programming experience or any experience with VBA (if that is even an option or needed). I've gotten this far through brute logic and just don't know enough about the many other commands to try anything else. The current "IF" formula is working just fine, I'm just hoping there's a simpler way to do it. I've attached an example file of what I am doing. I've also added the code that I'm trying to simplify. Thanks in advance.

Please Login or Register  to view this content.

**DonkeyOte** · 12-18-2009, 03:53 PM

Welcome to the Board.

My suggestion:

Please Login or Register  to view this content.

Group Day1:Day8 sheets and add the following:

Please Login or Register  to view this content.

Now Ungroup the sheets and you can replace your IF with:

Please Login or Register  to view this content.

the above assumes that you have only one MAX however... if you have multiple the above will return the lowest day value

**Lotus123** · 12-18-2009, 06:11 PM

I suggest this formula for cell A2 (same as suggested before):

Please Login or Register  to view this content.

Not sure if this meets the definition of "simpler", but I suggest this for cell B2:

Please Login or Register  to view this content.

The only problem with this function is the need to add in all of the days to the indirect function.

**Ogbuehi** · 12-18-2009, 11:31 PM

Originally Posted by DonkeyOte

Welcome to the Board.

My suggestion:

Please Login or Register  to view this content.

Group Day1:Day8 sheets and add the following:

Please Login or Register  to view this content.

Now Ungroup the sheets and you can replace your IF with:

Please Login or Register  to view this content.

the above assumes that you have only one MAX however... if you have multiple the above will return the lowest day value

Works great on my test sheet. I will try this out on the actual file itself. I wish I could get onto this site at work. Will this formula work if the data is not a number? I basically pull the max and min values from the sheet. Then I want to know the time and date of the max and min value. The time is no big deal as it's usually treated like a 4 digit number. But the date is usually ddmmmyy format. Thanks for all the help.

Just another question. I have some other things with the same spreadsheet but aren't related to the topic title. Should I pose the questions here or start up a separate thread for each question? Again thanks for all the assistance.

**DonkeyOte** · 12-19-2009, 03:55 AM

Originally Posted by Ogbuehi

Will this formula work if the data is not a number? I basically pull the max and min values from the sheet. Then I want to know the time and date of the max and min value. The time is no big deal as it's usually treated like a 4 digit number. But the date is usually ddmmmyy format.

In XL dates are Integers and Time is Decimal
(assuming they're entered as genuine date & time values of course!)

For ex. on 1900 Date System 6am on 19th Dec 2009 equates to: 40166.25

dateserial being 40166 and time being .25 (6 am = 1/4 of a day)

On that basis you should not have a problem.

Originally Posted by Ogbuehi

I have some other things with the same spreadsheet but aren't related to the topic title. Should I pose the questions here or start up a separate thread for each question?

Threads should specific to a given question... so new thread(s) please.

On a final note - Lotus123's approach is realistically the only non-VBA alternative you have if you prefer to avoid use of "helpers" on the source sheets however as he/she concedes themselves the use of INDIRECT makes the approach volatile and the use of SUMPRODUCT makes it relatively "expensive"... I'd normally say, based primarily on the knowledge of others more knowledgeable than myself, if you can use helpers you should do as though less elegant it will be more efficient long term.

(also FWIW should there be multiple instances of the MAX the SUMPRODUCT in it's present form would aggregate the respective B2 values which might be prove to be an issue for you - not clear at this point).

**Ogbuehi** · 12-19-2009, 08:26 AM

Well how about straight text instead of something number based. The time issue seems to be throwing me off as the time is inputted one way, and the formula no longer recognizes the numbers and displays a 0.

**Ogbuehi** · 12-19-2009, 08:56 AM

And one more problem. Basically I copy a week's worth of sheets into the workbook. When I start a new report and paste a new worksheet over the old one, will the formulas stay there? Or is there a way to lock the formulas into that part of the sheet so I don't have to reinput them every single time?

**Palmetto** · 12-19-2009, 08:57 AM

I understand you are getting daily reports and have a master sheet, but is there some reason why you cannot consolidate all of these daily reports into a single data sheet (using an additional column, if necessary, to ID the data source)? All of your data, at least as suggested by your sample workbook, is of the same type and could be stored in a single sheet.

It would make your task much easier and provide much more flexibility and simplicity in extracting data, etc.

**Ogbuehi** · 12-19-2009, 11:15 AM

The sample test sheet I provided in this forum was just o get the formula part down and simplicity's sake. The actual reports I'm getting contain alot more information than just temp, time, date (multiple generators, status, fuel usage, power factors etc.). If I put them all in one sheet, that sheet for the week would be so huge it would be very difficult to try and navigate. Trust me, if I could just combine that date into one sheet, it would make life alot easier for me. Thanks for the suggestion.