Weighted Average with uneven split

sriram170

New member
Joined
Aug 4, 2013
Messages
9
Reaction score
0
Points
0
I have a data which relates to particular vendor where the payment is made in single line , but we have to review the Invoice and give the break up(Manually) for each line and later the single line will be splitted on weighted average method based on the breakup, which makes a long time work is there any formula or Macro that would help me to do the job very easily

I have also attached sample file for your reference


Below is the example of the Issues, Solution is appreciated
Question

Vendor name AMT
chevron 5000
Glencore 3000
TCS 5000
G&F 40000



Answer

Vendor name AMT Breakup Weighted average Formula
chevron 5000 1000 1000 =C14/SUM($C$14:$C$17)*B14
2000 2000
1000 1000
1000 1000
Glencore 3000 1500 1500
1500 1500
TCS 5000 1500 326.09
1500 326.09
1000 2,17.91
1000 2,17.91
G&F 40000 2000 3636.64
2000 3636.36View attachment Krish.xlsxView attachment Krish.xlsx
 

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
It's complicated but here goes :)

In E15 create the ARRAY formula (press Cntrl Shift Enter) when done
E15 = IF(B15="",E14,B15/SUM(OFFSET(C15,0,0,MATCH(FALSE,ISBLANK(B16:$B1000),0),1)))

In D15 the formula is simply
D15 = C15*E15

Copy both formula down. At the end (currently Line 27) you need anything (eg B27= 1) to fix the final item

Going thru the formula in steps
1: IF(B15="",E14, <do something>)
If it is blank get the amount / sum from the previous cell otherwise <do something>

2:B15/SUM( <calculate amount/sum for that invoice>

3:OFFSET(C15,0,0,<How many blank rows>,1)))
using the offset to sum the number of rows

4: MATCH(FALSE,<Array to test>,0)
find which row is the first NON blank row

5: ISBLANK(B16:$B1000)
Makes an array of True/False

I have assumed 1000 rows, can make that to the end of the sheet or change $B1000 to B100 (assuming you won't have more than 100 items per invoice)
 
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
If you can put some string in A31, such as end, to signify the end of the table, this single array formula will do it

=IF($C15="","",INDEX($B$1:$B$31,MAX(IF($A$15:$A15<>"",ROW($A$15:$A15))))*$C15/SUM(INDEX($C$1:$C$31,MAX(IF($A$15:$A15<>"",ROW($A$15:$A15)))):INDEX($C$1:$C$31,MIN(IF(A16:$A$31<>"",ROW($A16:$A$31)))-1)))
 

sriram170

New member
Joined
Aug 4, 2013
Messages
9
Reaction score
0
Points
0
Am sorry, this is because i can get different answers in different methods... will take care of it in future
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Am sorry, this is because i can get different answers in different methods... will take care of it in future

yes, that is fine, but have the courtesy to post those links where others can find the threads and understand if you have gotten an answer before they waste their time.
 

sriram170

New member
Joined
Aug 4, 2013
Messages
9
Reaction score
0
Points
0
yes, that is fine, but have the courtesy to post those links where others can find the threads and understand if you have gotten an answer before they waste their time.


Sure and thanks and please close the thread
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Are you going to post the link?

Are you going to go back to those other threads and tell people that you have a solution, so that they don't continue to waste time?
 

sriram170

New member
Joined
Aug 4, 2013
Messages
9
Reaction score
0
Points
0
Are you going to post the link?

Are you going to go back to those other threads and tell people that you have a solution, so that they don't continue to waste time?

I request you not to use the word again "waste teh time", please... i have already closed them all
 

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
Bob's answer left me with that what happened feeling. So for anyone else wondering about the how and why.
First step is to calculate the Min and Max rows for each invoice (Both array formulas Cntrl+Shift+Enter if following)
EndRow =MIN(IF(A16:$A$31<>"",ROW($A16:$A$31)))-1)
StartRow =MAX(IF($A$15:$A15<>"",ROW($A$15:$A15)))

Second step is to replace the above formulas to simplify Bob's formula and I add extra spaces for clarity
=INDEX($B$1:$B$31,StartRow) * $C15 / SUM( INDEX($C$1:$C$31,StartRow) : INDEX($C$1:$C$31,EndRow) )

Splitting like this makes it easier to understand for a new project. Get the start and end rows in a new column. Do the "main" maths where the formula needs to go using the temp columns. When everything is working copy the temp column formulas into the main formula column and delete the temp columns.

Brilliant, thanks Bob.
 
Top