View Full Version : Calling all Excel gurus!
EESDL
20th Oct 2005, 15:26
I run a spreadsheet, part of which I put flying hours, all billed at different rates.
So for one day you'd have maybe 5 different 'rate' columns with the total flying for the day at the end of the row.
The flight can relate to 8 different jobs, ie charter, personal, business1, business2 etc.
So first column of spreadsheet is a column where I identify which business to invoice with numbers 1- 8.
I need to show how many hours I've done for each category 1- 8.
What I need to work out is how I can sum all the hours on the right, that have a particular reference on the left to give me 8 totals, each may have a mix of the 5 different charge rates.
Codes on left will not be in order so VLookup doesn't work!
Code Date Rate1 Rate2 Rate3 etc
3 0.8 0.4 0.5
1 1.7 1.8 1.1
1 0.2
4 1.7 1.6
Thank you in advance
Genghis the Engineer
20th Oct 2005, 17:18
Somewhere off to the right where you aren't looking use eight columns.
In each, use terms along the lines of IF(F3=1,D3,0) [If F3=1, value from D3, else 0]
Then put a SUM term at the top of each column.
Then somewhere more convenient just X-ref to that sum cell.
G
ExGrunt
21st Oct 2005, 09:56
EESDL
I think this has a fairly straight forward solution. But, I am not clear on how you want to present the results.
As I read your outline:
Each row has one job with a 'job code'. That job has a total number of hours split between five 'cost codes'.
Each date can contain a number of job rows, which is indicated by the date column in the job row.
The row entries are in random order.
What I need to know is how you what to show the output?
Do you want a table? ie:
Columns with cost codes.
Rows with job codes.
summarised data in the table.
Or do you need something else?
EG
EESDL
21st Oct 2005, 14:14
Thanks
Genghis, I was on the verge of doing the same when I thought that there must be an easier solution amongst all the 'functions'
Ex Grunt
You got it in one.
Require the result as a simple list (on my FY annual summary page) with a running total ie
Charter £total amount of revenue allocated code 1
Business 1 £blah amount of revenue allocated code 2
Business 2 £blah etc
Then I can provide adequate briefs/pie-charts etc
Thank you in advance
Genghis the Engineer
21st Oct 2005, 14:56
There are functions which will do this sort of thing, and I've used them on occasion.
But, the advantage (in my opinion) of doing it the way that I suggested is that the data analysis is more visible so that if there are any errors in your spreadsheet it's much easier to track down and correct.
G
ExGrunt
31st Oct 2005, 12:02
Hello EESDL,
I have been away and could not respond sooner, I suggest:
Assuming:
Cells B2:H2 contain titles: Code, Date, Rate1, Rate2, Rate3, Rate4, Rate5.
Cells B3:B25 contain jobcodes
Cells C3:C25 contain Dates
Cells D3:D25 contain Hours Rate1
Cells E3:E25 contain Hours Rate2
Cells F3:F25 contain Hours Rate3
Cells G3:G25 contain Hours Rate4
Cells H3:H25 contain Hours Rate5
In Cells K2:P2 enter: Code, Rate1, Rate2, Rate3, Rate4, Rate5.
In Cells K3:K10 enter the codes: Charter, Personal, Business1, Business2, Business3, Business4, Business5, Business6.
Then in Cells L3:P10 enter the formula =SUMPRODUCT(($B$3:$B$25=$K3)*($D$2:$H$2=L$2)*$D$3:$H$25)
Obviously when you go past row 25 you have to extend the formula ranges: $B$3:$B$25 and $D$3:$D$25.
I would echo Genghis' point that for a business application there needs to be some form of error checking calculation.
Hope this helps
EG
thanks, will give it a whirl
ExGrunt
3rd Nov 2005, 11:46
On reflection I thought some further clarification is needed:
The calculation is based on decimal time - ie 1 hour 6 minutes is entered as 1.1
When I say:
Then in Cells L3:P10 enter the formula =SUMPRODUCT(($B$3:$B$25=$K3)*($D$2:$H$2=L$2)*$D$3:
$H$25)
I actually mean:
Then in Cell L3 enter the formula =SUMPRODUCT(($B$3:$B$25=$K3)*($D$2:$H$2=L$2)*$D$3:
$H$25)
And then copy it into Cells L3:P10
EG