Excel

HLOOKUP Per the website, "HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row." Below, I wanted to return the number of Basis invoices paid on week 1, week 2, week 3, etc. I used the right formula here because the row index number is always one more than the week. Alternatively, on another sheet, I also just used an array constant that allowed me to use just one single formula to output the same result.

GETPIVOTDATA The GETPIVOTDATA function returns visible data from a PivotTable. Honestly, at its simplest, just use "=" and then click on a cell and then the whole syntax will autopopulate. That is how I returned the Billing Gross, Net, and Commission YTD. I think is helpful to use a data validation list. First, use "=" and reference a cell and then just change the item to reference the cell that has data validation. For example, there when you change the media type on the green cell F12, you can get its unbilled gross, net, and commission. Note that the data validation list dropdown may not appear unless you open the workbook on your Excel Desktop and open the workbook in Edit mode.  

IF, NOT, AND, OR To get more practice with the logic behind using the IF function with NOT, AND, OR logic: Below, I wanted to make a file where I showcase how I would use each in a AP/AR/Billing position such as my role as a Finance & Accounting Specialist. In my examples, I use them to see the following: 1) if we are billing over budget 2) if we have any urgent amounts to pay 3) if invoice is okay to pay as long as we billed client and receive client payment 4) if an invoice is due within 30 days.

IF, NOT, OR If it is either this or that, then mark and x. Either wise, do not mark it as an x. (i.e. Red or Green) If it is neither this or that, then mark an x. Either wise, do not mark it as an x. (i.e. Neither Red or Green) Below, I do a version 1 formula without using a constant array and formulas that uses constant array and produces same result.

RIGHT  &  SUMIF As a Finance & Accounting Specialist, I need to report he unbilled media actual or the final numbers after billing is sent out to the client. I use the RIGHT function to return the first 2 letters of the invoice number because that represents the media type. Then, I can use the SUMIF function to return the net, commission, and gross for GENERAL media types, Out of Home (OH) media type, and Digital (DG) media type. Finally, everyone reports their numbers on an excel file to track the company's gross profit.                                  

IF, AND, OR More practice with the IF, AND, OR logic formula. I used the IF and OR to identify certain colored apples and mark an x if so. I used the IF & AND to determine if it is a round apple or not.

IF and OR Constant Array See below 2 examples using IF and OR logic together. The first example is returning whether the individual pass or fail if either test is over 50. The second example is returning whether the invoice is okay to pay if the status column is either billed to client or cash receipt entered (client paid). For the second example, I need to use a constant array, a hard-coded set of values provided in an Excel formula for "Billed to Client" and "Cash Receipt Entered." A curly brace is used within the IF formula.  

Multiple Condition IF Below, I made up a scenario where a seller is offering 1 item for $2.00, 2 to 9 items for $1.50 each, and 10 items or more for $1.00 each.  In green is the syntax for the IF statement and a IF AND statement. The idea is that if the first condition is not true, then it will check if the next condition is true. If the second condition is not true, then it will check if the third condition is true. Finally, in the How Many Purchased Column is a Data Validation that only allows for whole numbers from 1 through a 100 to prevent any decimals or nothing purchased scenario. 

IF  &  Operators Below is file with example of arithmetic operators and comparison operator. I used the if statement along with comparison operator to determine result of "fail", "pass", or "perfect." I also used a conditional formatting rule to fill a certain color for each result.

Data Validation Below is an example of data validation where I created a custom rule that makes it that the date a vendor received via ACH must be in year of 2024, more than 2 days after processed date, and cannot fall on either a Sunday or Saturday. I used the AND, YEAR, WEEKDAY, and < >(does not equal) symbols in my formula.