Originally posted by RSoles
View Post
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Subtotal without subtotalling?
Collapse
X
-
-
A Employee ID B Month C Amount D Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)
1235 1 25.00
1235 1 15.00 40.00
1235 2 25.00 25.00
1235 3 125.00 125.00
2569 2 25.00
2569 2 15.00 40.00
2569 3 300.00
2870 3 25.00 325.00
What I need is a calculation in column E which will sum Column D at each change of reference in column A
I spent ages lining up all those numbers which now look fine whilst I am editing but all group together when I postComment
-
Originally posted by LisaContractorUmbrella View PostA Employee ID B Month C Amount D Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)
1235 1 25.00
1235 1 15.00 40.00
1235 2 25.00 25.00
1235 3 125.00 125.00
2569 2 25.00
2569 2 15.00 40.00
2569 3 300.00
2870 3 25.00 325.00
What I need is a calculation in column E which will sum Column D at each change of reference in column A
I spent ages lining up all those numbers which now look fine whilst I am editing but all group together when I postComment
-
Originally posted by LisaContractorUmbrella View PostUnfortunately the formulas need to remain
See it as a workaround not a solution.
It could be made into a macro too.McCoy: "Medical men are trained in logic."
Spock: "Trained? Judging from you, I would have guessed it was trial and error."Comment
-
Originally posted by woohoo View PostI must be reading this wrong, but why not create a pivot table and group on Column A and subtotal that. Would give you the same result. But I am stupid as you can tell by my previous comments.Comment
-
Originally posted by LisaContractorUmbrella View PostA Employee ID B Month C Amount D Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)
1235 1 25.00
1235 1 15.00 40.00
1235 2 25.00 25.00
1235 3 125.00 125.00
2569 2 25.00
2569 2 15.00 40.00
2569 3 300.00
2870 3 25.00 325.00
What I need is a calculation in column E which will sum Column D at each change of reference in column A
I spent ages lining up all those numbers which now look fine whilst I am editing but all group together when I postMcCoy: "Medical men are trained in logic."
Spock: "Trained? Judging from you, I would have guessed it was trial and error."Comment
-
Originally posted by lilelvis2000 View PostThat's fine. Just trying to determine if its all the shear volume of calculations that are causing the problem or a problem with the subtotaling.
See it as a workaround not a solution.
It could be made into a macro too.Comment
-
Comment
-
Originally posted by LisaContractorUmbrella View Posttell me more
I'm terrible with pseudo code but it will go something like this (I'm sure someone on here will find all sorts of errors and provide a better version.)
var subtotal = 0
var breakValue
//get the first row breakValue and its values
breakvalue = sheet[0][A]
subtotal = sheet[0][D]
sheet[0][E] = subtotal
loop i = 2 to 100 //say
if breakvalue <> sheet[i][A] then
subtotal = 0
breakvalue = sheet[i][A]
end if
subtotal = subtotal + sheet[i][D]
sheet[i][E] = subtotal
next
Something like that will do it.McCoy: "Medical men are trained in logic."
Spock: "Trained? Judging from you, I would have guessed it was trial and error."Comment
-
Originally posted by lilelvis2000 View PostHow familiar are you with loops?
I'm terrible with pseudo code but it will go something like this (I'm sure someone on here will find all sorts of errors and provide a better version.)
var subtotal = 0
var breakValue
//get the first row breakValue and its values
breakvalue = sheet[0][A]
subtotal = sheet[0][D]
sheet[0][E] = subtotal
loop i = 2 to 100 //say
if breakvalue <> sheet[i][A] then
subtotal = 0
breakvalue = sheet[i][A]
end if
subtotal = subtotal + sheet[i][D]
sheet[i][E] = subtotal
next
Something like that will do it.Comment
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Comment