Author: ANmar

Calculate above and below me

April 9, 2026 ANmar No Comments

Calculating average of the column in a cell inside that same column Getting average of what is above me and what is below me in my same column in one of the cells DS2 has 1 to use as Average, anything else to get regular number from some calculation … PH2 = IF($DS2=1, IFERROR(AVERAGE(PH$1:PH1,PH3:PH$110),”N/A”), “Other…

Average, IF, IFERROR, Logical, Standard functions, Statistical

Stale values (Scratched functions)

June 30, 2025 ANmar No Comments

Excel recently started to show cells with formulas scratched (see screenshot). Looks like this new feature to tell user that these cells will have new values and they are in the process of getting calculated. If you want to disable that feature, uncheck “Show stale values” in “Calculation Options” in “Formula” bar (Screenshot)

Excel User tips, Format Cells, Non-functions, Worksheet

Middle name + 1st name

February 3, 2024 ANmar

Get the initial (middle name) from a triple name in a cell.
C4 The cell contains the triple name separated with spaces.
=LEFT(C4,1) & "." & MID(C4, SEARCH(" ", C4, 1) + 1, 1) & "." & MID(C4, SEARCH(" ", C4, SEARCH(" ", C4, 1) + 1) + 1, 1) & "."

Read More “Middle name + 1st name” »

Basic Math, Formulas - combined functions, LEFT, MID, SEARCH, Standard functions, Texts and Strings

LastSunday, LastSaturday

October 17, 2023 ANmar

Get the date of the most recent SUNDAY for a specific date:D12 is the cell containing the date =D12 – WEEKDAY(D12) + 1 While Saturday is easier Get the date of the most recent SATURDAY for a specific date: =D12 – WEEKDAY(D12)

Date and Time, Standard functions, WEEKDAY

Paste Special Percentage

May 20, 2023 ANmar

One of many tricks that we overlook due to the huge number of possibilities in Excel and its functions.

This one I wanted to keep helping me as reference, it is how to change a number in range of cells by copying another value OVER it with ‘Paste Special’. Checkout screenshot.

Read More “Paste Special Percentage” »

Excel User tips, Non-functions

Jan8 date format

April 24, 2023 ANmar

Format a date in shortest format possible indicating how many days have passed (Past date)

If date1-date2 < 8 Sat, Fri, …
If date1-date2 < 350 Dec24, Jan4, Aug14
Else 2020Feb4

Filter. Google Sheets function

April 22, 2023 ANmar

Interesting seeing Google Sheets doing things differently than Excel. This function will fill in cells as values once the user presses Enter. Technically a feature rather than a function.

Once you do first cell, google sheets will fill the rest down with values, not formulas.

Google Sheets

Walking Columns

January 14, 2023 ANmar

An easy way to scroll through set of data in a graphic way without graphs.

A-pure-formula xlsx workbook. These were done using formulas, conditional formatting, and ActiveX control.

Unix DateTime Number

January 6, 2023 ANmar

Convert Date/time number from regular number to Unix DateTime Number.

Get 3rd Wednesday of month

November 15, 2022 ANmar

This was an interesting request.

Needing to get the date of 3rd Wednesday for a given month, automatically, using pure Excel functions.

So, sharing here on what is the formula and how to get there.

Basic Math, CHOOSE, DATE, Date and Time, Formulas - combined functions, Lookup and References, MONTH, Standard functions, WEEKDAY, YEAR

Spreadsheet Compare application

October 25, 2022 ANmar

This is only available in Microsoft 365 Enterprise version, an application that can compare two workbooks in all objects. This includes cells (values or formulas) in all sheets, VBA objects and macros. Bug: I noticed that even if VBE project in a workbook is protected, it will still open it and show you all macros,…

Excel User tips, Non-functions, Worksheet