newb question: need to match time values by minute, and add next column values

exclnewb

New Member
Joined
Jul 28, 2010
Messages
3
I have a worksheet with time values in the first column (values are hh:mm:ss am/pm) and a text value in the next column. Text is either red, green or yellow. Already I've made a column that has a value of 1 if the its red, and another column that gives a value of -1 if its green.
The purpose is to find out how many reds I have of a minute (minus greens becuase green means its not red anymore. )
So what I did was a another column with this value: =J7+G8+H8
J is the column with the total for red values, G has a 1 if red, H has a -1 if green.
K, follow that? So now I'm looking for a sum per minute, or the highest value in J for the times that match to the minute.

K, total newb to this, dunno if I need a macro or not, or what.
Please help, and if you could in a way that will teach so Im not just getting someone to do my work:)
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the Board!!!
Do you mean the text is actually "Red","Green",or "Yellow", or is that the color of the Font?

lenze
 

exclnewb

New Member
Joined
Jul 28, 2010
Messages
3
Hi and welcome to the Board!!!
Do you mean the text is actually "Red","Green",or "Yellow", or is that the color of the Font?

lenze
Thanks for the reply.
The text is actually "Red", "Green" or "Yellow"
This is an export of a hobbit report (open source network monitoring tool)
I can do an auto filter for the yellow and delete it. After which I could have on column =IF(C1="red",1,-1) then to set a value of 1 if red, and -1 if not red (which will be green cause thats the only option), then just =sum that result.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
Make a helper column that is the time without the seconds
=TIME(HOUR(A2),MINUTE(A2),0)

This will strip the seconds from your time value in cell A2.

Put this in row 2 of an empty column (For this example; column K), and copy it down column K (K1 has the column header)

Now you say you want the max J value for each minute

Select all your Data in columns J and K and make a Pivot table where K (Time in minutes) is in the Row area and Max of J is in the Data area.
 

exclnewb

New Member
Joined
Jul 28, 2010
Messages
3
Make a helper column that is the time without the seconds
=TIME(HOUR(A2),MINUTE(A2),0)

This will strip the seconds from your time value in cell A2.

Put this in row 2 of an empty column (For this example; column K), and copy it down column K (K1 has the column header)

Now you say you want the max J value for each minute

Select all your Data in columns J and K and make a Pivot table where K (Time in minutes) is in the Row area and Max of J is in the Data area.

(y)
Thanks much for the help.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,662
Messages
5,512,706
Members
408,910
Latest member
fchri31

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top