Thread: Online Questionnaire with analog results display - Excel IF Function & what Graphic?

Have you tried YouTube “How to Build Excel Interactive Dashboards” (which include graphs).

Not exactly what you asked for but the graphs act as the analogue display?

Originally Posted by Lionelgee

Hello Computer Aficionados,

Would using the IF with AND, OR, and NOT functions within Excel be the way to go to drive a digital display in either the form of an analog amperage meter, temperature gauge or an 'Econo' gauge, to pictorially display the results as people fill out an online questionnaire? Also, if this is suitable what graphics program could be linked to show the final result in the form of a gauge?

For example, responses for each question are arranged in a Likert Scale - based on a horizontal number line where 0 is none of the time and 5 represents all of the time. As people are answering the questions Excel - or some other software product is ticking along in the background adding up the results and calculating where the needle should sit within the analog gauge display. T

How do you get the Excel program to communicate with the graphic display to present the questionnaire results on the analog gauge? What graphic program would be available? Is Excel the best source for the calculations or is there already a mob with software who allows you to create your own custom made analog 'gauges' from online questionnaires? If so, who are they and what is their program?

"Questions, questions! Its always the questions with you - Lionel?"

BTW: the questionnaire is along the lines of a combination of The Kessler Psychological Distress Scale (K10) accessed 13/01/2025 from https://www.worksafe.qld.gov.au/__da...scale-k101.pdf with similarities to the The Depression Anxiety Stress Scale (DASS-10) assessed from Depression Anxiety Stress Scale (DASS-10) – NovoPsych

I am looking a designing a "wellbeing" scale.

Kind regards
Lionel

Hi Lionel

Playing with a new AI program gave me

"https://www.perplexity.ai/search/would-using-the-if-with-and-or-9pOvTOMFRKSccS09O1J8Lg"

Or "Using Excel's IF, AND, OR, and NOT functions could be part of the solution for driving a digital display based on questionnaire responses, but it wouldn't be sufficient on its own to create an interactive analog gauge. Here's a breakdown of the approach and some alternatives:

## Excel Calculations


Excel can certainly handle the calculations required for processing questionnaire responses:


1. You can use IF, AND, OR, and NOT functions to evaluate responses and assign scores[1].
2. SUM or AVERAGE functions can be used to calculate total or average scores.
3. Conditional formatting could be used to create simple visual representations within Excel[5].


However, Excel alone is not designed to create interactive, real-time analog gauge displays.


## Connecting Excel to Graphics


To create an analog gauge display, you'd need to:


1. Perform calculations in Excel
2. Export data to a visualization tool
3. Update the visualization in real-time


This process is complex and not natively supported by Excel. It would require custom programming or third-party tools.


## Alternative Solutions


Instead of using Excel, consider these options:


1. **Survey Software with Built-in Visualizations**: Many online survey platforms offer real-time results with customizable visualizations, including gauge-like displays.


2. **Data Visualization Tools**: Software like Tableau or Power BI can connect to data sources (including Excel) and create interactive, real-time visualizations including gauges.


3. **Web Development**: A custom web application could be built using JavaScript libraries like D3.js or Chart.js to create interactive gauges based on questionnaire responses.


4. **Specialized Questionnaire Software**: There may be niche software designed specifically for creating custom analog gauges from online questionnaires, but I don't have information about specific products.


## Recommendation


For the most efficient solution, I recommend using a. This would eliminate the need to connect separate calculation and visualization tools.


If you require a highly customized solution, consider working with a web developer to create a custom application that can handle both the questionnaire and the gauge visualization in one integrated system.


Citations:
[1] Using IF with AND, OR, and NOT functions in Excel - Microsoft Support
[2] Excel IF OR statement with formula examples
[3] Excel tips: how to use the IF function | INTHEBLACK
[4] Online Questionnaire with analog results display - Excel IF Function & what Graphic?
[5] https://support.microsoft.com/en-au/...c-aa8bbff73be2
[6] https://www.youtube.com/watch?v=t6h_VvfYrYY
[7] https://www.myonlinetraininghub.com/...ions-explained
[8] https://www.myexcelonline.com/blog/free-excel-templates-and-spreadsheets/"

I then of course asked "suggest comprehensive survey platform that includes both questionnaire functionality and real-time visualization capabilities"

Response

"Based on the search results, several comprehensive survey platforms offer both questionnaire functionality and real-time visualization capabilities. Here are some top recommendations:

1. SurveyMonkey: This platform provides real-time results and allows you to create stunning presentations. It offers professional charts and graphs to transform survey data into compelling infographics and visualizations[4]. SurveyMonkey also supports various data export formats for flexible presentation options.


2. Displayr: This platform is tailored for survey data analysis, reporting, and dashboard publication. It offers real-time collaboration, basic analysis, crosstabs, and professional-quality reporting with automatic data updating features[1].


3. Mentimeter: This free and easy-to-use survey tool allows for live questionnaires. It offers a variety of slide types that let your audience input their answers using their smartphones or other internet devices, enabling real-time interaction and visualization[7].


4. Polly: This platform provides real-time results and instant visualization. It allows you to see responses the second they come in, review and analyze results instantly within Slack or Microsoft Teams, and chart results in multiple visual formats[3].


5. Contentsquare: While primarily a digital experience analytics platform, Contentsquare offers survey capabilities combined with powerful visualization tools. It provides real-time, actionable insights from customer feedback and allows you to pair survey results with behavioral data like heatmaps and analytics[5].


These platforms offer a range of features to suit different needs, from basic survey creation to advanced data analysis and visualization. They all provide real-time capabilities, allowing you to see and act on results as they come in, which aligns with your requirement for an analog gauge-like display of questionnaire results.


Citations:
[1] https://www.insightplatforms.com/top...visualisation/
[2] https://wpdatatables.com/survey-data-visualization/
[3] https://www.polly.ai/real-time-visualization
[4] https://www.surveymonkey.com/product...isualize-data/
[5] https://contentsquare.com/guides/surveys/software/
[6] https://infovis-wiki.net/wiki/Intera...Online_Surveys
[7] https://www.mentimeter.com/features/surveys
[8] https://imotions.com/products/imotions-online/surveys/"

Honestly I love excel and its big brother ms ACCESS . The database size limits and 'not really' random number generator aside
My auditing days playing with those and reconstructive accounting tools are very fond memories for me

SurveyMonkey - Free online survey software and questionnaire tool

The free version of this is ok! Some government types have used the professional tool from it in surveys I have seen.

Hello NavyDiver,

Wow ... just wow. Thank you so much for posting such a detailed response and for obviously spending a significant amount of time and research to provide answers to my question. It will take me some time to navigate my way through your respose. Wow. I am lost for words.

Kind regards
Lionel

Hello All,

By the way The Depression Anxiety Stress Scale (DASS-10 & 42) assessed from Depression Anxiety Stress Scale (DASS-10) – NovoPsych, that I mentioned in my original post, is a 100 percent Australian product. The DASS was developed and tested right here in Australia by P. F. Lovibond & S. H. Lovibond., from the School of Psychology, University of New South Wales, Sydney, Australia. The inclusion of the numbers 10 and 42 identify the number of items in either questionnaire. Similarly the Kessler K5 and K10 identifies the number of item its respective questionnaires.

Peter F. Lovibond and Sydney (Syd) Harold Lovibond were a father and son research team. Syd died in 2017 and the obituary written by Professor Peter Lovibond makes for a very interesting read. It is available from, Obituary: Emeritus Professor Sydney (Syd) Harold Lovibond Hon FAPS | APS

There you go ... your fun facts for today!

Kind regards
Lionel