Date Range and calculation

I was wondering if an array formula was best for this or hopefully there is a shorter way?

I have a sheet (Base) which has the information for interrogation and a summary sheet (Monthly). I wish the monthly sheet to extract data from the base sheet according to the date range entered. There is two cells, with a start and end date. The rest of the fields then calculate depending on that. One of the fields needs to return an average net commission %. So, depending on the date range entered it needs to find (filter) within that date range in the Base sheet on Col P (which is list of dates of exchange), then get the average of Col Y (which is a fee amount in £) and divide it by the average of Col S (which is sale price in £).

sorry for being long winded!

any suggestions gratefully received.

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

knightly, many thanks for your response. the first approach reducing value to monthly one is useful and I will incorporate.

the second approach is great also and I will be tweaking it for use with the other calcuations that I have to make. thank you again for such a clear answer.

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly

Green,

Will the date ranges vary, or are you just trying to get a monthly
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column to
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range and
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking an
array formula would be best for that. Assuming that your criteria are
in A1 (first date) and A2 (subsequent date), and replacing GT and LT
with the appropriate symbols below, change the ranges in base as
necessary and enter as an array formula:

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base!A2,1,0),0)))

Knightly

vgreen Wrote:
> I was wondering if an array formula was best for this or hopefully
> there
> is a shorter way?
>
> I have a sheet (Base) which has the information for interrogation and
> a
> summary sheet (Monthly). I wish the monthly sheet to extract data
> from
> the base sheet according to the date range entered. There is two
> cells,
> with a start and end date. The rest of the fields then calculate
> depending on that. One of the fields needs to return an average net
> commission %. So, depending on the date range entered it needs to
> find
> (filter) within that date range in the Base sheet on Col P (which is
> list of dates of exchange), then get the average of Col Y (which is a
> fee amount in £) and divide it by the average of Col S (which is sale
> price in £).
>
> sorry for being long winded!
>
> any suggestions gratefully received.
>
>
> --
> vgreen
> ------------------------------------------------------------------------
> vgreen's Profile:
> http://www.excelforum.com/member.php...o&userid=26510
> View this thread:
> http://www.excelforum.com/showthread...hreadid=397768


--
sirknightly