davidascott

asked on

# Calculating Adjusted Totals

I am trying to find a formulaic approach to establishing the adjusted totals in column J on the attached spreadsheet. This is derived by taking the original supplier amount (range B2 to B4) and then adding the adjustments in column G.

The problem I have is that the layout of the table in columns D to G varies in the number of lines of adjustment per supplier and the supplier name is only output in the first line of each adjustment (see cell D2 - 'Smiths Ltd')

Example

Smiths Ltd 'Adjusted Total' = 2000 + 20000 + 1705.47 + 2890.22 = 26595.69

I would like to use a formula that references the 2000 and then automatically picks up the 'adjustments table' and recognizes how many adjustment lines there are.

I thought that the offset formula might work, but couldn't figure it out.

thanks,

David

Experts-Exchange-q1.xlsx

The problem I have is that the layout of the table in columns D to G varies in the number of lines of adjustment per supplier and the supplier name is only output in the first line of each adjustment (see cell D2 - 'Smiths Ltd')

Example

Smiths Ltd 'Adjusted Total' = 2000 + 20000 + 1705.47 + 2890.22 = 26595.69

I would like to use a formula that references the 2000 and then automatically picks up the 'adjustments table' and recognizes how many adjustment lines there are.

I thought that the offset formula might work, but couldn't figure it out.

thanks,

David

Experts-Exchange-q1.xlsx

David,

i used two helper columns to calculate exactly as per your need, without tempering with your original table.

please see attached workbook

=VLOOKUP([@Supplier],Table1,2,0)+(SUMPRODUCT((Table3[H2]=ROWS($L$2:L2))*(Table3[Amount])))

EE.xlsx

i used two helper columns to calculate exactly as per your need, without tempering with your original table.

please see attached workbook

