# Weighted Average with uneven split

#### sriram170

##### New member
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

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
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
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)))

#### WizzardOfOz

##### New member
What??? That went whizzing past overhead.

#### NBVC

##### Super Moderator
Staff member
sriram170,

Please do not crosspost to other forums without first providing links to those threads. It is unfair to all the volunteers who give their free time to answer questions.

#### sriram170

##### New member
Am sorry, this is because i can get different answers in different methods... will take care of it in future

Last edited:

#### NBVC

##### Super Moderator
Staff member
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
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.

#### NBVC

##### Super Moderator
Staff member
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
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
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.

#### NBVC

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

I have yet to see those links though.