M
masterkeys
Probably easy for you, but I'm not that good with sumproduct.
I have 3 Worksheets simplified below (some columns are not next to eac
other, but spaced out, (* and % = colums next to each other)))
Rates,Hours and summary
I have 2 special periods : -1 and 13 :- period -1 is for before the F
starts, and 13 is for after, so I can't search just on the month.
e.g. below, headings are the same
Rates
Period* | Month* | Start Rate | Changed Rate% | wef%
-1 | B4 FY | 10.00 |
1 | April | 10.00 | 12.00 | 15/04/07
2 | May | 12.00 | |
3 | June | 15.00 | 15.00 | 01/06/07
4 | July | 15.00 | |
...
12 | March | 23.00 | 25.00 | 28/03/08
13 | after FY | 25.00
etc.
The month starting rate, is autofilled from above, or changed rat
depending on if there was a change (if date of change = 1st, the
changed also)
Hours consists of:
Period*| DAte of Work* | Hours doing job
-1| 01/04/2007 | 10.00
1| 13/04/2007 | 12.00
8| 16/11/2007 | 5.00
13| 11/04/2008 | 20.00
now on the summary I have:
Period* | Month* |<OTHER COLUMS>| Total Hours% | Cost%
1 | April | | ^^ |
^^{=SUM(IF(INDIRECT($D$1)=$D7,INDIRECT(O$1),0))}
taken out sheet references below:
Hidden at the top of the summary page, are formulae, to work out th
last
filled row in column A. :- =MATCH(-99999,A:A,-1)
D1 contains "A1:A99" where 99 = last row
O1 contains the range, for the total hours column
now what I would like, is a formulae, that calculates the total cos
for each period, taking into account any rate change that has happene
(i.e. hours*rate on a date). for Period -1, the starting rate at 1 i
assumed, likewise for period 13, the ending rate for 12 is assumed.
I guess sumproduct would be the best here, but I am unsure on how t
calculate it, and whether it would be entered as an array formula, o
just a normal one. The wef date is the 1st day the new cost starts.
---edit---
if it's easier, I can add an extra column at the end and do it on
line by line basis.
---end edit---
Many Thanks in advanc
I have 3 Worksheets simplified below (some columns are not next to eac
other, but spaced out, (* and % = colums next to each other)))
Rates,Hours and summary
I have 2 special periods : -1 and 13 :- period -1 is for before the F
starts, and 13 is for after, so I can't search just on the month.
e.g. below, headings are the same
Rates
Period* | Month* | Start Rate | Changed Rate% | wef%
-1 | B4 FY | 10.00 |
1 | April | 10.00 | 12.00 | 15/04/07
2 | May | 12.00 | |
3 | June | 15.00 | 15.00 | 01/06/07
4 | July | 15.00 | |
...
12 | March | 23.00 | 25.00 | 28/03/08
13 | after FY | 25.00
etc.
The month starting rate, is autofilled from above, or changed rat
depending on if there was a change (if date of change = 1st, the
changed also)
Hours consists of:
Period*| DAte of Work* | Hours doing job
-1| 01/04/2007 | 10.00
1| 13/04/2007 | 12.00
8| 16/11/2007 | 5.00
13| 11/04/2008 | 20.00
now on the summary I have:
Period* | Month* |<OTHER COLUMS>| Total Hours% | Cost%
1 | April | | ^^ |
^^{=SUM(IF(INDIRECT($D$1)=$D7,INDIRECT(O$1),0))}
taken out sheet references below:
Hidden at the top of the summary page, are formulae, to work out th
last
filled row in column A. :- =MATCH(-99999,A:A,-1)
D1 contains "A1:A99" where 99 = last row
O1 contains the range, for the total hours column
now what I would like, is a formulae, that calculates the total cos
for each period, taking into account any rate change that has happene
(i.e. hours*rate on a date). for Period -1, the starting rate at 1 i
assumed, likewise for period 13, the ending rate for 12 is assumed.
I guess sumproduct would be the best here, but I am unsure on how t
calculate it, and whether it would be entered as an array formula, o
just a normal one. The wef date is the 1st day the new cost starts.
---edit---
if it's easier, I can add an extra column at the end and do it on
line by line basis.
---end edit---
Many Thanks in advanc