Average and Standard Deviation calculating Wrong values in JavaScript

Discussion
1

I am importing excel sheet in javascript and want to calculate Average and Standard Deviation of that imported data.
Problem: But it calculate wrong average and standard deviation.

Code:

Excel Data Analysis body { font-family: Arial, sans-serif; margin: 20px; } table { width: 100%; border-collapse: collapse; margin-top: 20px; } th, td { border: 1px solid black; padding: 8px; text-align: center; } th { background-color: #f2f2f2; } 
<h2>Import Excel and Analyze Data</h2>
<input type="file" id="fileInput" accept=".xlsx, .xls">
<br><br>
<table id="dataTable"></table>

<script>
    document.getElementById('fileInput').addEventListener('change', handleFile, false);

    function handleFile(event) {
        const file = event.target.files[0];
        if (!file) return;

        const reader = new FileReader();
        reader.onload = function (e) {
            const data = new Uint8Array(e.target.result);
            const workbook = XLSX.read(data, { type: 'array' });
            const sheetName = workbook.SheetNames[0]; 
            const sheet = workbook.Sheets[sheetName];

            // Convert sheet to JSON format
            const jsonData = XLSX.utils.sheet_to_json(sheet, { header: 1, raw: false });

            console.log("📂 RAW IMPORTED DATA:", jsonData); 

            // Process data and remove Excel connection
            const cleanedData = jsonData.map(row => row.map(cell => {
                let cleanedCell = (typeof cell === 'string') ? cell.trim() : cell;
                return !isNaN(cleanedCell) && cleanedCell !== "" ? Number(cleanedCell) : cell;
            }));

            console.log("✅ CLEANED DATA:", cleanedData);

            // Remove Excel link (disable file input)
            document.getElementById('fileInput').value = ""; 

            displayTable(cleanedData);
        };
        reader.readAsArrayBuffer(file);
    }

    function displayTable(data) {
        const table = document.getElementById('dataTable');
        table.innerHTML = ""; // Clear table

        if (data.length === 0) return;

        // Create table header
        const thead = document.createElement("thead");
        const headerRow = document.createElement("tr");
        data[0].forEach(header => {
            const th = document.createElement("th");
            th.textContent = header;
            headerRow.appendChild(th);
        });
        thead.appendChild(headerRow);
        table.appendChild(thead);

        // Create table body
        const tbody = document.createElement("tbody");
        for (let i = 1; i < data.length; i++) {
            const row = document.createElement("tr");
            data[i].forEach(cell => {
                const td = document.createElement("td");
                td.textContent = cell;
                row.appendChild(td);
            });
            tbody.appendChild(row);
        }

        // Add empty row for spacing
        const emptyRow = document.createElement("tr");
        data[0].forEach(() => {
            const emptyCell = document.createElement("td");
            emptyCell.textContent = "";
            emptyRow.appendChild(emptyCell);
        });
        tbody.appendChild(emptyRow);

        table.appendChild(tbody);

        // Compute statistics
        calculateStatistics(data);
    }

    function calculateStatistics(data) {
        if (data.length < 2) return;

        const numColumns = data[0].length;
        const numericColumns = new Array(numColumns).fill(0).map(() => []);

        for (let i = 1; i < data.length; i++) {
            data[i].forEach((value, colIndex) => {
                const num = parseFloat(value);
                if (!isNaN(num)) {
                    numericColumns[colIndex].push(num);
                }
            });
        }

        console.log("📊 NUMERIC DATA:", numericColumns);

        // Calculate Sum, Count, Mean, and Std Dev
        const sumRow = document.createElement("tr");
        const countRow = document.createElement("tr");
        const meanRow = document.createElement("tr");
        const stdRow = document.createElement("tr");

        for (let col = 0; col < numColumns; col++) {
            const sumCell = document.createElement("td");
            const countCell = document.createElement("td");
            const meanCell = document.createElement("td");
            const stdCell = document.createElement("td");

            if (numericColumns[col].length > 0) {
                const sum = math.sum(numericColumns[col]);
                const count = numericColumns[col].length;
                const mean = math.mean(numericColumns[col]);
                const stdDev = math.std(numericColumns[col]);

                sumCell.textContent = sum.toFixed(2);
                countCell.textContent = count;
                meanCell.textContent = mean.toFixed(2);
                stdCell.textContent = stdDev.toFixed(2);

                console.log(`📌 Column ${col + 1} -> Sum: ${sum.toFixed(2)}, Count: ${count}, Mean: ${mean.toFixed(2)}, Std Dev: ${stdDev.toFixed(2)}`);
            } else {
                sumCell.textContent = "-";
                countCell.textContent = "-";
                meanCell.textContent = "-";
                stdCell.textContent = "-";
            }

            sumRow.appendChild(sumCell);
            countRow.appendChild(countCell);
            meanRow.appendChild(meanCell);
            stdRow.appendChild(stdCell);
        }

        // Append rows to table
        const tbody = document.getElementById("dataTable").querySelector("tbody");
        tbody.appendChild(sumRow);
        tbody.appendChild(countRow);
        tbody.appendChild(meanRow);
        tbody.appendChild(stdRow);
    }
</script>
2

You may have picked the wrong forum for this. Try StackExchange.

2 Likes