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

[exclnewb]

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

 

[lenze]

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]

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]

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]

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.

Thanks much for the help.