Calculating Adjusted Totals

davidascott
davidascott used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

If you repeat the supplier name in Col D

you could use

=B2+SUMIF($D$2:$D$8,A2,$G$2:$G$8)

Open in new window

Regards
Experts-Exchange-q1V1.xlsx
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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

Author

Commented:
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

Commented:
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

Author

Commented:
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
Top Expert 2016

Commented:
Could you explain how negative value should be accounted for?

the(positive) sum of negatives is negative
see example
Experts-Exchange-q1V2.xlsx

Author

Commented:
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
Top Expert 2016

Commented:
then change the formula to

=B2+(SUMIF($D$2:$D$8,A2,$G$2:$G$8)*SIGN(B2))
Experts-Exchange-q1V3.xlsx

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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
Microsoft Excel Expert
Top Expert 2014
Commented:
Hi David,

the square brackets that you see are the Structured References of Excel Tables.  Excel Tables function is available from Excel 2007 onward.  i converted your ranges to Excel Tables, because one of the great features of Excel table is its Dynamic range expansion, meaning that if you add more data beneath the existing table then the excel table will automatically encompass the additional added data and you do not need to update your formulas becuase the formulas everything else in the table will automatically get updated by itself.  

now, in order to learn in detail what are those breakets, @signs, i suggest you read a very well explained detail about Structured Referencing of Excel table http://peltiertech.com/structured-referencing-excel-tables/ 

then you will understand what all those strange signs you see the formula.  i could have wrriten it here, but i could not explain it better than Zack Barresse & Kevin Jones who are the real Experts on Excel tables, they also have wrriten a Book called Excel tables, if you are inerested in Mastering Excel tables, i suggest you get a copy of that book. it is available in amazon and also in their website.

now back to the formula. i have now attached the version without the excel tables.  so you do not see those weird references but the normal cell references

to better understand the formula, i will break it down in details.

we could not have done this without using the helped columns of H and I
lets look first in column H.  the formula is countif performed in expandable range meaning that if you see the E1:E2  E$1 is locked witht he dollar sign in front of the $1 so that every-time the cell goes down it will start from the top and expands by each cell  this helper column looks considers the number incrementor to reset whenever similar to the first one which is X143 appears again and it goes until it finds another X143 and then puts one.  then the second helper column is to put repetitive number incrementor, so that everytime there is X143 in get increases and it goes with the same number until there is another X143 and you can see that in that column.

now we will talk about the formula in L column =VLOOKUP(Sheet5!$K2,Sheet5!$A$2:$B$4,2,0)+(SUMPRODUCT((Sheet5!$I$2:$I$8=ROWS($L$2:L2))*(Sheet5!$G$2:$G$8)))

the formula is two part, first one is a easy Vlookup basically it looks from the ID in K and returns the number from the B matched with the name. second part is joined with the plus sign +  that means to take that amount form the B column and + add it with result of SUMPRODUCT
so sumproduct uses helper column data to do the calculation.
SUMPRODUCT((Sheet5!$I$2:$I$8=ROWS($L$2:L2))*(Sheet5!$G$2:$G$8)))

SUMPRODUCT first line is Sheet5!$I$2:$I$8=ROWS($L$2:L2)   basically ROWS($L$2:L2) here do not do anything but just to generate number incrementor starting from the cell where the formula is and then going down it increases, again the first reference row2 is locked and the other one is not so when the formula is copied down the ROWS($L$2:L2) which hold a value of 1 becomes ROWS($L$2:L3) which hold the value of 2  
ROWs fucntion has one arument that when you give the reference of a range it returns the number of rows in that range.

now, in sumproduct it will check column I and whatever cells that have a value corresponding to the Row incrementor number it will result true and falses true means match it found and then it multipies by cells in column (Sheet5!$G$2:$G$8)))  so for zeros multuplies by corresponding G column result is zero and for those with 1 it returns the values of column G and then sumproduct basically adds them together and then sumproduct result and vlookup result gets summed at the end.

please see the attached file and feel free to come back if you need more clarification.
without-table-ref.xlsx

Author

Commented:
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

Author

Commented:
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
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Dear David,

on your question from ID: 41381159,  please check my latest attachment in post ID: 41379600
it does not have reference to J5.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial