What Excel function is appropriate for the following scenario?-CodePudding

I have an issue I'm running into using Excel (I need to utilize Excel for this project). I need to create a table that generates the percentage of trips that begin during each hour during the day (assuming 24 hours in a day, of course). I've already established the times during which each trip started and the total number of trips that occurred, but I'm having trouble writing a equation that would accept the total number of trips and divide that by a time range (i.e. 3:00:00 AM to 3:59:00 AM) and output the percentage of trips that occurred. I've tried using =SUMIF and =SUMIFS, since they seem the most appropriate. Any help is greatly appreciated!

CodePudding user response：

I'm not clear what you're trying to do. It sounds like you want the percentage of total trips that fall within that hour range. If so, you need to use a combination of COUNTIF, COUNT (or COUNTA, which I prefer), and HOUR. I couldn't get HOUR to work within COUNTIF to calculate the hour range or the condition, so I don't see a way to avoid adding a hidden column (which I hate to do).

HOUR() returns the hour in military time/24 hour time. If no time is given (e.g. date only), it returns 0.

Assume your table has headers and data is only from rows 2 to 20 where Column A = Trip Start Time, Column B is (hidden) Hour, and Column C = % Trips

Cell B2: =HOUR($A2) Cell C2: =COUNTIF($B$2:$B$20,"=$B2")/COUNTA($B$2:$B$20)

and so on, copied down all of the rows. Note I use dollar signs on the column (and row for range) so that I can easily copy down functions without having them screw up

CodePudding user response：

If your table isn't what Excel considers to be an actual table then highlight your entire source table and click "Insert - Table".

Besides formatting, one of the huge benefits to this is that, when possible, you can store a one formula per COLUMN instead of one formula per cell. If column title of the start times is "Start Time" then you can create a new column, enter a formula of =HOUR([@[Start Time]]) in one of the cells, and suddenly the whole column is populated.

From there, you can make a Pivot Table to get what you need, right?