=VLOOKUP([@Supplier],Table

EE.xlsx

ASKER

RGonzo,

Your formula would work, but would not handle -ve values i.e. two negatives would = plus so the formula would only work to a point. Would be good to see how you might adjust this.

Thanks,

David

Your formula would work, but would not handle -ve values i.e. two negatives would = plus so the formula would only work to a point. Would be good to see how you might adjust this.

Thanks,

David

ASKER

ProfessorJimJam,

Would you mind explaining your solution as I am not totally up to speed with array formulae. Is this similar to the sum-product function? Wasn't sure how you set up #This Row.

To get array formulae entered, do you key in the formula and then press alt>enter?

Thanks,

David

Would you mind explaining your solution as I am not totally up to speed with array formulae. Is this similar to the sum-product function? Wasn't sure how you set up #This Row.

To get array formulae entered, do you key in the formula and then press alt>enter?

Thanks,

David

ASKER

Dear ProfJimJam,

Sorry for the stupid question, but my real list is 6,000 lines long so how is the contents of the range populated? i.e. when I referred to the contents of the name, it appeared that all the contents of the range were in there.

Thanks,

David

Sorry for the stupid question, but my real list is 6,000 lines long so how is the contents of the range populated? i.e. when I referred to the contents of the name, it appeared that all the contents of the range were in there.

Thanks,

David

Could you explain how negative value should be accounted for?

the(positive) sum of negatives is negative

see example

Experts-Exchange-q1V2.xlsx

the(positive) sum of negatives is negative

see example

Experts-Exchange-q1V2.xlsx

ASKER

Dear Ggonzo,

When the first table contains a negative and the second cell is a negative, the result should be zero. By serendipity, due to the fact that the signs are not correctly shown the formula works by taking your original formula and replacing the 'plus' with a 'minus'

e.g. -minus 2000, minus, minus 2000 = zero, which is the right result

I had not looked deeper into the data than I should have as I was expecting the signs to be reversed in the 'adjustment' column.

Therefore, as stated above, your solution works by simply changing + with -

Thanks,

David

When the first table contains a negative and the second cell is a negative, the result should be zero. By serendipity, due to the fact that the signs are not correctly shown the formula works by taking your original formula and replacing the 'plus' with a 'minus'

e.g. -minus 2000, minus, minus 2000 = zero, which is the right result

I had not looked deeper into the data than I should have as I was expecting the signs to be reversed in the 'adjustment' column.

Therefore, as stated above, your solution works by simply changing + with -

Thanks,

David

ASKER

Dear Rgonzo,

I have just noticed one thing in your original solution as part of my own on-going work. You had additionally populated cells D3 and D4 with the same Smiths Ltd. Owing to the nature and volume of transactions and the associated extract provided, the cells D3 and D4 would be left blank, so the challenge is to extract the total without pre-populating the d3 and d4 i.e. the formula should pick up the fact that d2 'smiths' relates to cells g2, g3 and g4 without the need to copy the name 'smiths' down to d3 and d4.

Thanks,

David

I have just noticed one thing in your original solution as part of my own on-going work. You had additionally populated cells D3 and D4 with the same Smiths Ltd. Owing to the nature and volume of transactions and the associated extract provided, the cells D3 and D4 would be left blank, so the challenge is to extract the total without pre-populating the d3 and d4 i.e. the formula should pick up the fact that d2 'smiths' relates to cells g2, g3 and g4 without the need to copy the name 'smiths' down to d3 and d4.

Thanks,

David

ASKER

Dear Prof JimJam,

I have noticed that your formulae make reference to column J. Please would you update please. Should this make reference to column I?

Thanks,

David

I have noticed that your formulae make reference to column J. Please would you update please. Should this make reference to column I?

Thanks,

David

ASKER

Hi Prof JimJam,

My response above should have specifically referenced cell H2 which references cell J5 which is an unused column.

Would you mind adjusting?

Thanks,

David

My response above should have specifically referenced cell H2 which references cell J5 which is an unused column.

Would you mind adjusting?

Thanks,

David

ASKER

Hi Prof JimJam (again!),

I have just tried to understand the formula in say, cell L2. Specifically:

1. what do the [square brackets] do - I have never used this syntax in my formula before (sorry for stupid question)

2. [#ThisRow] - do I have to name a range etc and/or how does this syntax work/function?

3. [supplier] - ditto comment in 2 above

My working assumption that this is similar to index/match formulae where you are identifying the intersect of the two square brackets.

A long-hand explanation would be really appreciated as I really like what you've done.

Thanks,

David

I have just tried to understand the formula in say, cell L2. Specifically:

1. what do the [square brackets] do - I have never used this syntax in my formula before (sorry for stupid question)

2. [#ThisRow] - do I have to name a range etc and/or how does this syntax work/function?

3. [supplier] - ditto comment in 2 above

My working assumption that this is similar to index/match formulae where you are identifying the intersect of the two square brackets.

A long-hand explanation would be really appreciated as I really like what you've done.

Thanks,

David

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Dear Prof JimJam,

I am very appreciative of your very thorough response and I have/will genuinely learn something new from this encounter. There was one thing that was puzzling me; Cell H2 makes reference to cell 'J5' which appears to be an erroneous cell reference on the face of it.

Would you mind clarifying this point for me.

Thanks,

David

I am very appreciative of your very thorough response and I have/will genuinely learn something new from this encounter. There was one thing that was puzzling me; Cell H2 makes reference to cell 'J5' which appears to be an erroneous cell reference on the face of it.

Would you mind clarifying this point for me.

Thanks,

David

ASKER

Dear Prof JimJam,

I have just been testing the response and the associated formula and have the following comments:

1. The total elicited in column L should be code agnostic i.e. whatever codes appear in Column 1 really does not matter and therefore the driver of the outcome that drives the derivative total in column L is the 'Supplier'

Going back to my original question, there will be a number of suppliers who have 'n' number of accounts codes (column 1 codes) assigned to them. I would like column L to return the total supplier turnover for all codes allotted to them, irrespective of the code.

To illustrate this point, if I adjust say, cell E5 to another code number, the total for Jones returns an incorrect answer. I must admit that my original table did not correctly show the names and cells D2, D5 and D6 should read Smiths Ltd, Jones Ltd and Thomas Ltd respectively.

2. It is also worth highlighting that in the complete data set, there may be some suppliers who appear in column A but do not have any 'adjustment table' adjustments and therefore the formulae in column L should be able to manage this.

3. The objective is to ascertain 'total supplier turnover' (column L) by starting with the raw output in column A and B and taking account of adjustments (could be +ve or -ve) in the 'adjustments table' (columns D to I) to derive the 'adjusted supplier turnover'

What drew my attention to this was that when I tested the sheet that you sent and changed the name of the supplier, in column D, the totals remained the same.

The formula needs to recognize that Smith Ltd in cell A2 and B2 has a number of adjustments (may be none) in cells G2, G3 and G4 which must be totaled and added to cell the original total in B2 to derive the adjusted total in cell L2. The tricky thing (one of the many!) is that when the adjustment table is output (from a pivot), each supplier may have a number of adjustments but the name is only stated once e.g. smiths only appears on the first line of the series of adjustments in lines 2 to 4 in the 'adjustment table'.

Hope this helps for modification of the formula?

Thanks for your help.

Best regards,

David

I have just been testing the response and the associated formula and have the following comments:

1. The total elicited in column L should be code agnostic i.e. whatever codes appear in Column 1 really does not matter and therefore the driver of the outcome that drives the derivative total in column L is the 'Supplier'

Going back to my original question, there will be a number of suppliers who have 'n' number of accounts codes (column 1 codes) assigned to them. I would like column L to return the total supplier turnover for all codes allotted to them, irrespective of the code.

To illustrate this point, if I adjust say, cell E5 to another code number, the total for Jones returns an incorrect answer. I must admit that my original table did not correctly show the names and cells D2, D5 and D6 should read Smiths Ltd, Jones Ltd and Thomas Ltd respectively.

2. It is also worth highlighting that in the complete data set, there may be some suppliers who appear in column A but do not have any 'adjustment table' adjustments and therefore the formulae in column L should be able to manage this.

3. The objective is to ascertain 'total supplier turnover' (column L) by starting with the raw output in column A and B and taking account of adjustments (could be +ve or -ve) in the 'adjustments table' (columns D to I) to derive the 'adjusted supplier turnover'

What drew my attention to this was that when I tested the sheet that you sent and changed the name of the supplier, in column D, the totals remained the same.

The formula needs to recognize that Smith Ltd in cell A2 and B2 has a number of adjustments (may be none) in cells G2, G3 and G4 which must be totaled and added to cell the original total in B2 to derive the adjusted total in cell L2. The tricky thing (one of the many!) is that when the adjustment table is output (from a pivot), each supplier may have a number of adjustments but the name is only stated once e.g. smiths only appears on the first line of the series of adjustments in lines 2 to 4 in the 'adjustment table'.

Hope this helps for modification of the formula?

Thanks for your help.

Best regards,

David

Dear David,

on your question from ID: 41381159, please check my latest attachment in post ID: 41379600

it does not have reference to J5.

on your question from ID: 41381159, please check my latest attachment in post ID: 41379600

it does not have reference to J5.

Dear David,

Happy New Year!

in response to your question post ID: 41381206

the solution which i provided to the original post, is exactly mirroring what the example file you uploaded. now from what i understand from the above post, is that what you are looking for is completely different from the original post.

i suggest you create a comprehensive dummy sample file , exactly as per your real data, including the pivot table you pointed. then indicate the correct amount where you need the formula to calculate. then i would be able to help.

for this new set of requirement, i suggest you open a new question, i will try to come up with solution asap.

Happy New Year!

in response to your question post ID: 41381206

the solution which i provided to the original post, is exactly mirroring what the example file you uploaded. now from what i understand from the above post, is that what you are looking for is completely different from the original post.

i suggest you create a comprehensive dummy sample file , exactly as per your real data, including the pivot table you pointed. then indicate the correct amount where you need the formula to calculate. then i would be able to help.

for this new set of requirement, i suggest you open a new question, i will try to come up with solution asap.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

If you repeat the supplier name in Col D

you could use

Open in new window

RegardsExperts-Exchange-q1V1.xlsx