Thanks Max, I've never played with INDIRECT before. Definitely much less
baggage.

Roy

--
(delete .nospam)




"Roy Wagner" wrote:

Personally, I would prefer a vba solution, but here is a relatively simple
way to do it with worksheet functions. This method will work for as many
columns as you have data. Lets assume that the longest range is from rows 1
to 100. You can make it as long as you need, but it will be the same for all
columns, and you will simply offset where you put the formulas so that they
are below your data.

Open a blank sheet. Copy and paste some of your existing time data in A1:F100.

In my test, I have time data in columns A-F, rows 1-5 to keep it simple for
me, but it will work through row 100 as is.

Paste this formula into cell A101:

=IF(COLUMN(A102)<27,CHAR(COLUMN(A102)+64),CHAR(INT (COLUMN(A102)/26)+64)&CHAR(MOD(COLUMN(A102),26)+64))

That makes the cell display its column letter.

Copy and paste your existing formulas (below) into cells A102 and A103, .i.e,
=COUNTIF(A1:A100,"<0:05:00")/COUNT(A1:A100)
=COUNTIF(A1:A100,"1:00:00")/COUNT(A1:A100)

Select cells A101, A102 and A103, hold down the shift key and move the
cursor to the right until you have highligted cells A101:F103. Right click
and do an EDIT/FILL/RIGHT. This saves you from typing the formulas over and
over for each column.

You should now have a set of formulas in each column. This may be all you
are looking for. If so, you didn't need the column ID's in row 101. In my
example, you have just created a horizontal lookup table.

In cell G1, type "Select Column" and widen the column so it fits.

In cell G3, paste this formula:

=IF(ISERROR(HLOOKUP(UPPER(H1),A101:F103,2,FALSE)), "No times are entered in
this column.",TEXT(HLOOKUP(UPPER(H1),A101:F103,2,FALSE) ,"0.0%")&" of the
times in Column "&UPPER(H1)&" are less than 5 minutes.")

In cell G4, paste this formula:

=IF(ISERROR(HLOOKUP(UPPER(H1),A101:F103,3,FALSE)), "",TEXT(HLOOKUP(UPPER(H1),A101:F103,3,FALSE),"0.0% ")&"
of the times in Column "&UPPER(H1)&" are more than 1 hour.")

By changing the column letter in cell H1, the HLOOKUP displays the time
percentages for that column. Is that what you are looking for? Of course you
can move things around to suit your sheet design. If the column is empty, the
error is suppressed. The "UPPER" allows you to use either case in H1.


Have fun.
Roy
--
(delete .nospam)




"dofnup" wrote:

OK, here's my situation:

I have many columns of times. I need to find out what percentage of that
column is below 5 mins, which percentage is below 1 hour, which percentage is
above one hour, etc, etc.

I am using the following formula structu

=COUNTIF(A1:A100,"<0:05:00")/COUNT(A1:A100)
=COUNTIF(A1:A100,"1:00:00")/COUNT(A1:A100)
etc etc

Is is at all possible to create a custom function so that I don't have to
type all that stuff for every different range? Since some columns are
hundreds, other's are a small number, it's pretty random, so a custom
function would be ideal, with the range as the argument, however, i would
need to reference COUNTIF and COUNT, and i don't know how to do that or if it
is even at all possible.

Any help on this would be greatly appreciated!1 Thanks in advance ...