Report Making

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Greg, below is a description of the formulas I am looking for. It is essentially 2 tables. 1 for Weight and the other for purchasing. Theres a lot of formulas and I just don't know how to do it. But I know it can be done. And done probably easy for someone who knows what they are doing.
Weights:
- Cells I11-T11 to return the total average amount purchased per month. Isn't determined by the vendor entered into cell I1.
- Cells I12-T12 to return the total percentage amount by month. Isn't determined by the vendor entered into cell I1.
- Cells I13-T12 to return a selected vendors average percentage purchased by month against all vendors purchased. Determined by the vendor entered into cell I1. An example for cell I13 would be Vendor A accounts for 12% of all product purchased in the month of January.
- Cells I14-T14 to returned a selected vendors average weight purchased in that given month. Determined by the vendor entered into cell I1. An example for cell I14 would be Vendor A sells an average of 1,000 lbs
- I do not need information entered into I15-T15. I actually need that information entered into cell U15. It would be the average amount purchased from a selected vendor yearly. Determined by the vendor entered into cell I1. An example for cell U15 would be Vendor A sells an average of 11,000 lbs a year.

Purchasing
- Cells I32-T32 to return the average total spent amount per month for all vendors. Isn't determined by the vendor entered into cell I1. An example for cell I32 would be in January the average amount spent is $100,000.
- Cells I33-T33 to return the average amount spent per pound for all vendors. Isn't determined by the vendor entered into cell I1. An example for Cell 133 would be in January the average price per pound is $7.22 per lb
- Cells I34-T34 to return the average total spent per month for vendors. Determined by the vendor entered into cell I1. An example for Cell I34 would be in Vendor A cells an average of $10,000 in January.
- Cells I35-T35 to return the average spend per month per pound for vendors. Determined by the vendor entered into Cell I1. An example for Cell I35 would be the average price per pound from Vendor A is 10.01 per lb in January.
- Cell U33 to return the total average spend per month for all vendors. Isn't determined by the vendor entered into cell I1. An example for cell U33 would be the total average spend per month on all vendors is $120,000 per month
- Cell U34 to return the total average spend per pound for all vendors. Isn't determined by the vendor entered into cell I1. An example for cell U34 would be the total average price per pound is $8.72 per pound
- Cell U35 to return the total average spend per month per vendors. Determined by the vendor entered into cell I1. An example for cell U35 would be the total average spend per month for Vendor A is $11,000 per month
- Cell U36 to return the total average spend per pound per vendor. Determined by the vendor entered into cell I1. An example for cell U36 would be the total average price per pound is $8.72 per pound for Vendor A.

Below is the attachment.

Perhaps this will help.
1. Convert the data in columns A:F into an Excel table
2. Populate I10:T10 using:

Formula:

Please Login or Register  to view this content.

3. Populate I11:T11 using:

Formula:

Please Login or Register  to view this content.

4. Populate I12:T12 using:

Formula:

Please Login or Register  to view this content.

5. Populate I13:T13 using:

Formula:

Please Login or Register  to view this content.

6. Populate I14:T14 using:

Formula:

Please Login or Register  to view this content.

I feel that the formulas for rows 32:35 will follow the same pattern.
Let us know if you have any questions.

This has been extremely helpful. I have used some of the formulas you provided along with putting up some Pivot Tables.
I have everything I need compiled except one thing.
I'm trying to find how much the average spend per lb is per vendor per month.
So Need to add all of the spend per lbs for Vendor A purchased in January and divide by the number of times purchased in January for vendor A.

Formula I have but that doesn't work right. It ends up only dividing by the total number of years that were had purchases in January, not by the actual amount of times purchased in January. So if 3 purchases were made in January of 2022, and only 1 other time in 2023, its dividing by 2 instead of 3.
=IF($I$1="","",TableMasterSpendLbsVendor[[#Totals],[January]]/COUNTIF(TableMasterSpendLbsVendor[January],"<>0"))


All Table Dates are in Table1[[Date]]
All Vendor Names are in Table1[[Vendor]
All Cost/Price are in =Table1[[Cost Price]]


My table that is running my per spend is in the Formula...TableMasterSpendLbsVendor

Please give us examples based on the file as there is no 'Vendor A' listed.
In order to get an average based on a particular year, there needs to be a cell that has a year value (I37)
The formula for cells I35:T35 is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.