![Atlas Blog](https://i1.feedspot.com/4616542.jpg?t=1615968871)
Atlas Blog
1,012 FOLLOWERS
Atlas Programming Management is all about the business of Microsoft Office, particularly Excel. They provide a variety of services and support to business clients & individual Excel users at all skill levels. Their website is also an extensive resource offering expert information, tips and tools
Atlas Blog
1y ago
Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart
Here is a step by step example of how to program a pivot table and pivot chart, including how to place those objects exactly where you want them on your worksheet.
The programming code is in the downloadable workbook, which you can also see at the bottom of this web page following the pictures.
Click here to download this free and safe example workbook.color="blue">
Sub AnatomyOfPivotTableAndChart()
'Delete all pivot tables and pivot charts that might be on the worksheet,
'to start off with a clean w ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals
When rounding numbers to a particular decimal factor, you can express that rounding factor in your formula as either a fraction or as its decimal equivalent. In the pictures, the fraction one-eighth can be stated in a formula as 1/8 or by its decimal equivalent of .125.
Rounding up
Expressing a fraction, the formula in cell B4 copied to cell B8 is =CEILING(A4,1/8)
Expressing a decimal, the formula in cell D4 copied to cell D8 is =CEILING(A4,0.125)
Notice the results in range B4:B8 are the same as the results in range D4:D8 ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal
There are times when you want to identify a number as being a whole number (such as 47) or a decimalized number (such as 23.5).
The picture shows three ways to apply this idea. You might want to identify a number as being whole or decimalized in a separate cell; or in the same cell, or disallow its attempt at entry.
In the TRUE or FALSE image, the formula in cell C3 is =MOD(B3,1)=0 to identify decimal numbers.
In the Data Validation image, that same formula rule is used to disallow a decimal upon entry.
In the Conditional ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE
You can nest a function as the lookup_value argument with VLOOKUP, to return an item relating to the lookup_value function. In the pictured example, MIN, MAX, and AVERAGE are nested to return the name of the salesperson associated with those functions.
The formula in cell D2 is =VLOOKUP(MIN(A4:A22),A4:B22,2,0)
The formula in cell E2 is =VLOOKUP(MAX(A4:A22),A4:B22,2,0)
The formula in cell F2 is =VLOOKUP(AVERAGE(A4:A22),A4:B22,2,1)
The VLOOKUP function’s fourth argument is range_lookup. For the MIN and MAX functions, the range_ ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates
In yesterday’s tutorialcolor="blue">, I showed how to place a date and time on separate lines in the same cell.
That example involved a formula with the TEXT function, meaning the date and time value in cell A3 was not a real date or real time, but a constant text value that was not unlike a text sentence such as you are reading now.
Common wisdom has it that you cannot perform mathematical functions on text dates. For example, if you try to add a 1 to the text value, you will return a #VALUE! error.
However, you start to ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell
Here’s a tip to place the date and time in the same cell, in separate lines.
The first step is to enter the formula
=TEXT(NOW(),"MMMM D, YYYY")&CHAR(10)&TEXT(NOW(),"HH:MM AM/PM")
The CHAR(10) notation refers to ascii character number 10, which is a carriage return character. In the cell, it looks like a small square, for example as pointed to by the red arrow.
Next,right-click the cell and select Format Cells from that pop-up menu.
In the Format Cells dialog box, click onto the Alignment ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String
When you are faced with alphanumeric strings, such as those esoteric-looking serial numbers that represent a store’s stock items, here is how you can deal with parsing them based on the position of their first integer.
To find the position of the first integer, the formula in cell B2 and copied down to cell B13 is
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
Notice for example in cell B2, the formula returns 3. That is because cell A2 contains the alphanumeric string GS5453GDGD5. The first i ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Finding the Number Closest to Zero
Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did, you could simply do a VLOOKUP to find it), you can apply these array formulas as shown.
Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.
If you are ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell
Here’s a formula to help you list (that is, return) the values from every (in this case) 6 cells. This is a useful method when your data is structured such that you know the incremental factor of rows that are in between cells that carry the data you want to separately list. It is especially handy when your source list is hundreds or thousand of rows deep.
In the pictured example, the formula in cell F2, which was copied down to cell F5 is
=OFFSET($C$2,ROW(C2)*6-6,0 ..read more
Atlas Blog
3y ago
Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds
Formula examples for hours, minutes, and seconds being added or subtracted from time.
Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)
Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)
Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)
Combination of Hours, Minutes, and Seconds
Example to add 3 hours, 16 minutes, and 48 seconds: =$B$1+TIME(3,16,48)
Example ..read more