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

#### exclnewb

##### New Member
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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

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

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.

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.

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.

Replies
3
Views
301
Replies
5
Views
624
Replies
6
Views
850
Replies
0
Views
290
Replies
3
Views
697

1,211,697
Messages
6,103,360
Members
447,861
Latest member
LllopezXC

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back