Tutorials For Excel everyday with EVBA.info part 6[Free PDF ebook] - EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

## EVBA’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

You can use the `SUBTOTAL` function to look up the minimum or maximum number in a filtered list.
In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
`=SUBTOTAL(5,B5:B100)`
The formula in cell B2 that returns Sue Flay’s maximum sales number is
`=SUBTOTAL(4,B5:B100)`
The first argument for `SUBTOTAL` is `Function_Num`, basically what type of function you’re wanting to perform:
`1 AVERAGE2 COUNT3 COUNTA4 MAX5 MIN6 PRODUCT7 STDEV8 STDEVP9 SUM10 VAR11 VARP`

## Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

In a list that contains positive and negative numbers, here are formulas that can sum those numbers in different ways, depending on the nature of your project.
Based on the picture:
• Formula of only positive numbers summed: `=SUMIF(B3:B15,">0")`
• Formula of only negative numbers summed: `=SUMIF(B3:B15,"<0")`
• Formula of all numbers summed as if positive: `=SUM(ABS(B3:B15))`
Note, this last formula is an array formula. 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.
Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number
Some employers pay their employees based on work time that is measured in decimals as portions of an hour. For example, if an employee works a 7-hour and 45-minute day, the employer pays that person for 7.75 hours of work time.
Here is how to represent elapsed work times in decimalized fashion.
Step 1
• Enter and copy as needed the numeric formula, example `=HOUR(C3-B3)+(MINUTE(C3-B3)/60)`.
• Select the formula range and right-click that selection.
• From the right-click popup menu select Format Cells.
Step 2
In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list, select Number.
• In the Decimal Places field, select your desired setting. I selected 2 decimal places.
• In the Negative Numbers field, I selected the black font style -1234.10.
• Confirm your format selections by clicking the OK button.
Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month
Here are formulas to return various dates of first and last days of a given month.
First day’s date of that month: `=DATE(YEAR(B1),MONTH(B1),1)`
Last day’s date of that month: `=DATE(YEAR(B1),MONTH(B1)+1,0)`
First Monday date of that month:
`=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))`
Last Friday date of that month:
`=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)`
First day of the month, 3 months later: `=DATE(YEAR(B1),MONTH(B1)+3,1)`
Last day of the month, 3 months later: `=DATE(YEAR(B1),MONTH(B1)+4,0)`
Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months
As pictured below, here are formulas that return the date for the last day of…
• Current month: `=DATE(YEAR(NOW()),MONTH(NOW())+1,0)`
• Last month: `=DATE(YEAR(NOW()),MONTH(NOW()), 0)`
• Next month: `=DATE(YEAR(NOW()),MONTH(NOW())+2,0)`
Tom’s Tutorials For Excel: Summing a Range Diagonally
Every now and then you come across an unusual request to do this or that in Excel. Such an example is summing a range of numbers diagonally, as shown in the picture for B11:F15.
The array formula that accomplishes this in cell B17 is
`=SUM(B11:F15*(ROW(B11:F15)=COLUMN(B11:F15)+9))`
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.
One note about this particular formula, in case you are wondering about the `+9` portion. Because this is an array formula, the numerical range of rows and columns must equal each other in the summing process, as evidenced by the `=` operator in the formula. The range of interest is B11:F15, or to express it another way, from row 11 to row 15, and from column 2 to column 6. To satisfy the array’s requirement that elements be evaluated in equal numerical index terms, 9 is added to column 2 to equal 11 as a starting point, which is the same starting row number of the range’s upper left cell B11, that is, row 11. The array sums the elements in the range whose row and column indices equal each other. The `+9` notation simply aligns the row and column elements to begin at the same number.
Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes
When you work with numbers so large that Excel puts them in Scientific Notation format, you might want to format those numbers for a more meaningful look.
For example, in the Before and After comparison pictures, the large numbers represent the size of storage space kilobytes on computer hard drives. You’d like to see those numbers in a format that’s easy to comprehend, such as in gigabytes with one decimal and a “GB” suffix.
To make this happen, first, select the range of numbers, and right click the selection. From the popup menu, click to select Format Cells.
Next, in the Format Cells dialog box:
• Click onto the Number tab.
• From the Category list, select Custom.
• In the Type field, enter `#,##0.0,,,"GB"`
• Click the OK button.
Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date
In Excel, the 7 days of the calendar week can be identified by their index number using the `WEEKDAY` function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the `WEEKDAY` function returns the number 6.
In the next picture, a monthly calendar of daily events is listed, with Fridays being conditionally formatted in yellow. The Conditional Formatting formula rule that produces this result is
`=WEEKDAY(\$A3)=6`.
Notice that the non-date cells in column B are also highlighted in yellow, by virtue of the absolute reference to column A in the formula.
To start, show the Conditional Formatting dialog box by pressing `Alt+O+D`.
Versions 2003 and before, Step 1 of 3
• Select “Formula is” from the drop down list.
• Enter your conditional formula as shown.
• Click the Format button.
Versions 2003 and before, Step 2 of 3
In the Format Cells dialog box:
• Click the Patterns tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.
Versions 2003 and before, Step 3 of 3
You return to the Conditional Formatting dialog box, where you click the OK button to confirm.
Versions 2007 and after, Step 1 of 5
In the Conditional Formatting Rules Manager dialog box, click New Rule.
Versions 2007 and after, Step 2 of 5
In the New Formatting Rule dialog box, click the option “Use a formula to determine which cells to format”. Then, enter your formula rule as shown, and click the Format button.
Versions 2007 and after, Step 3 of 5
In the Format Cells dialog box:
• Click onto the Fill tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.
Versions 2007 and after, Step 4 of 5
You return to the New Formatting Rule dialog box, where you click the OK button to confirm.
Versions 2007 and after, Step 5 of 5
You return to the Conditional Formatting Rules Manager dialog box, where you click the OK button to confirm all your choices.
Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer
If you’ve ever wondered why a custom header or footer does not show an ampersand (`&`) when you want to show it, there’s a small trick to making that happen. All it takes is two ampersands in succession, in order to show a single one.
For example, suppose you want a custom footer to be `This & That`. When setting up your custom footer, you need to actually enter `This && That`.
Here are the steps.
Step 1
Step 2
In the Page Setup dialog box:
• Click onto the Header/Footer tab.
• Click the Custom Header or Custom Footer button. In this example I clicked Custom Footer.
Step 3
In the Footer dialog box, in the Center Section field, I entered `This && That`. Notice the two ampersand characters in immediate succession. Then, click the OK button.
Step 4
You will return to the Page Setup dialog to confirm the look of your custom footer text. I circled in red what it looks like in this example. Simply click the OK button to confirm your custom footer text.
Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character
Data Validation is an excellent way to control data entry to meet a certain condition.
In this example, suppose you want to insure that a specific character is included in a text entry for a range of cells. In the next picture, you want to enter email addresses so you want to make sure the “`@`” character is included. The following steps show how this can be done.
Step 1
Select the range of interest, and from your keyboard press `Alt+D+L` to show the Data Validation dialog box.
Step 2
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.
Step 3
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be `=ISNUMBER(FIND("@",B2))`
Step 4
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1