Using Max & keeping other data
I have a spreadsheet with three columns.
A: A date for every hour for the month (ie. 1/1/06 24 times) B: The hour C: A dollar amount I need to form a result that provides the max dollar amount per day, and I need to show the hour that it occurs on. I have no problem getting the max amount per day, but how do I also display the hour? Thank you, |
Using Max & keeping other data
I'm sorry, I tried for simplicity sake to make my example too generic. I was
able to solve the problem I submitted using 'Consolidate'. I think it worked because the hour column is numeric. In reality my middle column is text and consolidate is not picking it up. How can I display a miscellanious text field when doing a max amount by day? "Cathy" wrote: I have a spreadsheet with three columns. A: A date for every hour for the month (ie. 1/1/06 24 times) B: The hour C: A dollar amount I need to form a result that provides the max dollar amount per day, and I need to show the hour that it occurs on. I have no problem getting the max amount per day, but how do I also display the hour? Thank you, |
Using Max & keeping other data
Cathy,
Would a Macro do? If so then with Column D formatted as Currency and Column E formatted as Time try: Option Explicit Sub MaxPerDay() Dim R As Long Dim x As Long Dim D As Double Dim MaxVal As Double Dim MaxHour As String Application.ScreenUpdating = False D = Cells(1, 1).Value2 R = Cells(Rows.Count, 1).End(xlUp).Row MaxVal = Cells(1, 3).Value For x = 1 To R If Cells(x, 1).Value2 = D Then If Cells(x, 3).Value MaxVal Then MaxVal = Cells(x, 3).Value MaxHour = Cells(x, 2).Value End If End If If Cells(x, 1).Value2 < D Then Cells(x - 1, 4).Value = MaxVal Cells(x - 1, 5).Value2 = MaxHour D = Cells(x, 1).Value2 MaxVal = Cells(x, 3).Value MaxHour = Cells(x, 2).Value D = Cells(x, 1).Value2 End If Next x Cells(R, 4).Value = MaxVal Cells(R, 5).Value2 = MaxHour Application.ScreenUpdating = True End Sub -- HTH Sandy with @tiscali.co.uk "Cathy" wrote in message ... I'm sorry, I tried for simplicity sake to make my example too generic. I was able to solve the problem I submitted using 'Consolidate'. I think it worked because the hour column is numeric. In reality my middle column is text and consolidate is not picking it up. How can I display a miscellanious text field when doing a max amount by day? "Cathy" wrote: I have a spreadsheet with three columns. A: A date for every hour for the month (ie. 1/1/06 24 times) B: The hour C: A dollar amount I need to form a result that provides the max dollar amount per day, and I need to show the hour that it occurs on. I have no problem getting the max amount per day, but how do I also display the hour? Thank you, |
All times are GMT +1. The time now is 01:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com