Need to reference existing functions in a custom function: possibl

Perhaps a possible alternative to simplify ?

One play would be to concatenate the range string from an input for the
column of interest, then use INDIRECT in the COUNTIF ..

Let's reserve cell E1 for input of the column of interest
Enter in E1: A

Put in say, F1: =E1&"1:"&E1&"100"

Then you could put in say, G1:
=COUNTIF(INDIRECT(F1),"<0:05:00")/COUNT(INDIRECT(F1))

which would return the equivalent of:
=COUNTIF(A1:A100,"<0:05:00")/COUNT(A1:A100)

We could also extend the concatenation of the string in F1 to include
variations in the row references as well, besides the column reference, for
example:

If we were to reserve cells E1:E3 for inputs of column, start row, end row,
e.g. inputs made:

In E1: A
In E2: 1
In E3: 100

Then we could just amend the formula in F1 to: =E1&E2&":"&E1&E3
and use the same formula in G1

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----
"dofnup" <[email protected]> wrote in message
news:[email protected]...
> 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 structure:
>
> =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 ...

> Put in say, F1: =E1&"1:"&E1&"100"
> ... amend the formula in F1 to: =E1&E2&":"&E1&E3

Just a clarification:
In both instances, F1 is used to produce the concatenated range string
Then the INDIRECT is pointed to read what's in F1 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----

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 structure:
>
> =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 ...

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 structure:
> >
> > =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 ...

"Roy Wagner" wrote:
> Thanks Max, I've never played with INDIRECT before.
> Definitely much less baggage.

You're welcome !
Sometimes, it's a more direct route to use INDIRECT <g>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----

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

=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 ...

Perhaps a possible alternative to simplify ?

One play would be to concatenate the range string from an input for the
column of interest, then use INDIRECT in the COUNTIF ..

Let's reserve cell E1 for input of the column of interest
Enter in E1: A

Put in say, F1: =E1&"1:"&E1&"100"

Then you could put in say, G1:
=COUNTIF(INDIRECT(F1),"<0:05:00")/COUNT(INDIRECT(F1))

which would return the equivalent of:
=COUNTIF(A1:A100,"<0:05:00")/COUNT(A1:A100)

We could also extend the concatenation of the string in F1 to include
variations in the row references as well, besides the column reference, for
example:

If we were to reserve cells E1:E3 for inputs of column, start row, end row,
e.g. inputs made:

In E1: A
In E2: 1
In E3: 100

Then we could just amend the formula in F1 to: =E1&E2&":"&E1&E3
and use the same formula in G1

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----
"dofnup" <[email protected]> wrote in message
news:[email protected]...
> 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 structure:
>
> =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 ...

> Put in say, F1: =E1&"1:"&E1&"100"
> ... amend the formula in F1 to: =E1&E2&":"&E1&E3

Just a clarification:
In both instances, F1 is used to produce the concatenated range string
Then the INDIRECT is pointed to read what's in F1 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----

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 structure:
>
> =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 ...

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 structure:
> >
> > =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 ...

"Roy Wagner" wrote:
> Thanks Max, I've never played with INDIRECT before.
> Definitely much less baggage.

You're welcome !
Sometimes, it's a more direct route to use INDIRECT <g>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----