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:)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,450
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,750
Messages
5,833,478
Members
430,211
Latest member
Vandermeer

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top