# Thread: Weighted Average with uneven split

1. ## Weighted Average with uneven split

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.36Krish.xlsxKrish.xlsx

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

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

4. What??? That went whizzing past overhead.

5. 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.

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

7. Thank you very much WizzardOfOz and Bob Phillips

8. Originally Posted by sriram170
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.

9. Originally Posted by NBVC
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.

10. 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?

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•