Excel automation of correlation calculations
- or -
Post a project like this2034
£50(approx. $63)
- Posted:
- Proposals: 12
- Remote
- #2125264
- Awarded
Microsoft Certified Power BI Data Analyst & Advance Excel Specialist¦Professional Power BI consultant ¦ (ACCA)- Chartered Certified Accountant ¦ Accounting- Reconciliations ¦DAX ¦Data Models ¦
Doha
Virtual Assistant, Excel programmer, Customer Support Agent, EN-GR/GR-EN Translator
Kornos
Virtual Assistant | Web Research Specialist | Excel | Fillable / Editable PDF |Data Mining Specialist |Data Extraction | & Project Coordinator
Cairo
208270140641643239648649574541114494731788290226670623543172355446235650389834
Description
Experience Level: Intermediate
Attached are 2 example spreadsheets of survey results that we would like merged into a single spreadsheet and the calculation process automated.
We have a requirement to be able to automatically run correlation between any two scores, calculate the average of one of them, plus show the total count, filtered against other columns of data. Results would be plotted on a 3D bubble chart. (See Worksheet "Quadrant" in file “Example output for correlation automation”.)
We need a scalable spreadsheet (please note, the number of columns in the source file "Original Raw Data...." may increase or decrease) where we can drop a set of Original Raw Data data similar to that shown, select scores and filters, and then the filtered results automatically populate the associated sheets for the correlation and averages to be calculated, plus the final result updated in the bubble chart.
Please note, that we have created multiple worksheets (sheets1T, Sheet1P etc) in “Example output for correlation automation” but ideally these can be merged into a single process.
The example file is running the following analysis from the file “Original Raw Data for correlation automation”. (to be merged into a single spreadsheet);
Scores from “Original Raw Data for correlation automation”, in columns A is correlated against column D and are filtered on columns O and P.
The worksheets "sheets1T", "Sheet1P" etc., have been populated with the filtered results.
In each "sheets1T", "Sheet1P" etc., sheet, the average score is automatically calculated in cells E2, the correlation in F2 and the number of records G2.
Please note we have removed all the blank scores assuming this was needed.
The front sheet "Quadrant" automatically updates the bubble chart. However the scales of the chart are manually altered by us to create the best display but we would like this automatically updated too.
As stated, Scores from “Original Raw Data for correlation automation”, columns A and D are filtered on columns O and P. We would like the ability to quickly change and select the columns used for correlation and filtering.
For example, we want to correlate B and C which is filtered on I and J, but only where column D is 4. As another example, correlate C and D filtered on column A scores 0-6, 7+8 and 9+10 as three separate groups.
The budget is a pure guess. Please show examples of similar work.
We have a requirement to be able to automatically run correlation between any two scores, calculate the average of one of them, plus show the total count, filtered against other columns of data. Results would be plotted on a 3D bubble chart. (See Worksheet "Quadrant" in file “Example output for correlation automation”.)
We need a scalable spreadsheet (please note, the number of columns in the source file "Original Raw Data...." may increase or decrease) where we can drop a set of Original Raw Data data similar to that shown, select scores and filters, and then the filtered results automatically populate the associated sheets for the correlation and averages to be calculated, plus the final result updated in the bubble chart.
Please note, that we have created multiple worksheets (sheets1T, Sheet1P etc) in “Example output for correlation automation” but ideally these can be merged into a single process.
The example file is running the following analysis from the file “Original Raw Data for correlation automation”. (to be merged into a single spreadsheet);
Scores from “Original Raw Data for correlation automation”, in columns A is correlated against column D and are filtered on columns O and P.
The worksheets "sheets1T", "Sheet1P" etc., have been populated with the filtered results.
In each "sheets1T", "Sheet1P" etc., sheet, the average score is automatically calculated in cells E2, the correlation in F2 and the number of records G2.
Please note we have removed all the blank scores assuming this was needed.
The front sheet "Quadrant" automatically updates the bubble chart. However the scales of the chart are manually altered by us to create the best display but we would like this automatically updated too.
As stated, Scores from “Original Raw Data for correlation automation”, columns A and D are filtered on columns O and P. We would like the ability to quickly change and select the columns used for correlation and filtering.
For example, we want to correlate B and C which is filtered on I and J, but only where column D is 4. As another example, correlate C and D filtered on column A scores 0-6, 7+8 and 9+10 as three separate groups.
The budget is a pure guess. Please show examples of similar work.
Alan W.
100% (89)Projects Completed
93
Freelancers worked with
69
Projects awarded
52%
Last project
20 Sep 2022
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Alan,
Please invite me on this job...
Thanks
Sumit
SaS Technologies
702590
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies