Excel IF, IFERROR, IFNA, and IFS — When and How to Use Each

Logical functions are essential in Excel for data cleaning, reporting, and analytics. These four functions help you apply logic and handle errors cleanly:

  • IF → basic logic
  • IFS → multiple conditions
  • IFERROR → catch any error
  • IFNA → catch only #N/A

IF — Basic Logical Test

Use IF when you want Excel to return different results based on a condition.

=IF(A2>50,"Pass","Fail")

Structure

IF(logical_test, value_if_true, value_if_false)

Common uses: thresholds, flags, labels, categories.


IFS — Multiple Conditions (Cleaner than Nested IFs)

Use IFS when you have many conditions to check.

Instead of nested IFs:

=IF(A2>90,"A",IF(A2>80,"B",IF(A2>70,"C","D")))

Use:

=IFS(
A2>90,"A",
A2>80,"B",
A2>70,"C",
TRUE,"D"
)

Cleaner and easier to read.


IFERROR — Handle Any Excel Error

Use IFERROR to replace any error (#DIV/0!#N/A#VALUE!, etc.).

=IFERROR(A2/B2,"Error")

Structure

IFERROR(value, value_if_error)

Common in calculations, lookups, and formulas that might break.


IFNA — Handle Only #N/A Errors

Use IFNA when working with lookups and you only want to catch missing matches.

=IFNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not Found")

This keeps other errors visible while handling only #N/A.

Structure

IFNA(value, value_if_na)

When to Use Each

FunctionUse When
IFYou have one condition
IFSYou have multiple conditions
IFERRORYou want to hide all errors
IFNAYou only want to handle missing lookup results

Common Analytics Examples

Categorize values

=IF(A2>=100,"High","Low")

Grade ranges

=IFS(A2>=90,"A", A2>=80,"B", TRUE,"C")

Safe division

=IFERROR(A2/B2,0)

Lookup with fallback

=IFNA(XLOOKUP(A2,A:A,B:B),"Missing")

Key Takeaways

These are core functions for data cleaning and reporting in Excel

  • IF handles simple logic
  • IFS replaces nested IF statements
  • IFERROR hides all errors
  • IFNA hides only #N/A from lookups

Excel Formulas vs Functions: A Fast Beginner Guide

If you’re using Excel for data analytics, reporting, or general data work, understanding formulasfunctions, and cell references is essential. These are the building blocks behind almost every spreadsheet.


What Is a Formula?

formula is a calculation you write yourself using:

  • Numbers
  • Cell references
  • Math operators: + - * /

All formulas start with =.

Examples:

=B2+B3
=B2-B3
=B2*B3
=B2/B3

Cell References vs Hardcoding Numbers

Avoid hardcoding:

=10+20

Use cell references:

=B2+B3

This makes your spreadsheet dynamic. When values change, the result updates automatically.


What Is a Function?

function is a built-in formula Excel provides to simplify common calculations.

Example:

=SUM(B2:B5)

Key idea:
Every function is a formula. Not every formula is a function.


When to Use Functions

Instead of:

=B2+B3+B4+B5

Use:

=SUM(B2:B5)

Functions are cleaner, faster, and reduce errors.


Essential Excel Functions

SUM – Add values

=SUM(B2:B5)

AVERAGE – Calculate the mean

=AVERAGE(B2:B5)

MAX – Largest value

=MAX(B2:B5)

MIN – Smallest value

=MIN(B2:B5)

COUNT – Count numeric cells

=COUNT(B2:B5)

IF – Apply logic

=IF(B2>50,"Pass","Fail")

Relative vs Absolute Cell References

  • B2 → Relative reference (changes when dragged)
  • $B$2 → Absolute reference (stays fixed when dragged)

This matters when copying formulas across rows or columns.


Explore Excel’s Function Library

Go to Formulas → Insert Function to browse Excel’s full function library and get help building formulas.


Key Takeaways

Understand absolute vs relative references when copying formulas

Formulas start with = and use cell references

Functions are built-in formulas like SUMIFAVERAGE

Always reference cells instead of hardcoding numbers

Use functions to simplify calculations

Excel: Convert Dates from DD MM YYYY to MM DD YYYY (Fix Text Dates Fast)

Intro / The Problem

You have Excel dates like:

25 12 2024
27 01 2025

Excel treats them as text, not real dates, because the format is DD MM YYYY instead of MM DD YYYY.You can’t sort, filter, use date formulas, or load them correctly into Power BI / Tableau.

You need to convert text to a real Excel date, then format it.


Solution Summarized

  1. Select the column
  2. Data → Text to Columns
  3. Choose Delimited → Next → Next
  4. Set Column Data Format = Date (DMY) → Finish
  5. Ctrl + 1 → Custom format → mm dd yyyy

Solution Steps

Step 1 — Select the Date Column

Highlight the entire column with the DD MM YYYY dates.

Step 2 — Open Text to Columns

Go to Data → Text to Columns and choose Delimited.

Step 3 — Skip Delimiters

Uncheck all delimiters. Click Next.

Step 4 — Set Date Format to DMY

Under Column Data Format, choose Date → DMY. Click Finish.
This converts Excel text to a real date.

Step 5 — Apply Custom Date Format

Press Ctrl + 1 → Custom → type:

mm dd yyyy

How to Verify It Worked

  • =YEAR(A2) returns a year
  • Sorting works correctly
  • Changing to Long Date changes the display
  • Works in Pivot Tables / Power BI

Summary

  • Excel can’t recognize DD MM YYYY dates (when the region of the software is set to U.S.)
  • Formatting alone does not fix text dates
  • Text to Columns (DMY) converts text to a real date
  • Custom format displays as MM DD YYYY

How to Make Changes to a Previous Git Commit (Even Older Ones!)

Whether you forgot to update a file or want to clean up your commit history, Git gives you powerful tools to change past commits. Here’s how to do it safely and effectively.

There are 2 sets of instructions:

  1. How to Change the Most Recent Commit
  2. How to Change an Older Commit (Not the Latest One)

Follow whichever set of instructions makes sense for your situation.


1. How to Change the Most Recent Commit

Step 1: Make your changes to the code

Make the changes you want to make to your code then run the git add command:

# Make your code changes
git add .

Step 2: Add the changes to the latest commit

# Option 1: keeps the commit message the same
git commit --amend --no-edit


# Option 2: allows you to modify the commit message
git commit --amend

git commit --amend without the --no-edit flag opens your editor so you can modify the commit message.

⚠️ Important: Only amend if the commit hasn’t been pushed to a shared branch. Amending a pushed commit rewrites history, which can mess with other collaborators.


2. How to Change an Older Commit (Not the Latest One)

Step 1: Start an interactive rebase

To edit an earlier commit (e.g., the second one back), use interactive rebase:

git rebase -i HEAD~n

Replace n with how many commits back you want to go.

You’ll see something like:

pick a1b2c3 Fix login bug
pick d4e5f6 Update README
pick 789abc Add scraper module

Each line represents a commit.

Step 2: Change pick to edit

Change pick to edit on the line you want to modify:

pick a1b2c3 Fix login bug
edit d4e5f6 Update README
pick 789abc Add scraper module

This tells Git: “Pause at this commit so I can change it.”

❓ What if nothing happens when you try to type?

If Git opens Vim (default for many systems), here’s how to use it:

  1. Press i to enter Insert mode – now you can type.
  2. Change pick to edit as needed.
  3. Press Esc to exit insert mode.
  4. Save and exit by typing the following:
:wq

Then press Enter.


Step 3: Git Pauses the Rebase

After saving, Git will stop at the commit you marked for editing. You’ll see a message like:

Stopped at d4e5f6... Update README

Step 4: Make Your Changes

Make whatever changes you need to the code. Then:

git add .
git commit --amend

You can now update both the code and the message if you want.


Step 5: Continue the Rebase

Once you’re done:

git rebase --continue

Git will resume the rebase and replay the rest of your commits.


Made a Mistake?

To cancel the rebase and go back:

git rebase --abort

🧼 After Editing Commits: Push Carefully

If you’ve already pushed these commits to a remote branch, you’ll need to force push:

git push --force

⚠️ Use with caution – force pushing can overwrite others’ work. Only do this on branches you own or after coordinating with teammates.

Auto-Format Python on Save in VS Code with Black

Want to keep your Python code clean and consistent without thinking about it? You can automatically format your Python files every time you save them in Visual Studio Code using the Black formatter.

Here’s how to set it up:


🔌 Step 1: Install the Black Formatter

  1. Open VS Code.
  2. Go to the Extensions Marketplace (the square icon on the sidebar or use shortcut Cmd+Shift+X on macOS, Ctrl+Shift+X on Windows).
  3. Search for “Black Formatter” by Microsoft.
  4. Click Install.

⚙️ Step 2: Open VS Code Settings

You can access settings with:

  • macOS: Cmd + ,
  • Windows: Ctrl + ,

Then click the Open Settings (JSON) icon in the top right (looks like a page with {}) to open the settings.json file.


📝 Step 3: Add Python Format-on-Save Settings

Inside settings.json, add the following:

jsonCopyEdit"[python]": {
"editor.formatOnSave": true,
"editor.defaultFormatter": "ms-python.black-formatter"
}

Optional Formatting Settings

You can also include:

jsonCopyEdit"editor.tabSize": 4,
"editor.insertSpaces": true,
  • "editor.tabSize": 4 ensures indentation uses 4 spaces per level (Python standard).
  • "editor.insertSpaces": true replaces tabs with spaces (again, best practice in Python).

🔄 Step 4: Restart VS Code

After saving your settings.json, you may need to close and reopen VS Code for changes to fully apply.


✅ That’s It!

Now, whenever you save a .py file in VS Code, Black will automatically format it based on its strict, opinionated style — helping you write cleaner, more consistent code effortlessly.

🐛 Streamlit Couldn’t Find selenium — Even Though It Was Installed

Recently, while building a web scraper app with Streamlit, I ran into a frustrating error:

ModuleNotFoundError: No module named 'selenium'

I had already installed selenium using pip install -r requirements.txt, and even confirmed it was installed using:

pip show selenium

and

pip list

Still, Streamlit was throwing the error.

🕵️‍♂️ The Clue

To investigate, I added a simple debug line at the top of my main.py file:

import sys
print("Running from:", sys.executable)

When I ran:

streamlit run main.py

The output showed that Streamlit was running from a different Python environment — not the virtual environment where selenium was installed.

✅ The Fix

It turned out that I simply hadn’t activated the virtual environment before launching Streamlit. Here’s what finally worked:

source env/bin/activate
streamlit run main.py

Once I activated the correct virtual environment, everything worked as expected.


💡 Takeaway

If you ever install a package but your Python app can’t find it, the issue might not be the install itself — but which Python interpreter is being used. Checking sys.executable is a quick way to diagnose mismatched environments.

Troubleshooting Guide: Fixing ‘This is taking much longer than it should’ Error Preventing Expo App Project Loading in Expo Go App

I was running the command npx expo/ npx expo start and when I tried to open the Expo app on my phone, I was seeing this message on the screen:

The message says “This is taking much longer than it should. You might want to check your internet connectivity.

Solution

First, make sure your phone and the computer you’re running the code on are using the same Wi-Fi network

Run the command npx expo start --tunnel instead.

How to Install Python Libraries in Visual Studio Code (Mac)

Before we begin, ensure that you have at least Python 3 installed. I’m using Python 3.9

First, create a new folder. This will be your Python project folder.

Next, create a Python file inside your folder. It doesn’t matter what you call it. I’ll call mine main.py

In order to import libraries inside our folder, we need to first create a Python virtual environment. To do this run the following command in Terminal inside your project directory:

python3.9 -m venv projenv 

I typed python3.9 because I’m using Python 3.9, but you would type whichever version you’re using.

I called my virtual environment projenv, but you can call yours whatever you want.

You should see a new folder for your virtual environment was created within your project folder.

Note: Check to make sure that the VS Code Python interpreter is the correct version.

Next, we will activate the virtual environment so that Terminal knows we only want to work within the virtual environment.

source projenv/bin/activate

Now, you should see the virtual environment name in parentheses at the left of your new Terminal line.

It’s good practice to make sure you have installed the latest version of pip. Run this command to update to latest:

pip3 install --upgrade pip

Now, we can start installing pip dependencies to our project.

To install a dependency, run the following command:

pip3 install matplotlib

I’m using matplotlib in this example.

Now, we will check to to see if the install was successful. In the Python file you created earlier, import the matplotlib package:

import matplotlib.pyplot

Run the main.py file by typing python main.py in Terminal.

If you don’t get an error, then you successfully installed a package!

Build Realtime Chat App with Socket.io, NodeJS, and ReactJS

In this article, you will learn how to build a real-time chat application using NodeJS, ReactJS, and the Socket.io library. This article will provide a good introduction to WebSockets and the Socket.io library.

The application will be very simple in terms of features and design, as it is mainly intended to demonstrate the basic implementation of a Socket server and how to interact with it in a frontend application.

Here is a video of the final application:

Prerequisites

In order to follow along with the tutorial, you will need the following:

  • NodeJS v10+ installed
  • Basic knowledge of NodeJS, Express, ReactJS

WebSockets & Socket.io

Before we start building the application, we will discuss what WebSockets is and why it is useful for building web applications with real-time features.

WebSockets is a protocol for bidirectional, or two-way, communication between a client and server.

But what makes a bidirectional communication protocol like WebSockets preferable to a unidirectional protocol like HTTP when building real-time features?

While HTTP requires the client to send a request to the server first to receive any data, a bidirectional protocol such as WebSockets allows the connection between client and server to persist.

This means that a client is not required to make a new request to the server every time that they want to check for new data from the server.

In this tutorial, we will use the WebSockets API to implement a real-time chat, but we won’t be working with the API directly. Instead, we will use Socket.io as a wrapper over WebSockets.

Socket.io is a library that abstracts away all of the complications of working with WebSockets, allowing you to implement bidirectional communication in your application with less headache.

The Socket.io library contains two parts: a server that mounts onto the NodeJS HTTP server and a client-side library for the browser.

Building the Socket Server

We will start by building out the backend of the application.

First, create a new NodeJS project by running the following command:

npm init -y

Installing dependencies

We will need to install the following packages:

  • Express: NodeJS framework that makes building back end web applications easier
  • Socket.io: Library that enables real-time, bi-directional client-server communication
  • CORS: Mechanism that allows restricted resources from a domain to be shared with other domains
npm install express socket.io cors

Create a server file called index.js. Inside the server file, import Express and create an Express app:

const express = require("express");

const app = express();

CORS middleware

Cross-origin resource sharing (CORS) is an HTTP header mechanism that allows servers to grant permission to other external domains to access server resources.

The CORS NodeJS package provides a middleware that can be used to enable CORS on your server.

Import the CORS package and enable the Express server to accept cross-origin HTTP requests:

const cors = require("cors");

app.use(cors());

Creating Socket IO object

Although HTTP comes built-in to Express, we will need to access it directly to set up the Socket.io server.

So, we need to first create an HTTP server, which we can do using the built-in HTTP library in NodeJS.

const http = require("http");
const server = http.createServer(app);

Import the Server function from Socket.io:

const { Server } = require("socket.io");

Now, we will initialize a new instance of socket.io by calling all the Server function from Socket.io, taking in the HTTP server as an argument. Since we will be accessing the backend from a cross-origin frontend browser application, we need to pass a CORS object as an optional second argument to allow any URL to access our server URL:

const io = new Server(server, {
  cors: {
    origin: "*",
    methods: ["GET", "POST"],
  },
});

server.listen(8000, () => {
  console.log(`Server is running on port ${PORT}`);
});

Creating Socket event listener

Now, we can implement Socket IO logic. Just like WebSockets, it’s an event based system. When an event is emitted, you will have access to any data that was passed along with the event in the callback function. There, you can implement some function in response to the event.

The first event that you will want to listen for is the connection of a client from the frontend. The callback of the connection event will give us access to the client Socket object.

io.on('connection', (socket) => { 
    console.log('new client connected');
});

Whenever there is a new Socket connection, we will send the user that connected their Socket ID using io.to().emit()

By calling to and passing in the client’s Socket ID, we are ensuring that the event is only emitted to the specific client.

io.on("connection", (socket) => {
  io.to(socket.id).emit("socket_id", socket.id);
});

Now, we will create a custom event handler to handle the send_message event from the client. This event will be emitted anytime a user submits a new message in the chat application. The message data that is sent along with the event is passed to the callback function.

Taking the message data, the server emits a receive_message event to all clients connected to the server (except the client who sent the message). In short, this event sends newly submitted messages to other users of the application.

io.on("connection", (socket) => {
  socket.on("send_message", (messageData) => {
    socket.broadcast.emit("receive_message", messageData);
  });
});

We have finished implementing the backend for the application. Here is the full server file code:

const express = require("express");
const http = require("http");
const cors = require("cors");
const { Server } = require("socket.io");

const app = express();
const server = http.createServer(app);

app.use(cors());

const io = new Server(server, {
  cors: {
    origin: "*",
    methods: ["GET", "POST"],
  },
});

io.on("connection", (socket) => {
  io.to(socket.id).emit("socket_id", socket.id);

  socket.on("send_message", (messageData) => {
    socket.broadcast.emit("receive_message", messageData);
  });
});

server.listen(8000, () => {
  console.log(`Server is running on port 8000`);
});

Building React frontend application

In a separate client folder, create a new React application by running the following command:

npx create-react-app client

Now, open the App.js file in the src folder. This is the only file we’ll be using in the React project.

React boilerplate setup

Before we add Socket IO to the frontend, we will set up the layout of the frontend, create state variables and other Hooks elements, and handle some other boilerplate.

socketID will store the ID of the client’s socket.

currentMessage will store the text typed into the input. When the user presses the Send button, the will send this message text to other clients in the send_message event.

messageList will store all of the messages– both from the current user and other users– sent thus far.

The sendMessage function will be responsible for sending messages from the current user to the server.

import React, { useState, useEffect, useRef } from "react";
import "./styles.css";

function App() {
  const [socketID, setSocketID] = useState("");
  const [currentMessage, setCurrentMessage] = useState("");
  const [messageList, setMessageList] = useState([]);

  const sendMessage = async () => {
    
  };

  useEffect(() => {
    
  }, []);

  return (
    <div class="container">
      <h1>Realtime Chat</h1>

      <div class="msg-container-wrapper">
        <div class="message-container">
          {messageList.map((msg) => {
            return (
              <div class={`message ${msg.sender === socketID ? "my-msg" : "other-msg"}`}>
                {msg.message}
              </div>
            );
          })}
        </div>
      </div>

      <div class="input-container">
        <input
          type="text"
          value={currentMessage}
          onChange={(e) => setCurrentMessage(e.target.value)}
        />
        <button onClick={() => sendMessage(currentMessage)}>Send</button>
      </div>
    </div>
  );
}

export default App;

Installing Socket IO client

Socket IO is not a direct WebSocket implementation, which means that you can’t just use the built-in WebSocket class in the browser. Instead, you must use the Socket IO client library.

You can install it with the following command:

npm install socket.io-client

The io object, which is the Socket IO client library, will now be globally available in the browser and you can import it throughout your project.

Import the io object:

import { io } from "socket.io-client";

Create the connection by pointing to the URL of the server:

const socket = io("http://localhost:8000");

Now, we are ready to start listening to events from and emitting events to the server.

Getting client Socket ID from the server

In the useEffect hook, our client Socket will listen for the socket_id event from the server which sends the current user’s socket ID. We use this socket ID to differentiate between messages sent by the current user and messages sent by other users.

We also return an anonymous function that acts as a component cleanup upon unmounting.

useEffect(() => {
    socket.on("socket_id", (id) => {
      setSocketID(id);
    });

    return () => {
      socket.off("socket_id");
    };
}, [socket]);

Receiving messages from the server

Next, we will add an event listener to the useEffect hook to listen for the receive_message event. This event is triggered to receive any new messages from the Socket IO server.

When the event occurs, the data from the event, which is an array including the message text and the message sender, is added to the messageList array to be added on screen.

Again, we create an anonymous cleanup function for the event listener.

useEffect(() => {
    socket.on("receive_message", (data) => {
      setMessageList((list) => [...list, data]);
    });

    return () => {
      socket.off("receive_message");
    };
}, [socket]);

Sending messages to the server

Now, we will create a function that will send a new message to the Socket IO server. This will be an asynchronous function.

First, we check to make sure that the current message is not an empty string. If it is not, then we create a messageData object including the socket ID of the sender and the message text.

Then, we use the current user’s socket to emit a send_message event to the server, passing along the messageData object with it.

When the server gets this data, it will emit it to all of the other users in the application. However, the user who actually sent the message will not receive the data from the server. Instead, the data is added to the messageList manually. Then, the currentMessage is cleared.

const sendMessage = async () => {
    if (currentMessage !== "") {
      const messageData = {
        sender: socketID,
        message: currentMessage,
      };

      await socket.emit("send_message", messageData);
      setMessageList((list) => [...list, messageData]);
      setCurrentMessage("");
    }
};

We have finished implementing the logic for the frontend application. Here is the full App.js file code:

import React, { useState, useEffect, useRef } from "react";
import { io } from "socket.io-client";
import "./styles.css";

const socket = io("http://localhost:8000");

function App() {
  const [socketID, setSocketID] = useState("");
  const [currentMessage, setCurrentMessage] = useState("");
  const [messageList, setMessageList] = useState([]);

  const sendMessage = async () => {
    if (currentMessage !== "") {
      const messageData = {
        sender: socketID,
        message: currentMessage,
      };

      await socket.emit("send_message", messageData);
      setMessageList((list) => [...list, messageData]);
      setCurrentMessage("");
    }
  };

  useEffect(() => {
    socket.on("socket_id", (id) => {
      setSocketID(id);
    });

    socket.on("receive_message", (data) => {
      setMessageList((list) => [...list, data]);
    });

    return () => {
      socket.off("socket_id");
      socket.off("receive_message");
    };
  }, [socket]);

  return (
    <div class="container">
      <h1>Realtime Chat</h1>

      <div class="msg-container-wrapper">
        <div class="message-container">
          {messageList.map((msg) => {
            return (
              <div class={`message ${msg.sender === socketID ? "my-msg" : "other-msg"}`}>
                {msg.message}
              </div>
            );
          })}
        </div>
      </div>

      <div class="input-container">
        <input
          type="text"
          value={currentMessage}
          onChange={(e) => setCurrentMessage(e.target.value)}
        />
        <button onClick={() => sendMessage(currentMessage)}>Send</button>
      </div>
    </div>
  );
}

export default App;

With that, you have basically completed the application! All that is left is the CSS styling, which we won’t cover in this tutorial. In the links below, you can access the CSS that was used in the demo project.

Thank you for following this tutorial. Happy coding!

Adding TypeScript to Existing React Project

Installation

First install it into the project:

npm install --save typescript @types/node @types/react @types/react-dom @types/jest

or

yarn add typescript @types/node @types/react @types/react-dom @types/jest

Next, rename any file to be a TypeScript file (e.g. src/index.js to src/index.tsx). Type errors will start to show up.

Generate tsconfig.json file

Although the React documentation says that you are not required to make a tsconfig.json file because one will be made automatically for you, that was not my experience. After doing the installation, a tsconfig.json file was not generated for me.

I had to run the following command to generate the file:

npx tsc --init

Now, you should be able to run your React project with TypeScript, if there are no TypeScript errors.