The goal of my project is to create a macro that consolidates files to create a weighted average list with percentages beside particular identifiers. The sum of all percentages will be equal to 100% under most circumstances (see section III).
The macro will pull identifiers from several individual files. The macro should make it easy to input specific file paths. The format will be consistent with [login to view URL] that is uploaded where the identifers start at A5 and progress to cells downwards. Files can contain between 0 and 30 identifiers.
Each file will have an associated score that is an input at the start of the macro. The macro will return a list with all identifiers listed with a weighted average percentage beside each.
For example, if G1 has a score of 4 with 3 identifiers (A,B & C) and G2 has a score of 2 with 3 different identifiers (D, E & F) the final list will each identifier listed with a corresponding percentage of 22% ([4/(2+4)]/3) while each identifier from G2 will have a corresponding percentage of 11% ([2/(2+4)]/3).
This gets slightly more complex if two files have the same identifiers. For example, if G1 has a score of 4 with 3 identifiers (A, B, C) and G2 has a score of 2 with 3 identifers (C, D and E) then the list will be organized as:
The calculation for C is ([2/(2+4)]/3+[4/(2+4]/3).
The total list will be equal to 100% except when a particular file has less than 2 identifiers. For example, if G1 has a score of 6 with 2 identifiers (A, B) and G2 has a score of 2 with 3 identifiers (B, C, D) the output would be:
The calculation for B would be [6/(2+6)]/3 + [2/(2+6)]/3.
11 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön
Hello, my name is Cristian, I have a degree in Business and work with excel every day. I have much experience with spreadsheets, formulas, models and macros. Check my reviews. Best regards