I am attempting to collect funds from 3rd party vendors based on their yearly purchase amount. I am given a total target that needs to be split across merch. divisions but weighted based on their historical purchases for that MD. I know I can use a vlookup with a countif to divide based on occurrence of the account number but I need to take it a step further.

Example: Vendor 5 has a total target of $43M , I need to split that total across 3 divisions by the historical % (for kitchen, 18% of $43M would be the target $)

is there a formula that will automatically do this?

CodePudding user response：

You can use the formula

=$b$2*(h2*.01)

b2 contains the total target and h2 contains the percentage.

If the values in the h column are already formatted as percentages, then the *0.1 is not necessary

CodePudding user response：

I was able to figure this out by utilizing xlookup and multiplying by the percentage. =xlookup(e2,a2,b2*h,0)